Forum Discussion
Simple ALL function not working
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! 🙂
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! 🙂
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 Anonymous 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.”