SSAS calculated measure not slicing with its filtered dimension

301 Views Asked by At

I have created a measure like this, the problem is it's not slicing with its own dimension. Is it expected this way or am I missing something. I am new to MDX scripting, any help would be highly appreciated.

CREATE MEMBER CURRENTCUBE.[Measures].[CHARGE_CODE_TEST]
 AS Aggregate([Charege].[Station].&[CA2],[Measures].[Inv Line Amt]), 
VISIBLE = 1  ; 

enter image description here

CREATE MEMBER CURRENTCUBE.[Measures].[CHARGE_CODE_TEST]
     AS Aggregate({[Charege].[Station].&[CA1],[Charege].[Station].&[CA2]},[Measures].[Inv Line Amt]), 
    VISIBLE = 1  ; 

enter image description here

1

There are 1 best solutions below

7
On BEST ANSWER

It should work like that - it works the way you wrote it - no matter against which member of [Charege].[Station] hierarchy you are looking [CHARGE_CODE_TEST] measure, it will always aggregate [Inv Line Amt] for the [Charege].[Station].&[CA2] in the first case, and CA1 and CA2 in the second case. If you remove these members from Aggregate function then it will show the numbers in the context of the current cell, like [Measures].[Inv Line Amt] does.

Try something like this for the first case:

CREATE MEMBER CURRENTCUBE.[Measures].[CHARGE_CODE_TEST]
 AS IIf([Charege].[Station].CurrentMember IS [Charege].[Station].&[CA2], ([Charege].[Station].&[CA2],[Measures].[Inv Line Amt]), 0)  , 
VISIBLE = 1  ; 

And, if I understood correctly, this should be the second case:

    CREATE MEMBER CURRENTCUBE.[Measures].[CHARGE_CODE_TEST]
     AS IIf([Charege].[Station].CurrentMember IS [Charege].[Station].&[CA2]
OR [Charege].[Station].CurrentMember IS [Charege].[Station].&[CA1], ({[Charege].[Station].&[CA1],[Charege].[Station].&[CA2]},[Measures].[Inv Line Amt]), 0)  , 
    VISIBLE = 1  ;