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' fact table and a 'Currencies' dimension table. The 'Sales' table has a 'SaleAmount' in local currency and a 'CurrencyKey' column. The 'Currencies' table has 'CurrencyKey', 'CurrencyCode', and 'ExchangeRate' columns. The modeler needs to display 'SaleAmount' converted to USD using the 'ExchangeRate' from the 'Currencies' table. Which DAX pattern is most suitable for this row-level currency conversion?
- ACALCULATE (SUM (Sales[SaleAmount] * Currencies[ExchangeRate]))
- BSUMX (Sales, Sales[SaleAmount] * RELATED (Currencies[ExchangeRate]))
- CSUM (Sales[SaleAmount] * RELATED (Currencies[ExchangeRate]))
- DSUMX (Currencies, SUM (Sales[SaleAmount]) * Currencies[ExchangeRate])
Show answer & explanationAnswer & explanation
Correct answer: B. SUMX (Sales, Sales[SaleAmount] * RELATED (Currencies[ExchangeRate]))
To perform a row-level calculation (multiplying each sale amount by its corresponding exchange rate) and then sum the results, the SUMX function is required. Inside the SUMX, RELATED is used to retrieve the 'ExchangeRate' from the 'Currencies' dimension table for each row of the 'Sales' fact table, respecting the existing relationship.
Why the other options are wrong
- A. CALCULATE changes context but doesn't enable row-level multiplication and summation in this manner; it still needs an iterator.
- C. SUM cannot perform row-level multiplication with RELATED directly; it requires an iterator like SUMX.
- D. This iterates over 'Currencies' and attempts to sum 'SaleAmount' in a way that would incorrectly aggregate sales before applying the exchange rate, leading to incorrect results.
Row-Level Currency Conversion
The process of converting monetary values from one currency to another for each individual transaction row, typically using an exchange rate from a related dimension table.
- Requires an iterator function like SUMX.
- Utilizes RELATED or LOOKUPVALUE to retrieve dimension attributes.
- Ensures accurate aggregation after individual row conversion.
Memory trick: Convert currencies row by row, then sum.