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

[lsteffens]

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

[Anonymous]

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

[stevedep]

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 

[lsteffens · Accepted solution]

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

[Subashini_03]

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

[stevedep]

In your pseudo code you are using removefilters?

[stevedep]

Removefilters(

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