Forum Discussion

Michieldo's avatar
Michieldo
Icon for Helper III rankHelper III
6 years ago
Solved

Exclude applied filters with ALL()

Hi,   I have a certain page filter on, let's say TableX[Column2 = "" ].    Now I make the measure: CALCULATE(COUNT(TableX[Column1]), ALL(TableX[Column2)). I hoped this measure would ignore the pa...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Michieldo 

    Is there only Column2 in your Filters on this page? I think other columns in this table or other related table may impact the result.

    I build a sample table to have a test.

    Table1:

     

    Measure1 = CALCULATE(COUNT('Table1'[User]), ALL('Table1'[Quantity]))

     

    The result without filter:

    Add Quantity into Filters ,show items when the Quantity value is less than or equal to 100.

     

    Unless adding Quantity column, add User column in to filter as well.

    Change the measure:

     

    Measure2 = CALCULATE(COUNT('Table1'[User]), ALL('Table1'[Quantity],Table1[User]))

     

    All() funtion will remove the specified column's filters, so we need to add all columns in filters into All() functions. And the All(Table) is the a way to remove all filters in this table and return to a complete table. Or the result will be impacted by the filter. And if you add other related table's columns into the filter, they will impact the result of the measure as well.

    More info about ALL(): ALL

    ALL Function in Power BI – How To Use It With DAX 

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Michieldo's avatar
Michieldo
Icon for Helper III rankHelper III
6 years ago

Anonymous  That's right if you assume that the viewers of the page know all the filters that are applied in Desktop and that you want to combine the measures in one visual. I compare it more like the 'select all' option in the filters. If you have many different options in a column, but you only want to deselect one, I personally will not choose to select all the options one by one, but prefer to check the 'select all' and then deselect the one. This is my case here, I have many measures/visuals for which the filter is necessary, but one for which it's not.

 

But this question arose because of the doubts about the functionality of the option ALL(). I think that our discussion is not contributing in regard to that matter ðŸ™‚.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
If you put a filter on a page, then all, I mean ALL, data is filtered by this filter. If this filter affects your slicers, then you'll not see all options in your slicer but only those that are allowed by the page-level filter. And because of that your DAX will not see anything more than what your tables have been filtered to. Not even ALL() will help and for a good reason. If you have a strange need to overwrite a page-level filter in DAX... then you're a bit out of luck and need to invent a different solution. Your viewers will know the filters because they can hover over a visual and it will show them all filters applied to it. The (Select All) option is not unconditional. It's relative to other filters, among them to the page-level filter.
  • Michieldo's avatar
    Michieldo
    Icon for Helper III rankHelper III
    6 years ago

    Hi Anonymous & Anonymous,

     

    Again thanks for the explanation. I understand now that I should not misuse the page level filter. I simply removed the page level filter and applied it to all the visuals within the report. 

     

    However, I still don't understand why if the ALL() function is not supposed to work on page filters, about 80% of the page level filter is ignored due to applying the ALL() function. This made it very misleading for me (and possibly others) since I thought it worked in the first place..

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Michieldo 

    Is there only Column2 in your Filters on this page? I think other columns in this table or other related table may impact the result.

    I build a sample table to have a test.

    Table1:

     

    Measure1 = CALCULATE(COUNT('Table1'[User]), ALL('Table1'[Quantity]))

     

    The result without filter:

    Add Quantity into Filters ,show items when the Quantity value is less than or equal to 100.

     

    Unless adding Quantity column, add User column in to filter as well.

    Change the measure:

     

    Measure2 = CALCULATE(COUNT('Table1'[User]), ALL('Table1'[Quantity],Table1[User]))

     

    All() funtion will remove the specified column's filters, so we need to add all columns in filters into All() functions. And the All(Table) is the a way to remove all filters in this table and return to a complete table. Or the result will be impacted by the filter. And if you add other related table's columns into the filter, they will impact the result of the measure as well.

    More info about ALL(): ALL

    ALL Function in Power BI – How To Use It With DAX 

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • Michieldo's avatar
    Michieldo
    Icon for Helper III rankHelper III
    6 years ago

    Hi Anonymous ,

     

    Thanks for your extensive answer. Unfortunately removing the specific page filter will in any of your cases affect the number in my report.

     

    Since this is probably due to my complex data model and your answer will work for most cases, I will accept your answer as the solution.