Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is building a Power BI model for a global sales organization. The model contains a 'Sales' table with 'OrderID', 'OrderTotalLocalCurrency', and 'CurrencyID'. A 'Currency Exchange Rate' table contains 'CurrencyID', 'ExchangeRate', and 'Date'. The modeler needs to convert 'OrderTotalLocalCurrency' to USD based on the 'Date' of the order. Which DAX pattern enables this row-level currency conversion?
- ASUMX('Sales', 'Sales'[OrderTotalLocalCurrency] * RELATED('Currency Exchange Rate'[ExchangeRate]))
- BSUM('Sales'[OrderTotalLocalCurrency]) * AVERAGE('Currency Exchange Rate'[ExchangeRate])
- CCALCULATE(SUM('Sales'[OrderTotalLocalCurrency]), USERELATIONSHIP('Sales'[CurrencyID], 'Currency Exchange Rate'[CurrencyID]))
- DLOOKUPVALUE('Currency Exchange Rate'[ExchangeRate], 'Currency Exchange Rate'[CurrencyID], 'Sales'[CurrencyID])
Show answer & explanationAnswer & explanation
Correct answer: A. SUMX('Sales', 'Sales'[OrderTotalLocalCurrency] * RELATED('Currency Exchange Rate'[ExchangeRate]))
SUMX iterates through each row of the 'Sales' table, and for each row, RELATED retrieves the corresponding exchange rate from the 'Currency Exchange Rate' table based on the relationship, allowing for accurate row-level conversion before summing.
Why the other options are wrong
- B. This performs a simple column sum and then multiplies by an average, which is not a row-level conversion and would be incorrect if multiple currencies or dates are involved.
- C. USERELATIONSHIP activates a relationship, but doesn't perform a row-level lookup and multiplication for conversion.
- D. LOOKUPVALUE retrieves a single value and is not an iterator; it would need to be used within an iterator like SUMX.
Row-Level Currency Conversion
Row-level currency conversion involves applying the correct exchange rate to each individual transaction amount based on its specific date and currency, ensuring accuracy in aggregated totals.
- Requires iterating through each transaction row.
- Needs a way to lookup the correct exchange rate for each row.
- Exchange rates are typically date-sensitive.
- Essential for accurate reporting in multi-currency environments.
Memory trick: Convert currencies row-by-row, like a global money changer.