Using DAX variables in iterators

I was having a discussion earlier this week about the use of variables I used in my currency conversion blog post . The question was why I am using 2 different measure to calculate the sales for each currency. So I am using 2 measures together:

TrnExchangeRate =
MIN ( FactExchangeRate[Factor] )

and

TotalSales =
IF (
    HASONEVALUE ( ReportCurrency[ReportCurrency] ),
    SUMX ( FactSales, [Sales] * [TrnExchangeRate] )
)

As explained in the blog post the SUMX in this calculation will iterate over each row in the fact table which probably will have multiple currencies with different values for each date. The Min(FactExchangeRate[Factor]) will be evaluated for each currency and date and get the right value.

Now for those of you who have seen any of my sessions will ask why am I not using a variable as I always tell everyone to do so. Something like this would be very tempting to create:

TotalSales =
VAR TrnExchangeRate =
    MIN ( FactExchangeRate[Factor] )
RETURN
    IF (
        HASONEVALUE ( ReportCurrency[ReportCurrency] ),
        SUMX ( FactSales, [Sales] * TrnExchangeRate )
    )

The main problem here is that even though SUMX iterates over each row in the fact table the Min(FactExchangeRate[Factor]) will be executed only once as the variable is placed outside of the SUMX. That is how variables work, values get stored into the variable for reuse, in this case it is stored at the beginning, before the loop. Of course that is not a result you would expect.

Now it is possible to use variables inside the loop as well. You should write DAX like this:

TotalSales =
IF (
    HASONEVALUE ( ReportCurrency[ReportCurrency] ),
    SUMX (
        FactSales,
        VAR TrnExchangeRate =
            CALCULATE ( MIN ( FactExchangeRate[Factor] ) )
        RETURN
            [Sales] * TrnExchangeRate
    )
)

In this DAX calculation we now placed the variable assignment into the SUMX, now it gets evaluated for each row in the sales table and giving us the right value.

Hope this helps with understanding DAX and variables a bit better.

BTW if you are using Azure AS and have access to calculation groups, check out this approach that I would recommend to use instead of this approach.

10 Replies to “Using DAX variables in iterators

  1. Hi,
    If I’m not mistaken, you could also simply force a row to filter context transition using a calculate, and avoid using Var
    SUMX (
    FactSales,
    [Sales] * calculate (MIN ( FactExchangeRate[Factor] )))

    1. Wouldn’t this alternative perform worse at scale since the context transition is still dynamic per row?

      1. it needs to otherwise you get wrong results. It is the same as using the measure, any measure gets an implicit calculate wrapped around it.

  2. Shouldn’t the code read:

    TotalSales =
    IF (
    HASONEVALUE ( ReportCurrency[ReportCurrency] ),
    SUMX (
    FactSales,
    VAR TrnExchangeRate =
    CALCULATE ( MIN ( FactExchangeRate[Factor] ) )
    RETURN
    [Sales] * TrnExchangeRate
    )
    )

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.