Search code examples
ssasmdx

MDX query in cube calculation

I've created a simple cube calculation that sums two measures.I only want to sum, or to return data when both measures return a value.

When I use the calculation in an MDX query, it works as expected, however when I browse the cube via a pivot table I it display all results, and not what I need. It seems to me that I need to modify the cube calculation to get the same NONEMPTY behaviour as per the MDX query, but I just can't get the syntax correct, or know if this is indeed the correct approach. I'd be grateful for some pointers.

MDX Query

Sample of underlying data:

Underlying data

Cube calculation:

Simple addition of two measures

This MDX statement does exactly what I want it to:

MDX query result as required

Solution

  • The IIF function would be useful in this scenario

    For e.g.,

    CREATE MEMBER CURRENTCUBE.[Measures].[RevalCombined]
AS IIF([Measures].[Reval]=0, NULL, [Measures].[Reval]) + IIF([Measures].[dReval]=0, NULL, [Measures].[dReval])