topic Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others? in DAX Commands and Tips

Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

lsteffens — Fri, 26 Feb 2021 13:02:18 GMT

Hi.

I have this "simple" measure that calculates a sum of the column "Value" filtered by the slices values I give (FilterColumn1-N) plus a last filter in the measure DAX code (Data[Year] = Data[Data_MaxYear] - 1).

(preudo code to descibe my question ...)

Measure1 = CALCULATE(SUM(Data[Value]), FILTER(ALLEXCEPT(Data, Data[FilterColumn1], Data[FilterColumn2], ... , Data[FilterColumnN] ), Data[Year] = Data[Data_MaxYear] - 1))

This works, but is not ideal as I have to describe ALL the filter columns I will accept (FilterColumn1-N). And there are many "external" slicers and visuals that filter many columns in my PBI report.

What I really need is a similar DAX function like ALLEXCEPT that just removes ONE filter column, but keep all the others.

It seems like a logical DAX function, but I could not find it.

Can anybody help me here?

Thanks,

Lars

Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

stevedep — Fri, 26 Feb 2021 14:13:06 GMT

Removefilters(

https://docs.microsoft.com/nl-nl/dax/removefilters-function-dax

Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

Anonymous — Fri, 26 Feb 2021 14:45:56 GMT

ALL used as a CALCULATE modifier does exactly that. And by the way, ALL in this role is totally equivalent to REMOVEFILTERS.

Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

stevedep — Fri, 26 Feb 2021 16:42:06 GMT

removefilters allows to remove the filter on one specific column. I have the impression that the question is:

"removes ONE filter column, but keep all the others."

Although that is not what ALLExcept does, allexcept removes all filters except one:

"A table with all filters removed except for the filters on the specified columns." - https://docs.microsoft.com/nl-nl/dax/allexcept-function-dax

Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

lsteffens — Tue, 02 Mar 2021 13:32:41 GMT

Thanks to both of you for your suggestions.

It took me another four hours to get to the solution, as REMOVEFILTERS or ALL where not usable in my DAX context.

But I found the solution for me was to use this DAX measure (pseudo code):
Measure1 = CALCULATE([Value], CALCULATETABLE(ALLSELECTED(Data), REMOVEFILTERS(Data[FilterColumnToRemove])))

This is much simpler than what I have use before and works with external filters and filters in the context of the visual in use.

Thanks,
Lars

Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

stevedep — Tue, 02 Mar 2021 15:17:24 GMT

In your pseudo code you are using removefilters?

Re: Is there a DAX function similar to ALLEXCEPT that removes just a single filter and keeps the others?

Anonymous — Mon, 21 Aug 2023 11:28:36 GMT

Hi Lars, Thank you for this solution you saved my life.