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.