Search code examples
ssasmdxcube

SSAS Calculated Member for measures that have the same Dimension


I am trying to create a calculated measure that subtracts measures that are in different groups, but only if they have the same dimension member whatever that may be. (edit - essentially exclude UnknownMember numbers in the subtraction)

I have tried using Scope:

CREATE MEMBER CURRENTCUBE.[Measures].[CalcField] as NULL;

SCOPE([Dimension1].[DimensionField1].MEMBERS);
    [Measures].[CalcField] = [Measures].[a] - [Measures].[b];
END SCOPE;

I have also tried using a Tuple without success:

CREATE MEMBER CURRENTCUBE.[Measures].[CalcField]
AS ([Measures].[a] - [Measures].[b], [Dimension1].[DimensionField1]);

But I think there is something fundamental that I am missing

Edit

col a b dimension
1. 9 0 x
2. 0 2 x
3. 1 5 null

If you aggregate those rows I want the answer 7 not 5

Using a simple CASE WHEN works if the dimensions in use, but otherwise it blindly subtracts everything again

CASE 
  WHEN [Dimension1].[DimensionField1] IS [Dimension1].[DimensionField1].UnknownMember THEN 0 
  ELSE [Measures].[a] - [Measures].[b]
END

Using Aggregate works at the high level, but then when I use the Dimension I get no per member results

Aggregate(
  EXCEPT(
    [Dimension1].[DimensionField1].Members, {[Dimension1].[DimensionField1].UnknownMember, [Dimension1].[DimensionField1].[All]}
  ),[Measures].[a]) 
- 
Aggregate(
  EXCEPT(
    [Dimension1].[DimensionField1].Members, {[Dimension1].[DimensionField1].UnknownMember, [Dimension1].[DimensionField1].[All]}
  ),[Measures].[b]) 

Workaround Solution
I am sorry if I was not clear on my problem, but I have ended up solving this problem by putting the data I need in at the ETL stage rather than calculating it in the Cube


Solution

  • Did you try either Summing or Aggregating your second snippet?

    CREATE MEMBER CURRENTCUBE.[Measures].[CalcField]
    AS 
     Sum(
        [Measures].[a] - [Measures].[b], 
        [Dimension1].[DimensionField1]
     );
    
    CREATE MEMBER CURRENTCUBE.[Measures].[CalcField]
    AS 
     Aggregate(
        [Measures].[a] - [Measures].[b], 
        [Dimension1].[DimensionField1]
     );