Context Transition in SUMX Function

Posted on

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.

image-20

There are only 2 measures:

  1. TrnExchangeRate = Min(FactExchangeRate[Factor])
  2. TotalSales = if(HASONEVALUE(ReportCurrency[ReportCurrency]),

SumX( FactSales, [Sales] * [TrnExchangeRate]))

The pivot table:

image_thumb-21

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s