Power BI中用DAX实现动态列引用获取对应汇率的技术问询

Hey there! Let's work through your problem of dynamically fetching exchange rates based on the orderCurrency column in your Orders table. Your original LOOKUPVALUE formula doesn't work because DAX can't directly use a column value (like Orders[orderCurrency]) as a reference to another table's column name—we need a different approach to handle this dynamic selection.

Recommended Approach: Unpivot the ExchangeRates Table (Best Practice) #

The column-based structure of your ExchangeRates table isn't ideal for DAX's relational model. Converting it to a narrow (long) table will make the lookup seamless and scalable:

1. Transform ExchangeRates in Power Query

   • Load your ExchangeRates table into Power Query.
   • Select the date column, then right-click and choose Unpivot Other Columns.
   • Rename the resulting columns to CurrencyCode (the former column names like EUR/JPY) and ExchangeRate (the corresponding rate values).
   • Your table will now have three columns: date, CurrencyCode, ExchangeRate.

2. Create Relationships

   • In the Data Model, link Orders to the transformed ExchangeRates table:
     • Orders[date] ↔ ExchangeRates[date]
     • Orders[orderCurrency] ↔ ExchangeRates[CurrencyCode]

3. Add the Calculated Column
   Now you can simply use the RELATED function to pull in the matching rate:

   orderExchangeRate = RELATED(ExchangeRates[ExchangeRate])
   

   For measures (if you need it in a report context), use:

   Order Exchange Rate = CALCULATE(SELECTEDVALUE(ExchangeRates[ExchangeRate]))
   

Alternative: Use SWITCH for Static Currency Enumeration #

If you can't modify the ExchangeRates table structure, you can explicitly map each currency code to its column using SWITCH. Note that this requires updating the formula if you add new currency columns later:

orderExchangeRate = 
VAR CurrentCurrency = Orders[orderCurrency]
VAR CurrentOrderDate = Orders[date]
RETURN
SWITCH(
    CurrentCurrency,
    "EUR", LOOKUPVALUE(ExchangeRates[EUR], ExchangeRates[date], CurrentOrderDate),
    "JPY", LOOKUPVALUE(ExchangeRates[JPY], ExchangeRates[date], CurrentOrderDate),
    "USD", LOOKUPVALUE(ExchangeRates[USD], ExchangeRates[date], CurrentOrderDate),
    -- Add more currency codes here as needed
    BLANK() -- Default value if no matching currency is found
)

Why Your Original Formula Failed #

The first argument of LOOKUPVALUE expects a specific column reference (like ExchangeRates[EUR]), not a dynamic value from another column. DAX resolves column references at query compilation time, so it can't use row-level values (like Orders[orderCurrency]) to pick a column dynamically without extra handling.

内容的提问来源于stack exchange，提问作者Panda Coder