CASE WHEN and NULL / 0 (zero)

When you have a CASE WHEN in MDX you basically have two options:

The Simple or Searched statement:

CASE [<<Set To Be Evaluated>>]

WHEN [<<condition1>>] THEN [<<action>>]

WHEN [<<condition2>>] THEN [<<action>>]

ELSE [<<action>>] END

Or the Full statement:

CASE

WHEN [<<Set To Be Evaluated>>] = [<<condition1>>] THEN [<<action>>]

WHEN [<<Set To Be Evaluated>>] = [<<condition2>>] THEN [<<action>>]

ELSE [<<action>>] END

Although this statement seem to be the same there is a difference in how they are processed by the SSAS MDX engine.

With the simple statement all possible actions are evaluated and then the “right” action is presented. Here is the catch: If one or more of the possible action result in NULL or 0 (zero), the action result is dominant on all other actions.

WITH
MEMBER [Measures].[Case When Simple NULL]
AS
CASE [Date].[Calendar].Currentmember
    WHEN [Date].[Calendar].[Calendar Year].&[2013]
        THEN    Null
    WHEN [Date].[Calendar].[Calendar Year].&[2014]
        THEN    2
    ELSE
        3
    END

MEMBER [Measures].[Case When Simple 0]
AS
CASE [Date].[Calendar].Currentmember
    WHEN [Date].[Calendar].[Calendar Year].&[2013]
        THEN    0
    WHEN [Date].[Calendar].[Calendar Year].&[2014]
        THEN    2
    ELSE
        3
    END

MEMBER [Measures].[Case When Simple NOT NULL or 0]
AS
CASE [Date].[Calendar].Currentmember
    WHEN [Date].[Calendar].[Calendar Year].&[2013]
        THEN    1
    WHEN [Date].[Calendar].[Calendar Year].&[2014]
        THEN    2
    ELSE
        3
    END

MEMBER [Measures].[Case When Full]
AS
CASE
WHEN [Date].[Calendar].Currentmember IS [Date].[Calendar].[Calendar Year].&[2013]
    THEN    NULL
WHEN [Date].[Calendar].Currentmember IS [Date].[Calendar].[Calendar Year].&[2014]
    THEN    2
ELSE 3 END

select
{
      [Measures].[Case When Simple NULL]
    , [Measures].[Case When Simple or 0]
    , [Measures].[Case When Simple NOT NULL 0]
    , [Measures].[Case When Full]
} ON Columns,
{
      [Date].[Calendar].[Calendar Year].&[2013]
    , [Date].[Calendar].[Calendar Year].&[2014]
} ON ROWS
FROM [Adventure Works]

This will result in:

As you can see with the Full Statement, First the conditions are checked before calculating the action, resulting in the “right” answer.

So please try to avoid using the simple statement when possible.

Have Fun And Till Next Time