ssasmdxolapiif

Aggregating Members calculated with IIF / CurrentMember conditions


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.


Solution

  • 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
        )