SSAS calculated measure not slicing with its filtered dimension

Question

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  ;

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

vldmrrdjcc · Accepted Answer · 2020-09-24T08:35:38.633

2

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  ;

edited Sep 24 '20 at 08:35

answered Sep 23 '20 at 07:32

vldmrrdjcc

2,082
5
22
41

so it's expected this way! Can't we change this behavior to show value only for CA2 in the first case and split for CA1 and CA2 in the second case, as it happens in DAX unless otherwise we use ALL. – Arjun Sep 23 '20 at 14:44
1

Oh, I think i understood now: for the first case you want to see the value only for CA2 and 0 for all other members. – vldmrrdjcc Sep 23 '20 at 14:50
thanks for the MDX expession. Is there a way I can write something like this for 2nd case too? – Arjun Sep 23 '20 at 23:01
1

Take a look at the edited answer, so in the second case, it will show aggregate numbers for CA1 and CA2 members, and 0 otherwise. – vldmrrdjcc Sep 24 '20 at 08:37
Thanks for the second expression. I was looking for a function or some dynamic way of doing this as I have a lengthy list of members to be filtered. – Arjun Sep 24 '20 at 16:12
You can probably create a dynamic set and use it to filter members. – vldmrrdjcc Sep 24 '20 at 20:49
I think you meant named set, I tried that but no luck. – Arjun Sep 25 '20 at 12:06

SSAS calculated measure not slicing with its filtered dimension

1 Answers1