CODEWITHSUNDEEP
mdxssas-tabular
Fenix
Fenix

Reputation: 2391

MDX Dynamic dimension filter based on value of other dimension

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?

Upvotes: 1

Views: 558

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

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])

Upvotes: 1

Related Questions