topic Allexcept - Remove slicer in DAX Commands and Tips

Allexcept - Remove slicer

cn4422 — Tue, 27 Aug 2024 13:23:27 GMT

Hi,

I have a date slicer (Datum,Datum[Date]) and a country slicer.

I have the following DAX Measure, for which I want to remove all filters, except the date slicer.

I tried with ALLEXCEPT, but somehow it doens't remove the country slicer:

Count APF Status_Site_AT Filter =

CALCULATE(COUNT('lead'[Status 2]),

'lead'[Country construction site]="Österreich",

ALLEXCEPT(Datum,Datum[Date]))

Any ideas on that?

I'm thankful for help! 🙂

Re: Allexcept - Remove slicer

Rupak_bi — Tue, 27 Aug 2024 13:49:42 GMT

Hi ,

Try This

Count APF Status_Site_AT Filter =

CALCULATE(COUNT('lead'[Status 2]),

'lead'[Country construction site]="Österreich",

FILTER(ALL(Datum),Datum[Date]=selectedvalue(Datum,[Date]))

Re: Allexcept - Remove slicer

cn4422 — Tue, 27 Aug 2024 14:09:26 GMT

Thanks for your reply!

I had to change the last line of the code slightly, because of an error message:

FILTER(ALL(Datum),Datum[Date]=selectedvalue(Datum[Date])))

Now the error message is gone, but unfortunately the value = empty.

Re: Allexcept - Remove slicer

Rupak_bi — Tue, 27 Aug 2024 15:02:55 GMT

Plz share sample data

Re: Allexcept - Remove slicer

sjoerdvn — Tue, 27 Aug 2024 15:48:24 GMT

Count APF Status_Site_AT Filter = CALCULATE(COUNT('lead'[Status 2]), ALL(), 'lead'[Country construction site]="Österreich", VALUES(Datum[Date]))

Re: Allexcept - Remove slicer

cn4422 — Wed, 28 Aug 2024 12:07:56 GMT

Thanks, that worked like a charm! 👍

Re: Allexcept - Remove slicer

cn4422 — Wed, 28 Aug 2024 12:08:46 GMT

Thanks for your help, much appreciated!

Re: Allexcept - Remove slicer

cn4422 — Wed, 28 Aug 2024 12:28:23 GMT

I just found out that it works almost perfectly... 🙂

One thing I noticed is that it puts the grand total into all cells and not for every category the correct sum.

Any idea on how to fix that?

Re: Allexcept - Remove slicer

sjoerdvn — Wed, 28 Aug 2024 16:21:50 GMT

Well, the question was to remove all filters, and there is no real difference between row/column context and filter context.
There are two ways of fixing this
1) expand the current measure. The current measure uses ALL() to remove all filters, and then VALUES(Datum[Date]) to re-apply the dates in the active context. You can simply add that column in there (example below, adjust if the value is not from that table, but from a dimension table).
2) Alternatively, do not use ALL() but expand the context only for the slicers/filters you want overriden.

Count APF Status_Site_AT Filter = CALCULATE(COUNT('lead'[Status 2]), ALL(), 'lead'[Country construction site]="Österreich", VALUES(Datum[Date]), VALUES('lead'[Status 2]) ) Count APF Status_Site_AT Filter V2= CALCULATE(COUNT('lead'[Status 2]), ALL('lead'[Country construction site]), ALL('tableA'[column x]), 'lead'[Country construction site]="Österreich" )

Re: Allexcept - Remove slicer

cn4422 — Thu, 29 Aug 2024 07:29:20 GMT

sjoerdvn wrote:
Well, the question was to remove all filters, and there is no real difference between row/column context and filter context.

You're right of course - sorry, I'm still new to all this and so the "remove all filters" wasn't quite correctly formulated.

Thanks for clarifying the issue and providing two solutions... I've tried the first one and now it's working just fine! 👌