Given the following formula's
IncorrectMeasure =
VAR FilteredValue =
CALCULATE(
[SomeMeasure],
FILTER('SomeTable', 'SomeTable'[SomeColumn] = "SomeValue")
)
VAR FinalResult =
CALCULATE(
FilteredValue,
FILTER('AnotherTable', 'AnotherTable'[AnotherColumn] = "AnotherValue")
)
RETURN FinalResult
IntermediateMeasure =
CALCULATE(
[SomeMeasure],
FILTER('SomeTable', 'SomeTable'[SomeColumn] = "SomeValue")
)
CorrectMeasure =
CALCULATE(
IntermediateMeasure,
FILTER('AnotherTable', 'AnotherTable'[AnotherColumn] = "AnotherValue")
)
Why do IncorrectMeasure
and CorrectMeasure
have different results? It seems that the filter in VAR FinalResult
is not being honored.
The variable is a scalar value returned. Measures are handled differently, the DAX engine wraps a measure in a CALCULATE during evaluation which triggers a context transition.
The second example will look like this during evaluation:
IntermediateMeasure =
CALCULATE(
CALCULATE(
[SomeMeasure e.g. SUM(Sales)]
),
FILTER('SomeTable', 'SomeTable'[SomeColumn] = "SomeValue")
)
Whereas the scalar value is fixed at evaluation time.