Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is building a Power BI model for a financial institution. The model includes a 'Transactions' table with 'TransactionID', 'AccountID', 'TransactionDate', and 'Amount'. The modeler needs to calculate the cumulative sum of 'Amount' over time for each 'AccountID'. Which DAX pattern is appropriate for creating a running total that respects account-level context?
- ACALCULATE(SUM('Transactions'[Amount]), FILTER(ALLSELECTED('Transactions'), 'Transactions'[TransactionDate] <= MAX('Transactions'[TransactionDate])))
- BCALCULATE(SUM('Transactions'[Amount]), DATESBETWEEN('Date'[Date], BLANK(), MAX('Date'[Date])))
- CCALCULATE(SUM('Transactions'[Amount]), FILTER(ALL('Transactions'), 'Transactions'[TransactionDate] <= MAX('Transactions'[TransactionDate]) && 'Transactions'[AccountID] = MAX('Transactions'[AccountID])))
- DSUMX(FILTER('Transactions', 'Transactions'[TransactionDate] <= EARLIER('Transactions'[TransactionDate]) && 'Transactions'[AccountID] = EARLIER('Transactions'[AccountID])), 'Transactions'[Amount])
Show answer & explanationAnswer & explanation
Correct answer: A. CALCULATE(SUM('Transactions'[Amount]), FILTER(ALLSELECTED('Transactions'), 'Transactions'[TransactionDate] <= MAX('Transactions'[TransactionDate])))
This pattern calculates a running total within the current filter context for 'AccountID'. ALLSELECTED removes filters only from the 'Transactions' table but respects filters from other tables (like 'AccountID' from a visual), and the date filter ensures accumulation up to the current date.
Why the other options are wrong
- B. This calculates a running total over a date table, but it doesn't inherently respect the 'AccountID' context from the 'Transactions' table or a visual.
- C. ALL('Transactions') removes all filters from the 'Transactions' table, including 'AccountID' filters, which would prevent the running total from being specific to an account.
- D. Using EARLIER with SUMX for running totals can be complex and less performant in large models, and this specific formula might not respect the visual's filter context for 'AccountID' correctly.
Cumulative Sum (Running Total)
A cumulative sum, or running total, is a measure that aggregates values sequentially over a specified dimension, typically time, accumulating the total as new data points are added.
- Aggregates values from the beginning of a period up to the current point.
- Often requires modifying filter context to include past periods.
- Can be challenging to implement while respecting other dimensions (e.g., customer, product).
- ALLSELECTED is useful for running totals that should respect external filters but ignore internal table filters.
Memory trick: Running totals are like a marathon: keep adding to the distance covered.