An MDX newbie here. I am trying to define and aggregate a custom measure which contains an IIF condition. Suppose, for this data model:
Category | Name | ItemType | Price |
---|---|---|---|
CatA | Item1 | Normal | 4.0 |
CatA | Item2 | Precious | 5.0 |
I define the measure like this:
WITH Member [Measures].[WeightedMeasure] AS IIF(
[ItemType].CurrentMember IS [ItemType].[Precious],
2 * [Measures].[Price],
[Measures].[Price]
)
This calculates correctly on the leaf level, so if I have ItemType in the dimensions, all is fine. However if I try to aggregate by Category, IIF condition does not match anymore (as it is on the 'leaf' member). I cannot add a column with pre-scaled Price as the factor (2 in the example) can be dynamic. I also need to keep it as a measure (and not part of the query), as I need to plug it in later into TopCount (for example to show the most expensive Categories).
It seems these questions try to achieve something similar: MDX IIF Statement aggregation (not answered)
MDX IIF statement to calculate new member basing on measure and hierarchy leaves (needs a custom column, so cannot do a dynamic weight)
I have tried manipulating IIF to have a condition on 'descendants' - but not sure if possible. Also read about 'SCOPED' / filtered queries - but I cannot adapt the examples to my simple schema.
My approach assumes you're calculating at a level where Category members have ItemType children that can be iterated over, it may not directly solve the aggregation issue at higher levels if ItemType isn't part of your current context or slice in the cube.
WITH
Member [Measures].[WeightedPrice] AS
SUM(
[Category].CurrentMember.Children,
CASE WHEN [ItemType].CurrentMember IS [ItemType].[Precious]
THEN 2 * [Measures].[Price]
ELSE [Measures].[Price]
END
)