MDX: Rewrite query with CurrentMember for multiple selection

One of famous MDX issues is that you cannot use “CurrentMember” keyword when you have multiple selection on an attribute. Read Mosha’s article to understand the full picture.

In a few words, the solution is based on using EXISTING and EXISTS keywords.

EXISTING filters  a set of members to selected members only, it’s in fact the “currentmembers” missing keyword.

EXISTS is a function that returns members of first set that are included in the second set.

I’d like to show 2 concrete samples how to rewrite your query. You can run them on Adventure Works DW OLAP DB. Anyway it's important to notice that not every query/measure can be rewritten in this way, and sometimes the best way is to change the approach to the solution.

1)      Count days in selection

We want to count how many days are included in selected period. If 2 months are in selection, we expect to get the sum of each month values.

The “Single” version takes all descendants of current member in Date (day) level and counts them.

The “Multiple” version calls to an internal measure, similar as single version, for everyone of all selected members.

WITH

MEMBER Measures.[CountDays_Single] AS

COUNT(DESCENDANTS([Date].[Calendar].CURRENTMEMBER,[Date].[Calendar].[Date]))

MEMBER Measures.[CountDays_Multiple] AS

sum(existing [Date].[Calendar].[Month].MEMBERS,Measures.[CountDaysM])

MEMBER Measures.[CountDaysM] AS

COUNT(DESCENDANTS([Date].[Calendar].CURRENTMEMBER,[Date].[Calendar].[Date]))

SELECT {Measures.[CountDays_Single],Measures.[CountDays_Multiple]} ON COLUMNS

FROM [Adventure Works]

where ({[Date].[Calendar].[Month].&[2003]&[10],[Date].[Calendar].[Month].&[2003]&[11]})

2)      Check member is selected

In this case we want to check if a specific member is selected. In multiple version it means that we want to check if it’s one of selected members.

The solution is to check if the specific member EXISTS in the set of EXISTING members.

WITH

MEMBER Measures.[RightMemberIsSelected_Single] AS

iif( [Account].[Account].currentmember is [Account].[Account].&[48],'Yes','No')

MEMBER Measures.[RightMemberIsSelected_Multiple] AS

iif( COUNT(EXISTS ([Account].[Account].&[48],{EXISTING [Account].[Account].[All Accounts].children}))>0,'Yes','No')

SELECT {Measures.[RightMemberIsSelected_Single],Measures.[RightMemberIsSelected_Multiple]} ON COLUMNS

FROM [Adventure Works]

WHERE ({[Account].[Account].&[48],[Account].[Account].&[2]})