Allexcept - Remove slicer

[cn4422]

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! 🙂

[sjoerdvn · Accepted solution]

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

[sjoerdvn · Accepted solution]

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

[cn4422]

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?

[sjoerdvn · Accepted solution]

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"
)

[cn4422]

---

@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! 👌

[cn4422]

Thanks, that worked like a charm! 👍

[Rupak_bi]

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]))

[cn4422]

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.

[Rupak_bi]

Plz share sample data

[cn4422]

Thanks for your help, much appreciated!