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.