Conditional Calculated Measures

• michael.french 172

  Default port

  Points: 1472

  More actions

  October 30, 2012 at 1:37 pm

  #147635

  I am having an issue trying to add some conditional logic to a calculated measure. What I am trying to do is to account for a change in view formula prior to a certain date. The below MDX query produces the desired results.

  with member [Measures].[Monthly Adjusted Cost] as

  CASE

  WHEN StrToVal([Calendar].[YearMonthLabel].CurrentMember.Name) < 201101

  THEN [Measures].[Cost AMT]*0.95

  ELSE [Measures].[Cost AMT]

  END

  select non empty [Calendar].[YearMonthLabel].[YearMonthLabel].Members on rows

  , {[Measures].[Cost AMT], [Measures].[Monthly Adjusted Cost]} on columns

  from [CostCube]

  The question then becomes how to put the case statement into the actual Cube definition from the Calculations tab of the Cube Design for CostCube in Visual Studio. The forms that I have been trying end up with #value error saying I have an empty MDX Expression. The cube builds and does not produce an error.

  Please, let me know if anyone knows how best to do this.

  Thank you

• Mackers

  SSCrazy

  Points: 2935

  More actions

  October 31, 2012 at 7:51 am

  #1554600

  Try something like:

  with member [Measures].[Monthly Adjusted Cost] as

  IIF(Exists([Calendar].[YearMonthLabel].CurrentMember, {null:[Calendar].[YearMonthLabel].&[201101]}).COUNT > 0

  , [Measures].[Cost AMT]*0.95

  , [Measures].[Cost AMT])

  select non empty [Calendar].[YearMonthLabel].[YearMonthLabel].Members on rows

  , {[Measures].[Cost AMT], [Measures].[Monthly Adjusted Cost]} on columns

  from [CostCube]

  It may be simpler to add a scope statement in your cube rather than doing a calc

  scope {null:[Calendar].[YearMonthLabel].&[201101]};

  [Measures].[Cost AMT] = [Measures].[Cost AMT]*0.95;

  end scope;