Forum Discussion

cn4422's avatar
cn4422
Helper V
1 year 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, 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's avatar
    sjoerdvn
    1 year 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
    1 year 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"
)

     

9 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    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's avatar
      cn4422
      Helper V
      1 year ago

      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.
       

       

  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    1 year ago
    Count APF Status_Site_AT Filter =
CALCULATE(COUNT('lead'[Status 2]),
  ALL(),
  'lead'[Country construction site]="Ă–sterreich",
  VALUES(Datum[Date]))
    • cn4422's avatar
      cn4422
      Helper V
      1 year ago

      Thanks, that worked like a charm! đź‘Ť

    • cn4422's avatar
      cn4422
      Helper V
      1 year ago

      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's avatar
        sjoerdvn
        Solution Sage
        1 year 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"
)