Every now and then we need to create calculation with filtered values. Like on the picture bellow.
As you can see, we can use KEEPFILTERS or FILTER function. What is the difference? Performance of course.
As an example we use Iowa Liquor Sales dataset. Contains 21 milion records.
When we use KEEPFILTERS…
…we are getting following statistics and plan.
Once you use FILTER function…
As you can see, here is a difference of performance for the benefit of KEEPFILTERS function.
KEEPFILTERS is 2 times faster than FILTER, Total time 15ms in comparison to 30ms, FILTER uses two queries as opposed to KEEPFILTERS which uses only one query.
Why? The answer is pretty simple. KEEPFILTERS keeps existing context and compares context filters with filters from the function. In other words, result of the query is combination of filtered context values and values from the function’s filter.
FILTER overwrites the context. If filtered columns are both in the context and filter function then filter from context is replaced by filter from function.