Forum Discussion
REMOVEFILTERS() Unexpected behaviour
I am trying to use the DAX function REMOVEFILTERS() to remove the filter context of an external filter from a field that has been applied on the filter pane. The function works great until I filter by another field that comes from the same table. REMOVEFILTERS() does not work.
Example:
Table = Fruit
| FruitName | Tasty |
| Apple | No |
| Bananna | Yes |
| Pear | No |
Measure:
Yes, that's correct. The filter from Fruit[Tasty] is only fully ignored by the measure if no other filters from the same table (Fruit) are applied.
When filters from other columns like FruitName are present, DAX keeps those filters, and they restrict the row context. So even though Tasty is unfiltered,
the remaining filters can still result in no matching rows, leading to a blank output.
6 Replies
- BhavinVyas3003
Super User
CountFruit = CALCULATE(COUNTROWS(Fruit), REMOVEFILTERS(Fruit[Tasty]))
removes filters only from the Tasty column, not from other columns like FruitName. So when both FruitName = "Bananna" and Tasty = "No" are selected,
the Tasty filter is ignored, but the FruitName filter still limits the data to Bananna — and since Bananna is only marked as "Yes", no row matches,
so the result is blank. This is expected DAX behavior when filters come from the same table.Thanks Bhavin,
So the filter from Fruit[Tasty] is only ignored if no other filters are being applied from fields from the same table?- BhavinVyas3003
Yes, that's correct. The filter from Fruit[Tasty] is only fully ignored by the measure if no other filters from the same table (Fruit) are applied.
When filters from other columns like FruitName are present, DAX keeps those filters, and they restrict the row context. So even though Tasty is unfiltered,
the remaining filters can still result in no matching rows, leading to a blank output.
Thanks Bhavin,
So the filter from Fruit[Tasty] is only ignored by the measure if no other filters from other fields on the same table are applied?
Try the following formula if you want to exclude all the filters from Fruit table.
CountFruit = CALCULATE(COUNTROWS(Fruit), ALL(Fruit))
ALL(Fruit) removes all filters on the Fruit table.- Royel
Hi Marcus079570
REMOVEFILTERS() is working exactly as it’s designed to. The key thing to remember is that it only removes filters from the column (or table) you explicitly specify. In DAX, filters are applied at the row level, so filtering one column indirectly filters all the other columns in the same table because they share the same rows.
Let’s break it down with your example:
CountFruit = CALCULATE( COUNTROWS(Fruit),REMOVEFILTERS(Fruit[Tasty]))and it returns total 3 records which is fine.
When, Filtering Tasty = No
CountFruit No Testy = CALCULATE( COUNTROWS(Fruit), Fruit[Tasty] = "No" )Result:
FruitName
Tasty
Apple
No
Pear
No
When Fruit[FruitName] = "Bananna" and Fruit[Tasty] = "No"
CountFruit Both = CALCULATE( COUNTROWS(Fruit), Fruit[FruitName] = "Bananna" && Fruit[Tasty] = "No" )Returned blank because there is no matched records.
when we use REMOVEFILTERS(Fruit[Tasty]) can remove the Tasty filter, but FruitName = Bananna is still applied and ites returning the matched records 1.
CountFruit_Removed_Tasty = CALCULATE( COUNTROWS(Fruit), Fruit[FruitName] = "Bananna", -- keeps only Bananna rows REMOVEFILTERS(Fruit[Tasty]) -- removes any Tasty filter )So, It returns 1 records because REMOVEFILTERS() is only skipping Testy columns and we have extra one filter Fruit[FruitName] = "Bananna" which one is applicable and its returned its matched records. Eventually, you can achieve this without Appling REMOVEFILTERS()
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
