Search code examples
ssasmdxolap

Slicing inside of a calculated measure defintion? (MDX)


I have a many-to-many relationship between dimension D and measure M.

I need to create a generic calculated measure that would have the following formula: M/Σ(M), where Σ stands for the sum of all measure's facts that are associated with at least one member of a given dimension.

It's easy for other (one-to-many) dimensions, but getting Σ in many-to-many... well, if it was just a regular MDX query, that would be easy as well. I could just slice on all children of a dimension:

SELECT [Measures].[M] ON 0
FROM [MyCube]
WHERE [D].[All].CHILDREN

But how do I slice in a calculated measure?

A simplified example of what I would expect to work:

CREATE MEMBER [Measures].[Calc] AS
  [Measures].[M] / ( DRILLUPLEVEL( AXIS( 1 ).ITEM( 0 ).HIERARCHY.MEMBERS ).ITEM( 0 ), [Measures].[M] )
  WHERE DRILLUPLEVEL( AXIS( 1 ).ITEM( 0 ).HIERARCHY.MEMBERS ).ITEM( 0 ).CHILDREN

But of course, MDX doesn't support a WHERE clause in MEMBER definitions.

Please advise me how to approach this.


Solution

  • In the end, this is what I got:

    CREATE HIDDEN [Total M] =
      AGGREGATE(
        DESCENDANTS(
          AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
          AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS.COUNT
        ) - AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
        [Measures].[M]
      );
    

    In short, I subtracted the root member from the set of the dimension's members. Aggregating over the remaining set gives proper value in many-to-many relationships. I can now use this measure in other measures to calculate M/Σ(M), etc.