Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A data modeler is designing a Power BI data model for an online retail company. The company wants to analyze customer lifetime value (CLTV). The 'Sales' table contains 'CustomerID', 'OrderDate', and 'OrderTotal'. The 'Customers' table contains 'CustomerID' and 'CustomerJoinDate'. You need to calculate the total sales for each customer up to their last purchase date. Which DAX function is most appropriate for iterating through sales rows for each customer to sum their order totals?
- ASUMX
- BDISTINCTCOUNT
- CSUM
- DCALCULATE
Show answer & explanationAnswer & explanation
Correct answer: A. SUMX
SUMX is an iterator function that evaluates an expression for each row of a table and then sums the results, making it ideal for row-by-row calculations like CLTV.
Why the other options are wrong
- B. DISTINCTCOUNT counts unique values, it is not used for summing numeric expressions.
- C. SUM aggregates a column directly, it does not iterate row by row with an expression.
- D. CALCULATE modifies filter context, it does not iterate row by row for aggregation.
SUMX Function
SUMX is a DAX iterator function that evaluates an expression for each row of a table and then sums the resulting values.
- Iterates row by row over a specified table.
- Evaluates an expression for each row.
- Aggregates the results of the expression.
- Used for complex aggregations that require row-context evaluation.
Memory trick: Iterators 'X' marks the spot for row-by-row calculations.