Context Transition in SUMX Function
Yesterday I have read an article “Currency conversion in DAX for Power BI and SSAS” from Kasper de Jonge. He used a very neat technique for that.
But I think there might be some confusions from some people, why this technique works, especially how the context flow down to the lookup table.
There are only 2 measures:
- TrnExchangeRate = Min(FactExchangeRate[Factor])
- TotalSales = if(HASONEVALUE(ReportCurrency[ReportCurrency]),
SumX( FactSales, [Sales] * [TrnExchangeRate]))
The pivot table:
Some people might ask, for this measure “SumX( FactSales, [Sales] * [TrnExchangeRate])”, why iterating FactSales table can filter down to the “1-side” lookup table TransactionCurrency and ExchangeDate?
The trick here is, [TrnExchangeRate] is a measure. When it is used in another measure, it will be automatically wrapped with CALCULATE, which will perform the context transition from row context to filter context. Important is, the Fact table “FactSales” in SUMX is expanded and includes all its 1-side lookup tables. So when context transition happens, the filter context will also affect the lookup tables.
