Forum Discussion
Surprising difference in performance between DAX measures - why?!
Hi irobba, okay, that makes more sense!
You said "only use the FILTER function if you need it" and "FILTER would be most useful when" ... however, I'd like to think of my "Version 1" and "Version 2" as both using the FILTER function. Right? Because Version 2b includes FILTER, and it is equivalent to Version 2a.
So what is the fundamental difference between my Version 1 and my Version 2b?
I did some more searching and I found this article:
https://exceleratorbi.com.au/the-filter-function-in-dax-part-2/
That's the explanation I needed all along! If I interpret that correctly, the main difference is simply the size of the table over which FILTER is iterating.
If the first argument to FILTER is 'MyTable', then it is forced to iterate over each row of 'MyTable'.
If the first argument to FILTER is ALL('MyTable'[Quantity]), then it still iterates over a table. But it is a much smaller table: it is "all the unique values of 'MyTable'[Quantity], after removing all the filters affecting the visual." So 'MyTable' has millions of rows; but 'MyTable'[Quantity] only has a few thousand unique values.
I read further into MattAllington's article about lineage/virtual tables. It explains how "virtual tables" are created; how virtual tables have a relationship back to the original tables from which they were created; and how filters can propagate from those virtual tables back to the original tables.
So using my Version 2b example:
- The first argument to FILTER is ALL('MyTable'[Quantity]), which returns a table consisting of the unique values of [Quantity] (a few thousand rows). This become s a virtual table.
- This "virtual table can be considered to have a virtual relationship back to the 'MySales'[Quantity] column from where it was born"
- FILTER(ALL('MyTable',[Quantity]), 'MyTable'[Quantity] > 0) then iterates over the virtual table, and filters the virtual table to include only values greater than zero. (Iterating over this virtual table is relatively fast, because the virtual table only has a few thousand rows, as opposed to millions of rows.)
- Because of the "virtual relationship," the filter on the virtual table propagates to 'MyTable', which filters 'MyTable' (similar to any other filter coming from the visual's filter context).
It was also interesting to learn that instead of ALL('MyTable'[Quantity]), you could also use VALUES('MyTable'[Quantity]), if you want the virtual table to retain any filters coming from the visual.
Hi there kevhav,
You've almost answered your own question with the articles you've linked but in general, the simpler the better and only use the FILTER function if you need it - which would typically be because you need to filter your table results outside of the natural context in which you are using your measure.
So as you say, FILTER is creating a new table of results, it's doing this before then applying whatever other filters are passed on to it from the context in which you use the measure so, if you were to use this in a matrix for instance, it would need to create that table for each row (and column potentially) in that matrix.
With version 2a, its much simpler, you are simply adding your additional condition (quantity > 0) to the existing inherited conditions, so just like adding an and clause to the conditions that are already there rather than breaking in to 2 separate stages of filtering.
FILTER would be most useful when your condition needs to compare against a value that would be outside the "normal" context. So in this case where you are counting products, lets say you wanted to filter to only products that cost more than average, taking the average across the whole data set. You would use filter here because you want to filter the products above average first ignoring the current row context, then count the remaining products taking in to account the current row context.
In actual fact with this example, if you tried to write something like the following it would tell you cannot use AVERAGE in this way, and you would need to use the FILTER function to achieve this.
CALCULATE(
DISTINCTCOUNT('MyTable'[ProductId]),
'MyTable'[Price] > AVERAGE('MyTable'[Price])
)
I hope that makes a little sense!
Hi irobba, okay, that makes more sense!
You said "only use the FILTER function if you need it" and "FILTER would be most useful when" ... however, I'd like to think of my "Version 1" and "Version 2" as both using the FILTER function. Right? Because Version 2b includes FILTER, and it is equivalent to Version 2a.
So what is the fundamental difference between my Version 1 and my Version 2b?
I did some more searching and I found this article:
https://exceleratorbi.com.au/the-filter-function-in-dax-part-2/
That's the explanation I needed all along! If I interpret that correctly, the main difference is simply the size of the table over which FILTER is iterating.
If the first argument to FILTER is 'MyTable', then it is forced to iterate over each row of 'MyTable'.
If the first argument to FILTER is ALL('MyTable'[Quantity]), then it still iterates over a table. But it is a much smaller table: it is "all the unique values of 'MyTable'[Quantity], after removing all the filters affecting the visual." So 'MyTable' has millions of rows; but 'MyTable'[Quantity] only has a few thousand unique values.
I read further into MattAllington's article about lineage/virtual tables. It explains how "virtual tables" are created; how virtual tables have a relationship back to the original tables from which they were created; and how filters can propagate from those virtual tables back to the original tables.
So using my Version 2b example:
- The first argument to FILTER is ALL('MyTable'[Quantity]), which returns a table consisting of the unique values of [Quantity] (a few thousand rows). This become s a virtual table.
- This "virtual table can be considered to have a virtual relationship back to the 'MySales'[Quantity] column from where it was born"
- FILTER(ALL('MyTable',[Quantity]), 'MyTable'[Quantity] > 0) then iterates over the virtual table, and filters the virtual table to include only values greater than zero. (Iterating over this virtual table is relatively fast, because the virtual table only has a few thousand rows, as opposed to millions of rows.)
- Because of the "virtual relationship," the filter on the virtual table propagates to 'MyTable', which filters 'MyTable' (similar to any other filter coming from the visual's filter context).
It was also interesting to learn that instead of ALL('MyTable'[Quantity]), you could also use VALUES('MyTable'[Quantity]), if you want the virtual table to retain any filters coming from the visual.