SSAS MDX Calculated Member, multiple scopes

Question

Imagine a cube with dimensions - DimA, DimB, DimC and DimD.

I want a calculated Member that should only be calculated if sliced against DimA or DimB. If only sliced against DimC or DimD, then it should return Null.

Currently I have achieved this, by using the 3 calculated members below:

Create Member CurrentCube.[Measures].[CalMeasure1] As Null, VISIBLE = 0; 

SCOPE([Measures].[CalcMeasure1]); 
       SCOPE ([DimA].[DimA].[DimA]); 
            This = [Measures].[Camp Index]; 
       END SCOPE; 
END SCOPE; 


Create Member CurrentCube.[Measures].[CalcMeasure2] As Null, VISIBLE = 0; 

SCOPE([Measures].[CalcMeasure2]); 
       SCOPE ([DimB].[DimB].[DimB]); 
            This = [Measures].[Camp Index]; 

CREATE MEMBER CURRENTCUBE.[Measures].[CalcMeasure3]
 AS IIF(ISEMPTY([Measures].[CalcMeasure1]),[Measures].[CalcMeasure2],[Measures].[CalcMeasure1]), 
VISIBLE = 1  ; 
       END SCOPE; 
END SCOPE;

I have to create quite a few of these, so ideally, I wanted to do this with a single measure, rather than 3 separate measures. I have tried creating a nested scope. I have also tried putting both dimension hierarchies within a single scope. Neither of these methods work, however. Both methods require that the measure is sliced against both DimA AND DimB. I need it to work if it is sliced against either DimA OR DimB.

Note My code above DOES work, I just want to condense it to a single calculated member

Edit:- Expected Results, for different pivot table usages.

DimA Attribute    CalcMeasure3  
1                 1.1  
2                 1.1  
3                 8.6  


DimB Attribute    CalcMeasure3  
4                 2.1  
5                 2.1  
6                 9.6  


DimA Attribute    DimC Attribute    CalcMeasure3  
1                 A                 1.1  
2                 B                 1.1  
3                 C                 8.6  


DimB Attribute    DimD Attribute    CalcMeasure3  
4                 D                 1.1  
5                 E                 1.1  
6                 F                 8.6  

DimC Attribute    CalcMeasure3  
A                 (Null)  
B                 (Null)  
C                 (Null)  


DimD Attribute    CalcMeasure3  
D                 (Null)  
E                 (Null)  
F                 (Null)

Tom Huang Tom Huang · Accepted Answer · 2017-05-17T13:58:03

How about this calculated measure, without scope:

WITH MEMBER [Camp Index] AS
IIF( 
    not ([DimA].[DimA].CurrentMember is [DimA].[DimA].[All]) or not([DimB].[DimB].CurrentMember is [DimB].[DimB].[All]), 
[You Calcuate],
IIF( 
    not ([DimC].[DimC].CurrentMember is [DimC].[DimC].[All]) or not([DimD].[DimD].CurrentMember is [DimD].[DimD].[All]),
    NULL,
    [You Calcuate]
),
NULL 
)
SELECT
{[Camp Index]} on 0
from [Adventure Works]

SSAS MDX Calculated Member, multiple scopes

2 Answers