Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is building a Power BI model for a manufacturing company. The model contains a 'Production' fact table with daily production quantities and a 'Products' dimension table. The 'Products' table has a 'ProductCategory' column. The modeler needs to calculate the 'Total Production Quantity' for products belonging to the 'Electronics' category. However, the 'Production' table does not have a direct 'ProductCategory' column, only 'ProductID'. Which DAX function is most appropriate to achieve this calculation while ensuring optimal performance?
- ASUMX(FILTER(Products, Products[ProductCategory] = "Electronics"), CALCULATE(SUM(Production[Quantity])))
- BCALCULATE(SUM(Production[Quantity]), FILTER(Products, Products[ProductCategory] = "Electronics"))
- CCALCULATE(SUM(Production[Quantity]), Products[ProductCategory] = "Electronics")
- DSUMMARIZECOLUMNS(Products[ProductCategory], "Total Quantity", SUM(Production[Quantity]))
Show answer & explanationAnswer & explanation
Correct answer: C. CALCULATE(SUM(Production[Quantity]), Products[ProductCategory] = "Electronics")
The CALCULATE function, when provided with a column from a related table, automatically performs context transition and applies the filter to the related table, propagating it to the fact table. This is the most direct and performant way to filter a fact table based on a dimension table's column.
Why the other options are wrong
- A. SUMX is an iterator and is generally less performant for simple aggregations across a filtered dimension table when CALCULATE can achieve the same result more efficiently through context transition.
- B. Using FILTER explicitly within CALCULATE on a dimension table is less efficient than directly providing the filter argument, as CALCULATE can implicitly handle the context transition.
- D. SUMMARIZECOLUMNS is used for creating summary tables and is not suitable for defining a single measure that filters a fact table based on a dimension attribute.
CALCULATE with Related Table Filters
CALCULATE can directly accept a filter expression on a related dimension table's column. It implicitly handles context transition and filter propagation to the fact table, making it an efficient way to filter measures.
- Leverages existing model relationships.
- More performant than explicit FILTER for simple cases.
- Automatically applies filter context to the fact table.
Memory trick: CALCULATE sees relationships; it doesn't need a map.