MDX: calculated member, Comparing 2 dimension attributes

Question

I am very new to SSAS and its corresponding language MDX. So this is probably a newbie question.

I have a cube with a fact table, games, and 2 dimensions attached, players and events, repectively. Each dimension has a date.

What I want to do is create a calculated member to count the number of new players for each event, and exclude the ones that already played in previous events.

A logic representation of the code would be this:

DistinctCount(
   IIF(
      [Customer].[Date registered as player] > [Sponsored events].[Start date]
      ,1
      ,0
   )
)

But this does not work in MDX.

if we have DISTINCTCOUNT({1,1,1,1,1,...0,0,0,0,}) then the answer is always 2 so we can see why that representation isn't going to give the correct answer. — whytheq

whytheq whytheq · Accepted Answer · 2018-01-08T23:18:09

Really tricky to help without more information about the structure of your cube - maybe you could add a screenprint showing us the dimensions and hierarchies.

You could use an approach something like this:

SUM(
   [Customer].[Customer].[Customer].members
   ,IIF(
      [Customer].[Date registered as player].currentmember.membervalue 
          > [Sponsored events].[Start date].currentmember.membervalue
      ,1
      ,NULL
   )
)

The above is really just pseudo-code without more information about your cube structure.

MDX: calculated member, Comparing 2 dimension attributes

1 Answers