Forum Discussion
Exclude applied filters with ALL()
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.
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 🙂.
Hi Michieldo
Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
- 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.
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..
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.