cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lsteffens
Helper I
Helper I

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

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

 

1 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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

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 

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors