Search code examples
dax

DAX Multiple measures different result than one measure with variables

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.

Solution

  • 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.