MDX custom consolidation: consolidate only last level descendants

Question

I'm looking for a elegant MDX expression that sum values only of the elements of last level dimension:

I have a measure M , also I have a hierarchical parent - child dimension U that is non balanced tree:

R -> ( M = R1 + R2 = 157 )
..R1 ->  ( M = R11 + R12 = 150 )
...R11 ->   ( M=R111 = 100 )
.....R111 ->   M=100
...R12 ->  M = 50
..R2 -> M = 7

I have a set that contains some elements from this dimension:

S contains R11, R111, R12

Now I need to take, for a U.currentMember the M value (that is, the sum of last level descendants)

I have written this expression, it works but perhaps they are a more elegant way to write it:

with member measures.XX as
sum ( 
       intersect( 
            [S], 
            Except( 
              descendants( [U].currentMember ),
              existing( descendants( [U].currentMember ).item(0) )
            )                 
       ) 
   , 
   [M]
)    
select 
 measures.xx on columns
from [CUBE]
 where [U].[R]

Note: This MDX dont run:

with member measures.XX as
sum ( 
       intersect( 
            [S], 
            descendants( [U].currentMember )
       ) 
   , 
   [M]
)    
select 
 measures.xx on columns
from [CUBE]
 where [U].[R]

because return 250 insteat 150.

Right result is 150: R11 + R12 (because R111 is included in R11).

Bad result is: 250: '100' value is taked for twice R11 + R111.

Final Solution:

with member measures.XX as
sum(    
    intersect ( 
       descendants([U].currentMember,,leaves),
       [S]
    ) 
   , 
   [M]
)    
select 
 measures.XX on 0,
descendants( [Unitats].[Unitat].[All] ) on 1
   from [C]

Sounds really strange, it's not a bug from SSAS ? did you create the measure with SUM as aggregation method — ic3
Hi @icCube, yes, it is very bizarre. And yes, SUM is the aggragation method. — dani herrera
You don't have an issue with your facts and are defining a value for R11 member ? you can check with drillthrough function — ic3

ic3 ic3 · Accepted Answer · 2011-11-13T19:23:58

Not sure what you want to calculate but let's assume [Member] is the member you want to evaluate :

I'd use the descendants, filter and isLeaf MDX functions :

 Sum( 
   Filter( Descendants( [Member] ), isLeaf(Member_hierarchy.currentmember) )
   ,[M])

You're adding all descendants including itself that are leafs (no children).

MDX custom consolidation: consolidate only last level descendants

1 Answers