ALL() does not remove filter

[jdusek92]

Hello,
I have the following problem. I do not understand the behavior of ALL() in my specific scenario.
I prepared a sample PBIX that demonstrates it. I want the measure to ignore filters in Potential Rating (and Keep other applied filters)

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

as you can see on the picture below, the measure returns 9 if High or Low is filtered, but returns 4 for Medium.

Why the ALL(Potential Rating) returns 4 when the correct count after removing filter on Potential Rating is 9?

(My aim is to have a dynamic measure that returns the percentage of selected/filtered ratings out of the grand total, so for this I need the count with filter on Potential Rating removed)

Thank you

Jakub

[OwenAuger · Accepted solution]

Hi @jdusek92 

This confusing behaviour is due to auto-exist.

Check out this article:

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

The solution in this case is to create dimension tables for columns you are filtering on: Potential Rating and Job Level.

regards

[OwenAuger]

1. That's a great question, and I'm afraid I can't find a workaround (so far). I'll keep trying though. Someone else may be able to suggest something! Since it all relates to how the DAX query handles filters from columns of the same table, it's not an easy one 😞
2. This is the main issue that I'm aware of in terms of DAX calculations, but there could well be others.

If you connect Excel and generate the same outputs (with an appropriate PivotTable or cube function and slicers), you end up with the result you expected. I guess this is a "benefit" of the MDX queries generated by Excel.

I will have a further play around and see if I can add anything else.

Regards

[jdusek92 · Accepted solution]

Hi, thanks for your reply.

I submitted an idea to change this behavior to be less confusing and more friendly towards non professionals who do not know what a "star schema" is.

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=3f6ad02e-eb2a-ee11-a81c-000d3a0ec312

[OwenAuger · Accepted solution]

Hi @jdusek92 

This confusing behaviour is due to auto-exist.

Check out this article:

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

The solution in this case is to create dimension tables for columns you are filtering on: Potential Rating and Job Level.

regards

[jdusek92]

Hello @OwenAuger 

thanks for the article, it indeed explains the behavior.

I would like to ask some more:

1. Is there some workaround/DAX syntax to make this work with only one flat table? often I create just a quick small reports, so I think for these scenarios it is an unnecessary struggle to create a dimension table for all the filters I might use. Is there some syntax that can make it work on the original data model?
2. Does Auto-Exist cause problems only with ALL and REMOVEFILTERS, or are there some some other DAX functions where I should be aware of this problems with single talbe models?