Saturday, June 6, 2015

Confusing = with IS in MDX calculations

This topic has been written about many times, perhaps even by me. However, it is a common pitfall, so I am writing about it again. See this msdn post for a recent example.

Essentially, the pitfall is when an author wants to check if the current member of a dimension attribute is at a particular member. The incorrect MDX might look like this
IIF(DimCustomer.CustomerClass.Currentmember = DimCustomer.CustomerClass.CustomerClass.&[Gold], Measures.Price*0.9,Measures.Price)

However, the "=" operator is actually testing if the tuples on either side are equal in their Measure. Since there is no Measure specified, the measure will be the one on that slice (tuple), or the default measure if there isn't one in the tuple. For the two tuples to be equal is rather random. So the results will be somewhat unpredictable.

What should be used is the "IS" operator, which will test if the members on either side are the same. That is; is the current CustomerClass Gold? So the IIF() should be rewritten as
IIF(DimCustomer.CustomerClass.Currentmember IS DimCustomer.CustomerClass.CustomerClass.&[Gold], Measures.Price*0.9,Measures.Price)

Note, if you are having problems following this logic, the IIF() with the IS operator is equivalent to the following statement.
IIF("key") = "Gold", Measures.Price*0.9,Measures.Price)

No comments: