SSAS add measure value for some dimention value

Viewed 31

I have fact table which looks like:

Fact Key, DIM ABC Key, AMOUNT, Other Dim Keys
1, 1, 0, ...
2, 2, 1, ...
3, 2, 2, ...
DIM ABC looks like:
DIM ABC Key, CODE, NAME
1, C1, N1
2, C2, N2
3, C3, N3

As You can see there is no fact for DIM ABC Key = 3 so I want to make some substitution by CODE:

SCOPE ([Measures].[AMOUNT], {[DIM ABC].[CODE].[C3]});
   This = [Measures].[Count];
END SCOPE;

But this seems to work only if i have not selected DIM ABC Key.

Below is MDX which is working without CODE selected but it adds new measure what is unwanted:

CREATE MEMBER CURRENTCUBE.[Measures].[AMOUNT5] AS
    IIF( [DIM ABC].[CODE].CURRENTMEMBER IS [DIM ABC].[CODE].[C3], [Measures].[Count], [Measures].[AMOUNT] )
, FORMAT_STRING = "Currency", VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'FACT 1';

Should I start to play with LEAVES/DESCENDANTS or something in SCOPE? I cannot do it by DIM Key becouse its surrogate key. [Measures].[Count] is from other fact which has same dimention references and has value for C3. Final data should behave like when You have fact as below:

Fact Key, DIM ABC Key, AMOUNT
    1, 1, 0
    2, 2, 1
    3, 2, 2
4, 3, 1
0 Answers
Related