MDX Dynamic dimension filter based on value of other dimension

Question

How can I filter using values from two dimension in MDX?

The required result should include records where [Purchase Date].[Date] is before today minus number of years from [Accounting Date].[Year]. So, the result should include YoY records from today based on [Purchase Date].[Date] for each [Accounting Date].[Year] member.

I would like something like the following:

 SELECT NON EMPTY [Measures].[Amount] ON 0,
 NON EMPTY [Accounting Date].[Year].[Year].ALLMEMBERS ON 1
 FROM [Tabular_Model]
 WHERE (
    NULL :
    STRTOMEMBER("[Purchase Date].[Date].&["+ Format(DateAdd("YYYY", [Accounting Date].[Year].CURRENTMEMBER.MEMBER_VALUE - 2020, Now()),"yyyy-MM-ddT00:00:00") + "]")
 )

But it fails with error: Execution of the managed stored procedure DateAdd failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException. The syntax for 'All' is incorrect. (All).

Why CURRENTMEMBER.MEMBER_VALUE works for HAVING but not in my WHERE clause? What is the right way?

GregGalloway · Accepted Answer · 2019-12-30T18:18:37.430

Try the following measure and query:

 WITH
 MEMBER [Measures].[Trailing Amount] as    SUM({NULL :
    STRTOMEMBER("[Purchase Date].[Date].&["+ Format(DateAdd("YYYY", [Accounting Date].[Year].CURRENTMEMBER.MEMBER_VALUE - 2020, Now()),"yyyy-MM-ddT00:00:00") + "]")}, [Measures].[Amount])
 SELECT [Measures].[Trailing Amount] ON 0,
 NON EMPTY [Accounting Date].[Year].[Year].MEMBERS ON 1
 FROM [Tabular_Model]

If MDX doesn't perform as well as you hope, then you might consider adding the following DAX measure into your Tabular model. The following DAX query illustrates how to use it, but if you put this DAX measure into your model, you can query it with MDX queries and it should likely perform better than an MDX calculation:

define
measure 'Your Table Name Here'[Trailing Sales] = 
 VAR YearOffset = SELECTEDVALUE('Accounting Date'[Year]) - 2020
 VAR NowDate = NOW()
 VAR EndDate = DATE(YEAR(NowDate)+YearOffset,MONTH(NowDate),DAY(NowDate))
 RETURN CALCULATE([Amount], 'Purchase Date'[Date] <= EndDate)
evaluate ADDCOLUMNS(ALL('Accounting Date'[Year]),"Trailing Sales",[Trailing Sales])

Thank you, the query works for me. Unfortunately, it is very slow when more dimensions are used `ON ROWS`. On the other side, when I use T-SQL with loop through Year of Accounting Date, it is much faster. Is there any other way in MDX how to do that? — Fenix, Dec 30 '19 at 16:07
@Fenix please see edits above which include a DAX equivalent. Hopefully it will perform better. — GregGalloway, Dec 30 '19 at 18:18

MDX Dynamic dimension filter based on value of other dimension

1 Answers1