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