Exclude applied filters with ALL()

[Michieldo]

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 page filter, however when I remove the page filters the results of the measure is different from whenever I keep the filter on.

Any explanation why this happens?

Thanks in advance!

[amitchandak]

@Michieldo . Try remove filters

CALCULATE(COUNT(TableX[Column1]), removefilters(TableX[Column2))

refer

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak...

[Michieldo]

@amitchandak & @BA_Pete ,

Somehow none of the offered solutions result in the same number as removing the column filter from the page..

• REMOVEFILTERS() results in the same number as ALL() (as it should right?)
• ALLEXCEPT() results in a higher number

REMOVEFILTERS()/ALL() < desired result < ALLEXCEPT()

[FarhanAhmed]

It seems that you have Customer Filter on Page as well as on Slicer as well.

You may want to remove Page Filter and exclude values from Slicer using Measure filter on Visual Filter by something like this. 

IF(MAX(DateLast[Customer])="",0,1)

then create measure using Allexcept as suggested by @BA_Pete 

[Anonymous]

From what I know, you cannot remove a filter that is page-level. The whole point of a page-level filter is to filter all data in the page in such a way that other data can't be seen. If you have a different need, do not use a page level filter. Instead, create your measures accordingly.

[Michieldo]

@Anonymous Although I agree with your argument, this is not what happens. If I include one of the suggestions ( ALL, REMOVEFILTERS, etc) it does ignore (most) of the applied page filter. 

Of course I can remove the page filter, but that would mean I have to apply the filter on all the visuals on that page with the risk of forgetting that making a new visual.

[Anonymous]

If you think about it carefully, it makes sense that you can't remove a page level filter via DAX. If you could, then results would be highly confusing. Just try to imagine that you filter EVERYTHING in the page by some T[Column] in {"A", "B"} and then suddenly your measure in one of your tables instead of showing 100% for a total, shows.... 250%. Would you want to see this? I don't think so.

[Michieldo]

@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]

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]

Hi @Anonymous & @v-rzhou-msft,

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

[v-rzhou-msft · Accepted solution]

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]

Hi @v-rzhou-msft ,

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.

[v-rzhou-msft]

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

[BA_Pete]

Hi @Michieldo ,

Try this measure and see if it does what you need:

yourMeasure = 
CALCULATE(
    COUNT(TableX[Column1]),
    ALL(TableX)
)

Pete 

[Michieldo]

Hi @BA_Pete ,

Thanks for your suggestion, but I also have multiple other filters in that table that I would like to keep.

Michiel

[BA_Pete]

@Michieldo ,

No problem, try ALLEXCEPT to keep the filters you want:

yourMeasure = 
CALCULATE(
    COUNT(TableX[Column1]),
    ALLEXCEPT(
        TableX,
        TableX[Column6],
        TableX[Column3],
        TableX[Column5]
    )
)

Pete