MDX ‘Is’ Operator
Wednesday, July 4, 2012
by jsalvo
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 | ||
| 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 | |||
| G173 Total | 10 | 10 | 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 