Forum Discussion

jdusek92's avatar
jdusek92
Advocate III
3 years ago
Solved

ALL() does not remove filter

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

 

4 Replies

    • jdusek92's avatar
      jdusek92
      Advocate III
      3 years ago

      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?

       

  • OwenAuger's avatar
    OwenAuger
    Super User
    3 years ago
    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