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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors