CODEWITHSUNDEEP
sql-serverssasmdxolap
Kulis
Kulis

Reputation: 1010

OLAP Calculated Measure based on another Calculated Measure

How I can add to cube calculations (New Calculated Measure) which based on another calculated measure? Simply referencing to this name, gives me NULL (I don't get error). After changing to different measure I get correct value.

Measure 1:

[Measures].[A] + [Measures].[B] - [Measures].[C]

Measure 2:

AGGREGATE(NULL:TAIL(EXISTING [Date].[Date].[Date].Members).Item(0), [Measures].[Measure 1])

Upvotes: 0

Views: 839

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Use the calculation from your first calculated measure and add to it to build the calculation for your new calculated measure.

EDIT:

As an example of what I mean, say you have [measures].[CalcMeasure1]

that has a calculation (definition) of [measures].[measure_A] + [measures].[measure_B]

And you wanted to create [measures].[CalcMeasure2] that adds measure_C to CalcMeasure1, you would give it a definition of:

[measures].[measure_A] + [measures].[measure_B] + [measures].[measure_C]

So to use the code from your question, your Measure 2 would be:

AGGREGATE(NULL:TAIL(EXISTING [Date].[Date].[Date].Members).Item(0), ([Measures].[A] + [Measures].[B] - [Measures].[C]))

According to MSDN:

Expression: Specify the expression that produces the values of the calculated member. This expression can be written in Multidimensional Expressions (MDX). The expression can contain any of the following:

•Data expressions that represent cube components such as dimensions, levels, measures, and so on

•Arithmetic operators

•Numbers

•Functions

You can drag or copy cube components from the Metadata tab of the Calculation Tools pane to quickly add them to an expression.

Any calculated member that is to be used in the value expression of another calculated member must be created before the calculated member that uses it.

Upvotes: 1

Related Questions