In this post, I will show some samples on different ways to use filtering in DAX. If you write ineffective filters you will most likely always run into performance issues. We will also see the difference between using SUMMARIZE() and ROW() in queries that only return one row.
Let’s pretend one of your customers wants to know the sum of sales for the product “AWC Logo Cap”. And the minimum sales.
The benchmarking is done by running the different queries in DAX Studio multiple times. I have cleared the cache between the executions.
Sample query 1.
DEFINE MEASURE 'FactInternetSales'[MyFilteredNumber] = CALCULATE ( SUM ( FactInternetSales[UnitPrice] ), FILTER ( DimProduct, [EnglishProductName] = "AWC Logo Cap" ) ) MEASURE 'FactInternetSales'[MyFilteredNumberMin] = CALCULATE ( MIN ( FactInternetSales[UnitPrice] ), FILTER ( DimProduct, [EnglishProductName] = "AWC Logo Cap" ) ) EVALUATE ( FILTER ( SUMMARIZE ( FactInternetSales, DimProduct[EnglishProductName], "Filtered result", 'FactInternetSales'[MyFilteredNumber], "Filtered result min", 'FactInternetSales'[MyFilteredNumberMin] ), 'FactInternetSales'[MyFilteredNumber] > 0 ) )
This query produces the correct answer. But is does not perform very well.
It took 32 MS.