topic Dynamic Use of REMOVEFILTERS in DAX Commands and Tips

Dynamic Use of REMOVEFILTERS

Anonymous — Fri, 04 Apr 2025 13:56:19 GMT

How can I make my variable "SelectedRow" be the input for all of the REMOVEFILTERS below?

Measure =
VAR SelectedRow = SELECTEDVALUE('Add Rows:'[View by: Fields])

VAR ExtSales = ROUND([Ext. Sales],0)

VAR TotalExtSales =CALCULATE( ROUND([TotalExtSales],0),REMOVEFILTERS(PS_TYPE[PS_TYPE]))
// How I want it to work: TotalExtSales =CALCULATE( ROUND([TotalExtSales],0),REMOVEFILTERS(SelectedRow))

VAR ExtSalesPY = ROUND([Ext. Sales PY],0)

VAR TotalExtSalesPY = CALCULATE(ROUND([TotalExtSales PY],0),REMOVEFILTERS(PS_TYPE[PS_TYPE]))
// How I want it to work: TotalExtSalesPY =CALCULATE( ROUND([TotalExtSales PY],0),REMOVEFILTERS(SelectedRow))

VAR ExtGPPY = ROUND([Ext. GP% PY],10)

VAR TotalGMPY = CALCULATE(ROUND([TotalGM PY],10),REMOVEFILTERS(PS_TYPE[PS_TYPE]))
// How I want it to work: TotalGMPY =CALCULATE( ROUND([TotalGM PY]],0),REMOVEFILTERS(SelectedRow))

Var Mix =

(ExtSales - (TotalExtSales * (ExtSalesPY / TotalExtSalesPY))) * (ExtGPPY - TotalGMPY)
Return
Mix

Re: Dynamic Use of REMOVEFILTERS

bhanu_gautam — Fri, 04 Apr 2025 14:10:42 GMT

Anonymous

Assuming SelectedRow is a column reference, you can use the TREATAS function to dynamically apply the filter. Here is how you can modify your measure:

DAX
Measure =
VAR SelectedRow = SELECTEDVALUE('Add Rows:'[View by: Fields])
VAR ExtSales = ROUND([Ext. Sales], 0)
VAR TotalExtSales = CALCULATE(ROUND([TotalExtSales], 0), REMOVEFILTERS(TREATAS({SelectedRow}, 'Add Rows:'[View by: Fields])))
VAR ExtSalesPY = ROUND([Ext. Sales PY], 0)
VAR TotalExtSalesPY = CALCULATE(ROUND([TotalExtSales PY], 0), REMOVEFILTERS(TREATAS({SelectedRow}, 'Add Rows:'[View by: Fields])))
VAR ExtGPPY = ROUND([Ext. GP% PY], 10)
VAR TotalGMPY = CALCULATE(ROUND([TotalGM PY], 10), REMOVEFILTERS(TREATAS({SelectedRow}, 'Add Rows:'[View by: Fields])))
VAR Mix = (ExtSales - (TotalExtSales * (ExtSalesPY / TotalExtSalesPY))) * (ExtGPPY - TotalGMPY)
RETURN
Mix

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Mon, 07 Apr 2025 05:40:47 GMT

Hi Anonymous ,

Thanks for reaching out to the Microsoft Fabric Community. Also thank you bhanu_gautam for your inputs.

In DAX, REMOVEFILTERS() only accepts explicit column references and doesn't support dynamic variables like SelectedRow.

To achieve a similar effect based on user selection from 'Add Rows:'[View by: Fields], you can use:

REMOVEFILTERS(TREATAS({SelectedRow}, 'Add Rows:'[View by: Fields]))

This removes the specific filter context tied to the selected value, not the entire column. It's a practical workaround for dynamic filtering scenarios, though it won’t behave exactly like REMOVEFILTERS(PS_TYPE[PS_TYPE]), which removes all filters on that column.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Fri, 11 Apr 2025 00:53:50 GMT

Hi Anonymous ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Mon, 14 Apr 2025 18:17:43 GMT

this gives me the error "The REMOVEFILTERS function expects a table reference for argument '1', but a table expression was used." It's related to using TREATAS, I believe.

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Mon, 14 Apr 2025 18:19:01 GMT

Unfortunatley that did not work:

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Tue, 15 Apr 2025 10:06:43 GMT

Hi Anonymous ,

To clarify from a DAX capability standpoint REMOVEFILTERS() does not support dynamic column references via variables. It requires explicit column references at design time. Therefore, using a variable like SelectedRow to dynamically pass in a column name (e.g., 'PS_TYPE'[PS_TYPE]) will not work as intended in the context of REMOVEFILTERS().

The use of TREATAS() in this case can help simulate certain dynamic behaviors by altering the filter context based on values, but it does not remove filter context from a dynamically referenced column. This is a limitation within DAX, which does not support indirect column referencing or evaluation from a string input.

If your goal is to conditionally remove filters on different columns based on user selection (e.g., from a slicer), we recommend using a SWITCH() construct to explicitly define the logic for each possible selection. While this is more manual, it is currently the most reliable pattern within the DAX engine to simulate dynamic column operations.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Sun, 20 Apr 2025 00:03:54 GMT

Hi @fergu513 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Dynamic Use of REMOVEFILTERS

Anonymous — Wed, 23 Apr 2025 01:52:39 GMT

Hi Anonymous ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.