Simple ALL function not working

[juan_pablo]

Hi,

I really don't get why this simple measure is not working on this simple one table model:

The measure should ignore the filter on commercialInvoiceNo nevertheless when I apply a filter on commercialInvoiceNo on the first table, the result is 45 and as the table below shows (which is the same table but without the commercialInvoiceNo filter aplied) the result shoul be 155 which is the value when no filters are applied to commercialInvoiceNo.

This is the link to the Power BI file. 

https://drive.google.com/file/d/1ZmoILX6B9VCT0ciJHG28AKA4mljsLmSH/view?usp=sharing

[JustJan]

Hi @JarroVGIT ,

Great find, thanks. This question had me puzzled too.  

@juan_pablo  I tried to translate it to your PBIX,  and as far as I can see it the only way the get the desired result is when you create a new table with all distinct commercial invoice numbers, link it to the data table (single direction) and use the new table in the visual filter.  

Jan

[JarroVGIT · Accepted solution]

Hi @juan_pablo ,

Well, the main reason I am active in this community is because I learn from every answer I give 🙂 This question is no exception, I really had to look into the behaviour of this feature in PBI. 

As it turns out (and I didn't know this), you cannot escape filters that are set on the Filter pane. Apparently, there is this mechanic called Auto Exist. Here is an article that explains in detail how the result gets to what it is:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/ 

Hope this helps you out, let me know if you have any more questions 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[juan_pablo]

Hi @JarroVGIT 

Sorry for the late reply, it took me several hours to understand this application of AutoExist.

DAX is full of traps, unintuitive and undesirable results but this definitely got to the top of the unintuitive and undesirable behaviors list. I think the developers have to figure out how to control this application of AutoExist in this scenario.

Thank you very much for the article, you place me on the right direction to understand the problem and figure out the solution. The article explains exactly what is happening and its reasons.

The only observation I have is that the filters on the filter pane aren’t part of the problem. I moved the filter to a slicer and the behavior was the same.

The solution I figure out was to make a second unnecessary table (as @JustJan suggested) and apply the filter on this new table instead of the original table.

This AutoExist behavior is not listed in the Definitive Guide to DAX book and nowhere else except for the article provided. In order to figure out if AutoExist would kick in and spoil your measure is to know if certain combination of values from the filtered variables exist or not in your data (which is almost impossible to know this).

I tried to state this behavior as a “general rule” in a sentence in order to generalize the situation and make it easier to identify when it would happen, I hope it helps other users identify when they would get in trouble because of AutoExist feature:

“When there are values from the variable A which would be removed with the ALL function that never combine with values from the second filtered variable B (which is located in same table) but those values from B do combine with values not filtered from A: AutoExist will give unexpected results.”