Power BI dynamic column reference with DAX formula

Question

I have a Orders and ExchangeRates tables connected via a date key:

What I am trying to do is to fetch the correct currency exchange rate from ExchangeRates, which are organized in columns, with the column name matching the currency codes from Orders.

I'm basically trying to make a dynamic column reference to the EUR or JPY columns by using the matching Orders[orderCurrency] like this:

orderExchangeRate = LOOKUPVALUE(ExchangeRates[Orders[orderCurrency]],
ExchangeRates[date],Orders[date])

or:

orderExchangeRage = SELECTCOLUMNS(ExchangeRates,Orders[orderCurrency], ....)

But none of the functions accept a dynamic column reference, they expect the actual name of the column

How can I dynamically refer to the EUR and JPY columns based on Orders[orderCurrency]? Isn't there something similar to INDIRECT to make a dynamic reference in PowerBI?

Answer 1

As far as I know, there is no such function as INDIRECT in DAX.

You have (at least) two options:

• If it's just EUR and JPY, you could create two formulas and based on the currency switch between them with IF() or SWITCH(). Like: If (Currency = "EUR", LOOKUPVALUE(EUR), LOOKUPVALUE(JPY). Pseudo code, of course.

• Unpivot the EUR and JPY column in the ExchangeRate table. Then you'll have a line for each date and currency, and you can reference it as you like. Especially usefull with more currency combinations. You can Unpivot in the Query Editor, Transformation Tab.