SSAS Calculated Member for measures that have the same Dimension

Question

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

Please provide example based on Adventure Works - we will find the solution for you. — Daniel Hanczyc
@DanielHanczyc - Giving an example based on AW could be a daunting task in many cases. On SO, you will find that most of the questions ON SQL or MDX, are not based on AW. — SouravA
I am designing the calculated column in SSAS, there is no error, it is just blindly subtracting b from a regardless of the dimensions. I want it to only subtract b from a where both measures have the same dimension value. Perhaps what I need is it to only subtract if it is not an unknown value during the aggregations, I am not sure. — feedthedogs
I am not getting you - "where both measures have the same dimension value"?? — SouravA

whytheq whytheq · Accepted Answer · 2015-01-08T21:31:04

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]
 );

SSAS Calculated Member for measures that have the same Dimension

2 Answers