Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 . Try remove filters
CALCULATE(COUNT(TableX[Column1]), removefilters(TableX[Column2))
refer
@amitchandak & @BA_Pete ,
Somehow none of the offered solutions result in the same number as removing the column filter from the page..
REMOVEFILTERS()/ALL() < desired result < ALLEXCEPT()
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
Proud to be a Super User!
@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 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 @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..
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.
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.
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
Hi @Michieldo ,
Try this measure and see if it does what you need:
yourMeasure =
CALCULATE(
COUNT(TableX[Column1]),
ALL(TableX)
)
Pete
Proud to be a Datanaut!
Hi @BA_Pete ,
Thanks for your suggestion, but I also have multiple other filters in that table that I would like to keep.
Michiel
No problem, try ALLEXCEPT to keep the filters you want:
yourMeasure =
CALCULATE(
COUNT(TableX[Column1]),
ALLEXCEPT(
TableX,
TableX[Column6],
TableX[Column3],
TableX[Column5]
)
)
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |