Menu
What is the difference in results of CALCULATE function if we use it with and without FILTER function. Suppose we have those two measures:
Measure1 = CALCULATE([X], 'FactTable'[Color]="Red")
Measure2 = CALCULATE([X], FILTER('FactTable', 'FactTable'[Color]="Red")
Is there a way to modify Measure2 by using other functions, such as ALL, or ALLSELECTED, so that it would return exactly the same results as Measure1?
411
The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context.
The CALCULATE function accepts a table expression returned by the FILTER DAX function, which evaluates its filter expression for each row of the Product table. It achieves the correct result—the sales result for red products. However, it could be achieved much more efficiently by using a Boolean expression.
The FILTER function in DAX is a simple function to use for filtering rows of a table. This function does not change the columns (unless it is used as an input of column manipulation functions such as SELECTCOLUMNS or ADDCOLUMNS). The filter function requires a table input and an expression.
ALL() can only be used to clear filters but not to return a table. Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied.
Not only the results, but also the way of obtaining those results for both measures will not be the same.
I created two measures similar to your example to test this:
TestAvgNoFilter = CALCULATE([PrcAvg]; cal[ReadDate]=DATE(2018;05;23))
TestAvgFilter = CALCULATE([PrcAvg]; filter(cal; cal[ReadDate]=DATE(2018;05;23)))
When I simply throw both of them into pivot table without any additional fields or slicers, ofcourse both of them show the same result:
However:
Using FILTER has significant performance impact, which can be clearly seen looking at query plans and utilization of Storage Engine vs Formula Engine. It creates additional temporary table that it needs to "interact" with already existing filters coming from report/pivot table itself (rows, columns, slicers). You won't notice anything for simple average value in single cell, but if your [x] measure itself is complicated and there are many of those "initial" filters, the difference in calculation time can be huge.
FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. See what happens, when I add ReadDate to the pivot table:
This is precisely why the measure without FILTER is faster: it doesn't care what dates are in columns - it already calculated one "true" value, while the measure with FILTER evaluates itself against initial filters for every row.
Results in both columns can be considered correct - it really all depends on interpretation and how you name the measures ;).
As a general rule I would suggest you don't use FILTER when you don't have to. Save it's power for when it's really needed.
163
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
