Forum Discussion

cn4422's avatar
cn4422
Icon for Helper V rankHelper V
2 years ago
Solved

Allexcept - Remove slicer

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, b...
  • sjoerdvn's avatar
    sjoerdvn
    2 years ago
    Count APF Status_Site_AT Filter =
CALCULATE(COUNT('lead'[Status 2]),
  ALL(),
  'lead'[Country construction site]="Österreich",
  VALUES(Datum[Date]))
  • sjoerdvn's avatar
    sjoerdvn
    2 years ago

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

     

sjoerdvn's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
2 years ago

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's avatar
cn4422
Icon for Helper V rankHelper V
2 years ago
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! 👌