A data modeler is working on a Power BI model that contains a 'Customers' table, a 'Products' table, and a 'Sales' fact table. The 'Sales' table has millions of rows. The modeler needs to create a measure to calculate the count of distinct customers who purchased a specific product, 'Product Z'. The report is experiencing slow performance when this measure is used. Which DAX expression would be most efficient for this calculation?
- ACOUNTROWS(SUMMARIZE(FILTER(Sales, RELATED(Products[ProductName]) = "Product Z"), Sales[CustomerID]))
- BCOUNTROWS(FILTER(Customers, CALCULATE(COUNTROWS(Sales), RELATED(Products[ProductName]) = "Product Z")))
- CCALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Sales[ProductID] = LOOKUPVALUE(Products[ProductID], Products[ProductName], "Product Z"))
- DCALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Products[ProductName] = "Product Z")
Show answer & explanationAnswer & explanation
Correct answer: D. CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Products[ProductName] = "Product Z")
The most efficient way to achieve this is by using CALCULATE with a direct filter on the related 'Products' table. 'CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Products[ProductName] = "Product Z")' directly applies a filter to the 'Products' table. Due to the one-to-many relationship from 'Products' to 'Sales', this filter propagates to the 'Sales' table, effectively filtering it to only include sales of 'Product Z'. Then, 'DISTINCTCOUNT(Sales[CustomerID])' counts the unique customers within this filtered context. This avoids expensive table iterations or lookups within the measure.
Why the other options are wrong
- A. Using SUMMARIZE and FILTER with RELATEDTABLE is generally less performant than direct filter context modification with CALCULATE, especially on large tables. SUMMARIZE creates a new virtual table, which can be memory-intensive. Also, 'RELATED(Products[ProductName])' is not correct in this context; it should be 'Products[ProductName]' if filtering directly on the table.
- B. This expression is overly complex and inefficient. It tries to filter customers based on related sales, and the 'RELATED' function is typically used in row context, not directly in a filter argument for CALCULATE like this for a measure.
- C. Using LOOKUPVALUE within CALCULATE can be less efficient than a direct filter, especially if 'LOOKUPVALUE' needs to scan a large table repeatedly. The filter should ideally be applied directly.
DAX Filter Propagation
Filter propagation in DAX refers to how filters applied to one table travel through relationships to other tables in the data model. This is a key mechanism for how filter context is established and modified in Power BI.
- Filters propagate along one-to-many relationships from the 'one' side to the 'many' side.
- CALCULATE can modify filter context, including adding new filters that propagate.
- Optimized filter propagation is crucial for measure performance.
- Understanding relationship direction is vital for effective filtering.
Memory trick: Filter once, propagate smart, count distinct with a clear path.