# MDX ‘Is’ Operator

I recently encountered a business scenario involving calculated members in a cube that I thought is worth sharing.

We have a cube with the following measures:  Shipped Quantity, Forecast Quantity, and ABS Variance to Forecast.

ABS Variance to Forecast is a calculated member in the cube, the MDX is pretty simple:

IIF ([Measures].[Forecast Quantity]<>0 OR [Measures].[Shipped Quantity]<>0
,ABS([Measures].[Forecast Quantity]-[Measures].[Shipped Quantity])
,Null)

As you can see in the example below, when the ABS Variance to Forecast is calculated at the part type level, it is using the aggregated values of the Shipped Quantity and Forecast Quantity and subtracting them.

 Part Type Item Code Item Description Shipped Quantity Forecast Quantity ABS Variance to Forecast Bag B001 Backpack 5 7 2 L005 Laptop Case 10 9 1 M123 Messenger Bag 20 10 10 W187 Wheel Bag 5 9 4 D137 Duffle Bag (Wheeled) 13 15 2 G173 Grocery Bag 10 10 0 C183 Cargo Transport 5 3 2 T183 Trunk 4 5 1 Total 72 68 4

This was not the behavior the user was looking for, instead he wanted the ABS Variance to Forecast at the part type level to be the Sum of the ABS Variance to Forecast at the Item level.  In the example above, the result would be 22.

At first, it appeared that creating a new calculated member with the following MDX would do the trick.

SUM([Item].[Item Code].[Item Code].MEMBERS,[Measures].[ABS Variance to Forecast])

This did yield the correct values calculated at the part type level, but caused issues for the totals at the item code level.

 Part Type Item Code Item Description Shipped Quantity Forecast Quantity ABS Variance to Forecast New ABS Variance to Forecast Bag B001 Backpack 5 7 2 2 B001 Total 5 7 2 22 L005 Laptop Case 10 9 1 1 L005 Total 10 9 1 22 M123 Messenger Bag 20 10 10 10 M123 Total 20 10 10 22 W187 Wheel Bag 5 9 4 4 W187 Total 5 9 4 22 D137 Duffle Bag (Wheeled) 13 15 2 2 D137 Total 13 15 2 22 G173 Grocery Bag 10 10 0 0 G173 Total 10 10 0 22 C183 Cargo Transport 5 3 2 2 C183 Total 5 3 2 22 T183 Trunk 4 5 1 1 T183 Total 4 5 1 22 Total 72 68 4 22

The totals at the item level were the same as the total at the part type level, definitely not correct.

To resolve this issue, my co-worked introduced me to a new operator in MDX: ‘Is’.  The ‘Is’ operator is used to check whether two objects are equivalent. In this case, we needed to check if the CurrentMember was equal to the DefaultMember (‘All’).  If the CurrentMember is equal to the DefaultMember, then we are at the ‘All’ level of the hierarchy and need to use the SUM function, otherwise we use the value of the ABS Variance to Forecast measure.

IIF([Item].[Item Code].CurrentMember Is [Item].[Item Code].DefaultMember ,SUM([Item].[Item Code].[Item Code].MEMBERS,[Measures].[ABS Variance to Forecast]),[Measures].[ABS Variance to Forecast])

This produced the correct results and I learnt something new about MDX

## Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.