CODEWITHSUNDEEP
ssashierarchymdx
Preet Sangha
Preet Sangha

Reputation: 65555

MDX; Get aggregation of measure over all values in a hiercharchy except named ones

This tuple gets me an aggregation of measure X in the dimension of [MyHierarchy].[MyHierarchy].&[some_value].

([Measures].[X], [MyHierarchy].[MyHierarchy].&[some_value])

But what is the complement - ie Measures.X in the same hierarchy but excluding [MyHierarchy].[MyHierarchy].&[some_value]

I'm sure it's the EXCEPT function but I can't seem to get the syntax right.

Upvotes: 1

Views: 2448

Answers (2)

Benoit
Benoit

Reputation: 1993

The following expresion works with non linear aggregation functions:

WITH MEMBER [Measures].[Y] as 
       Aggregate( 
         Except( [MyHierarchy].[MyHierarchy].&[some_value].Level.Members, { [MyHierarchy].[MyHierarchy].&[some_value] } ), 
         [Measures].[X] )
       )

You can generalize it when you have to exclude several members that belong to the same level (the excluded members are in the excludedMembers set):

WITH MEMBER [Measures].[Y] as 
       Aggregate( 
         Except( excludedMembers.Item(0).Level.Members , excludedMembers ), 
         [Measures].[X] )
       )

Upvotes: 1

Marc Polizzi
Marc Polizzi

Reputation: 9375

The general idea to aggregate over any kind of set is something like:

with member [Measures].[Y] as 
       aggregate( 
         except( [hierarchy].members, { excluded } ), 
         [Measures].[X] )
       )

Aggregate() is handling whatever actual aggregation of the [Measures]. The issue is that if [hierarchy].members contains some intermediate (parent) members, you'll have values counted twice.

If the aggregation is SUM and you've an all member, then the following is giving the expected result :

with member [Measures].[Y] as 
       ( [hierarchy].[all], [Measures].[X] ) 
       - sum( { excluded }, [Measures].[X] )

If no all then you can use members of the first level:

with member [Measures].[Y] as 
       sum( [hierarchy].levels(0), [Measures].[X] ) 
       - sum( { excluded }, [Measures].[X] )

_

Upvotes: 2

Related Questions