Search code examples
multidimensional-arrayssasmdxcubeolap-cube

MDX error trying to compare one hierarchy level to another one

I have an MDX issue that I really don't understand with a 5 level hierarchy "SEGMENTATION" : AFFAIRE/NIVEAU 1/ NIVEAU 2/NIVEAU 3/NIVEAU 4

I want to compare "NIVEAU 1" sub-levels weight to "Niveau 1". For instance, I want to know for each 'NIVEAU 3' members its contributions part for its "NIVEAU 1".

I've tried a bunch of things, but nothing works properly. I don't get the trick and is stucked to :

 WITH MEMBER [Measures].[TEST] AS'
iif(ISEMPTY(([Segmentation].[Niveau1], [Measures].[Total])) OR ([Segmentation].[Niveau1],[Measures].[Total]) = 0
    , NULL
    ,[Measures].[Total] / ([Segmentation].[Niveau1], [Measures].[Total])
)'
          
SELECT NON EMPTY { [Measures].[TEST],[Measures].[Total]} ON COLUMNS
, NON EMPTY { [Segmentation].[Niveau2]}
ON ROWS FROM ( SELECT ( { [Segmentation].[Niveau1].&[8589934592]&[1|DESC111] } ) ON COLUMNS FROM [CUBE]) // Only one "Niveau 1" focus

And I get :

enter image description here

<Niveau 2>                TEST           Total
SF - C...                 #Error      25143658
SF - M...                 #Error       1638913,5
ZZZ  ...                  #Error      90468628

#Error : The EqualTo function expects a string or numeric expression for argument 1. A tuple set expression was used.

The expected result is :

<Niveau 2>                 TEST         Total
SF - C...                21,44%      25143658
SF - M...                 1,40%       1638913,5
ZZZ  ...                 77,16%      90468628

21,4% = 25143658/(25143658+1638913,5+90468628)

What's wrong with my MDX? Is there a mistake among the dimension or hierarchy set up?

Solution

  • Tuples are written as comma separated lists of members. What you have is a dimension.

    Try

    [Segmentation].CurrentMember.Parent

    Instead of

    [Segmentation].[Niveau1]

    On your measure definition.

    [EDIT] As mentioned in a comment, the goal is a solution that works on all levels. The solution is to use

    Ancestor( [Segmentation].CurrentMember, [Segmentation].[Niveau1] )

    in the Tuple used in the custom measure definition.