Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is building a Power BI model for a global company. They have a 'Sales' table and a 'Products' table. The 'Products' table contains 'ProductID', 'ProductName', and 'Category'. The 'Sales' table contains 'ProductID', 'SaleAmount', and 'Region'. The analyst needs to create a measure that calculates the total sales for 'Electronics' category products, regardless of any other filters applied to the 'Products' table. Which DAX expression achieves this?
- ACALCULATE(SUM('Sales'[SaleAmount]), ALL('Products'), 'Products'[Category] = "Electronics")
- BSUMX('Sales', 'Sales'[SaleAmount] * ('Products'[Category] = "Electronics"))
- CCALCULATE(SUM('Sales'[SaleAmount]), KEEPFILTERS('Products'[Category] = "Electronics"))
- DCALCULATE(SUM('Sales'[SaleAmount]), 'Products'[Category] = "Electronics")
Show answer & explanationAnswer & explanation
Correct answer: A. CALCULATE(SUM('Sales'[SaleAmount]), ALL('Products'), 'Products'[Category] = "Electronics")
This expression uses ALL('Products') to remove any existing filters on the entire 'Products' table, then applies a new filter 'Products'[Category] = "Electronics", ensuring the calculation is only for Electronics regardless of other product filters.
Why the other options are wrong
- B. SUMX iterates, but the boolean multiplication ('Products'[Category] = "Electronics") would result in 0 or 1, not effectively filtering the rows for summing in the desired way, and it doesn't remove existing filters.
- C. KEEPFILTERS preserves existing filters, which is the opposite of the requirement to ignore other filters.
- D. This applies the filter but does not remove existing filters on the 'Products' table, so it would still be affected by other product filters.
Filter Modification with ALL
The ALL function in DAX is used within CALCULATE to remove all existing filters from a table or selected columns, allowing new filter contexts to be applied independently.
- Removes all filters from a table or column(s).
- Used to override existing filter context.
- Crucial for 'ignoring' slicers or other filters.
- Often combined with CALCULATE to apply new filters after removing old ones.
Memory trick: Control filters like traffic lights: ALL stops them, then a new one green-lights.