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
daxer
Solution Sage
Solution Sage

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors