PowerBI. Two apparently equal calculated measures are actually different. why?

Question

I end up with the following two calculated measures in Power BI which look semantically equal, but each produces a different result. I'd like to understand why. How does Power BI calculate each so they produce different results?

measure1 =
VAR var1 =
    CALCULATE ( [measure], table[column_1] = "some value in column 1" )
VAR var2 =
    CALCULATE ( var1, table[column_2] = "some value in column 2" )
RETURN
    var2

Literally, copy the RHS of var1 into its value in var2.

measure2 =
VAR var2 =
    CALCULATE (
        CALCULATE ( [measure], table[column_1] = "some value in column 1" ),
        table[column_2] = "some value in column 2"
    )
RETURN
    var2

The visual I'm using is a matrix in which the rows are table[column_2], don't know if it matters. In whatever case I'd say this two expresions should be equivalent... but they aren't

Alexis Olson Alexis Olson · Accepted Answer · 2020-10-28T16:40:11

When you define a variable with VAR, that value is treated as a constant when referenced later.

Therefore, in your first measure, var2 is the same as var1 since a constant is not changed by adjusting the filter context with CALCULATE.

If instead of defining a temporary variable, you defined var1 as a separate measure, then it would function as you expected it to in your first example.

As a side note, you can use multiple conditions rather than nesting CALCULATE functions:

measure3 =
CALCULATE (
    [measure],
    table[column_1] = "some value in column 1",
    table[column_2] = "some value in column 2"
)

PowerBI. Two apparently equal calculated measures are actually different. why?

1 Answers