topic Re: ALL() does not remove filter in DAX Commands and Tips

ALL() does not remove filter

jdusek92 — Sun, 23 Jul 2023 18:48:32 GMT

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

Re: ALL() does not remove filter

OwenAuger — Sun, 23 Jul 2023 21:32:53 GMT

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

Re: ALL() does not remove filter

jdusek92 — Mon, 24 Jul 2023 19:18:06 GMT

Hello OwenAuger

thanks for the article, it indeed explains the behavior.

I would like to ask some more:

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?
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?

Re: ALL() does not remove filter

OwenAuger — Wed, 26 Jul 2023 11:27:30 GMT

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 😞
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

Re: ALL() does not remove filter

jdusek92 — Thu, 27 Jul 2023 19:35:47 GMT

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