Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A data analyst is working with a Power BI model that includes a 'Sales' table and a 'Products' table. The 'Sales' table contains 'ProductID' and 'OrderDate' columns, while the 'Products' table contains 'ProductID' and 'ProductName' columns. There is a one-to-many relationship from 'Products[ProductID]' to 'Sales[ProductID]'. The analyst needs to create a measure that calculates the total sales amount for a specific product, 'Laptop A', across all sales. Which of the following DAX expressions should the analyst use?
- ASUMX(FILTER(Sales, RELATED(Products[ProductName]) = "Laptop A"), Sales[Amount])
- BCALCULATE(SUM(Sales[Amount]), ALL(Products[ProductName] = "Laptop A"))
- CCALCULATE(SUM(Sales[Amount]), Products[ProductName] = "Laptop A")
- DCALCULATE(SUM(Sales[Amount]), TREATAS({"Laptop A"}, Products[ProductName]))
Show answer & explanationAnswer & explanation
Correct answer: C. CALCULATE(SUM(Sales[Amount]), Products[ProductName] = "Laptop A")
The CALCULATE function is used to change the filter context. By specifying 'Products[ProductName] = "Laptop A"' as a filter argument, it modifies the context to only include sales related to 'Laptop A', and then sums the 'Sales[Amount]'.
Why the other options are wrong
- A. SUMX with FILTER and RELATED can achieve this but is more complex than necessary for a direct filter context modification with CALCULATE.
- B. ALL removes all filters from the specified column, which would prevent filtering by 'Laptop A'.
- D. TREATAS is used to apply a table expression as filters to columns, often for virtual relationships or when dealing with disconnected tables, which is not the most direct approach here.
CALCULATE Function
The CALCULATE function evaluates an expression in a context modified by new filters. It is one of the most powerful and frequently used functions in DAX.
- Changes filter context for an expression.
- First argument is an expression (e.g., SUM, AVERAGE).
- Subsequent arguments are filters or context modifiers.
- Filters applied directly to related tables will propagate.
Memory trick: CALCULATE is the filter controller, always changing the view.