Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data analyst is creating a Power BI report to track product sales. The model includes a 'Sales' table and a 'Products' table. The 'Products' table has a 'ProductCategory' column. The analyst needs to create a measure that calculates the sales amount for a specific product category, say 'Electronics', and always displays this value regardless of any filters applied to the 'Products' table in the report. Which DAX expression achieves this?
- ACALCULATE(SUM(Sales[SalesAmount]), ALL(Products), Products[ProductCategory] = "Electronics")
- BCALCULATE(SUM(Sales[SalesAmount]), KEEPFILTERS(Products[ProductCategory] = "Electronics"))
- CCALCULATE(SUM(Sales[SalesAmount]), Products[ProductCategory] = "Electronics")
- DSUMX(FILTER(Products, Products[ProductCategory] = "Electronics"), RELATED(Sales[SalesAmount]))
Show answer & explanationAnswer & explanation
Correct answer: A. CALCULATE(SUM(Sales[SalesAmount]), ALL(Products), Products[ProductCategory] = "Electronics")
To calculate sales for a specific category while ignoring all other filters on the 'Products' table, you must use ALL(Products) to remove existing filters from the entire table, and then apply the specific filter for 'Electronics'.
Why the other options are wrong
- B. KEEPFILTERS would preserve existing filters on 'Products' while adding the 'Electronics' filter, which is the opposite of the requirement to ignore other filters.
- C. This only applies the 'Electronics' filter but does not remove any other existing filters on the 'Products' table, so it would still be affected by other slicers or filters.
- D. SUMX with FILTER and RELATED is a row-context calculation that would sum sales for 'Electronics' but would still be affected by external filters on the 'Products' table unless explicitly removed.
CALCULATE with ALL for Filter Override
Using the CALCULATE function with ALL() to remove existing filters from a table or column, allowing a new, specific filter to be applied without interference from the original filter context.
- CALCULATE modifies filter context.
- ALL() removes filters from a table or column.
- Allows creating measures that are independent of external filters for specific dimensions.
Memory trick: All filters out, then specific filter in.