CALCULATE with REMOVEFILTERS still affected by slicer

[jsangerman]

I feel like I'm taking crazy pills. REMOVEFILTERS in a CALCULATE should remove the filters. Then can someone please explain why my data is changing when I change the value in a filter that is in REMOVEFILTERS?

It is interesting me that I get one of 3 values, depending on the value selected in the slicer: one for blank, one for dates up to 1/2024, and one for dates after 1/2024. However, if I put them in a table, I get the expected result.

So, why is the slicer affecting the measure differently than the table filter context does?

[Poojara_D12 · Accepted solution]

Hello  @jsangerman 

Your situation is indeed puzzling but can be clarified by understanding how REMOVEFILTERS behaves differently depending on the filtering context, particularly with slicers and tables.

Key Points to Consider:

1. REMOVEFILTERS Behavior: When you use REMOVEFILTERS in CALCULATE, it’s supposed to remove the filter on the specified column or table within the formula's context, effectively ignoring any filters applied to that column. However, if the filter in question is applied via a slicer, things get slightly more complex.

2. Context from Slicers vs. Table Filters: A slicer affects the report-level filter context in Power BI, which is processed slightly differently than a row-level filter applied directly within a table. Slicers can still impact the outer context of CALCULATE, meaning even if REMOVEFILTERS removes the filter on that column, other filters (like cross-filtering from related tables or model-specific filtering behavior) can sometimes still affect the measure.

3. Unexpected Results with Time-Based Slicers: Date-based filtering often triggers additional context interactions. If you’ve set a slicer on a date column and it’s impacting the measure even with REMOVEFILTERS, it might be due to the way REMOVEFILTERS interacts with the entire model. Removing filters from a column doesn’t entirely reset other filters or cross-filtering from related tables, especially if there are relationships with single or bidirectional filter propagation involved.

   Why You're Seeing Different Results

   When you adjust the slicer for different date ranges, Power BI might be creating new filters at the model level that affect related tables or even implicit groupings within the visual’s filtering context. When you apply REMOVEFILTERS on a single column, it only removes direct filters on that column, not necessarily all derived filters affecting the measure.

   Why the Table Filter Context Works as Expected

   When you view the measure in a table, it evaluates within each row’s context individually. Here, Power BI calculates each row’s context separately, adhering more closely to the REMOVEFILTERS directive. In contrast, the slicer’s impact at the report level introduces broader filtering interactions across the model.

   Suggested Workaround

   To get consistent behavior with REMOVEFILTERS, especially when slicers are involved, you can try wrapping your measure in an ALL or ALLSELECTED function to fully clear the context at a higher level:

   DAX: MeasureWithoutSlicerImpact = CALCULATE( [Your Measure], REMOVEFILTERS('YourTable'[YourColumn]), ALL('YourTable') -- or ALLSELECTED('YourTable') depending on your needs )

   :- Using ALL clears all filters on YourTable, which includes slicer impacts, giving you a more predictable outcome. If you need to consider filters other than the one removed with REMOVEFILTERS, try ALLSELECTED, which keeps other filters active but still prevents slicer overreach.

   This approach should provide a more stable and predictable result, independent of slicer values.

   Please let me know if this helps you.

   Regards,

   Poojara.

[Ankur04]

Hi @jsangerman ,

can you try creating below measure. if I understand your requirement clearly then this should help you.

Allocation (ignore archive date)= CALCULATE(SUM('your table'[Allocation]),ALL('your table'[archive date]))

Let me know if you need any other support.

Thanks,

[jsangerman]

As a CALCULATE modifier, ALL works identically to REMOVEFILTERS. So, this isn't the answer.

[Poojara_D12 · Accepted solution]

Hello  @jsangerman 

Your situation is indeed puzzling but can be clarified by understanding how REMOVEFILTERS behaves differently depending on the filtering context, particularly with slicers and tables.

Key Points to Consider:

1. REMOVEFILTERS Behavior: When you use REMOVEFILTERS in CALCULATE, it’s supposed to remove the filter on the specified column or table within the formula's context, effectively ignoring any filters applied to that column. However, if the filter in question is applied via a slicer, things get slightly more complex.

2. Context from Slicers vs. Table Filters: A slicer affects the report-level filter context in Power BI, which is processed slightly differently than a row-level filter applied directly within a table. Slicers can still impact the outer context of CALCULATE, meaning even if REMOVEFILTERS removes the filter on that column, other filters (like cross-filtering from related tables or model-specific filtering behavior) can sometimes still affect the measure.

3. Unexpected Results with Time-Based Slicers: Date-based filtering often triggers additional context interactions. If you’ve set a slicer on a date column and it’s impacting the measure even with REMOVEFILTERS, it might be due to the way REMOVEFILTERS interacts with the entire model. Removing filters from a column doesn’t entirely reset other filters or cross-filtering from related tables, especially if there are relationships with single or bidirectional filter propagation involved.

   Why You're Seeing Different Results

   When you adjust the slicer for different date ranges, Power BI might be creating new filters at the model level that affect related tables or even implicit groupings within the visual’s filtering context. When you apply REMOVEFILTERS on a single column, it only removes direct filters on that column, not necessarily all derived filters affecting the measure.

   Why the Table Filter Context Works as Expected

   When you view the measure in a table, it evaluates within each row’s context individually. Here, Power BI calculates each row’s context separately, adhering more closely to the REMOVEFILTERS directive. In contrast, the slicer’s impact at the report level introduces broader filtering interactions across the model.

   Suggested Workaround

   To get consistent behavior with REMOVEFILTERS, especially when slicers are involved, you can try wrapping your measure in an ALL or ALLSELECTED function to fully clear the context at a higher level:

   DAX: MeasureWithoutSlicerImpact = CALCULATE( [Your Measure], REMOVEFILTERS('YourTable'[YourColumn]), ALL('YourTable') -- or ALLSELECTED('YourTable') depending on your needs )

   :- Using ALL clears all filters on YourTable, which includes slicer impacts, giving you a more predictable outcome. If you need to consider filters other than the one removed with REMOVEFILTERS, try ALLSELECTED, which keeps other filters active but still prevents slicer overreach.

   This approach should provide a more stable and predictable result, independent of slicer values.

   Please let me know if this helps you.

   Regards,

   Poojara.

[jsangerman · Accepted solution]

Poojara, this is very interesting. I haven't heard of this, but I think I follow. So you know, I tried adding ALL( 'Resource Allocations' ) as you suggested, and of course I get consistent and expected results. The problem is that I have several other filters on that table on a given page. I want to maintain without having to write them into the measure, so ALL doesn't really work. I tried ALLSELECTED, but got the same result as without it.

But, what you were writing sounded related to a new feature I read about in the October 2024 update: value filter behavior. Turns out that if you change the behavior from Automatic (which currently uses Coalesced) to Independent, I get the expected result without using any ALL modifiers. In other words, REMOVEFILTERS just works!

Thank you for the help. You led me in the right direction.

[v-xuxinyi-msft]

Hi @jsangerman 

It sounds like you have solved your problem. Thanks for sharing! Could you please mark the reply that helped you and your method as the solution? This will help more users who are facing the same or similar difficulties. Thank you!

Best Regards,
Yulia Xu

[FreemanZ]

hi @jsangerman ,

Are the archive date always from the same table?

Do you have other filter contexts filtering other columns other than archive date?