Monday, July 25, 2011

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.

MEMBER Measures.[CountDays_Single] AS

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

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.

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


  1. Thanks for this post. Thanks to it I managed to solve an issue with our cubes. EXISTING made my life easier :)

  2. Finally the EXISTING clause led our MDX queries to considerably slow down performance. Doing some research we found it is not really recommended to use EXISTING as this disables "block-computation" (kind of a cache). We finally found a solution for our queries by selectively using currentmember or not depending on a real measure from DB.