ALLEXCEPT with external filters

[aashritsingh]

We are trying to get an ALLEXCEPT measure working also accounting for external filters.

In this example scenario, we want to remove ALL filters in the table EXCEPT the Year (Including all columns from DATE table such as Month etc).

ALLEXCEPT works fine when no external filters are applied.  However, it ignores any filters appliedin the slicer.

We also tried all selected with this code:

But, it doesn't work and gives Amounts across the years.

To summarize, we want the visual to only keep DATE table filters and ignore every table filter while keeping external slicer filters. Our issue with ALLSELECTED is that it requires its Table Name to ignore filters. However, our model has around 18 different dimensions besides DATE_DIM which users can randomly choose from and it gets difficult to specify each table in ALLSELECTED.

Please suggest a best practice to accomplish this.

[Daniel29195]

@aashritsingh 

allselected if the only function that returns the external slicer filters and get rid of the visual filters .

i m afraid there is no other way in DAX  (that i know of ) to be able to maintain the slicer filters while removing the visual filters .

if you have a starschema, maybe try to use allselected(fact_table) , 

let me know if it works for you . 

best regards

[aashritsingh]

Hello @Daniel29195 

I did try using ALLSELECT(Fact Table). Although, it maintain external filters, but, removes all the context filters from the table. We just want to keep the filters on DATE_DIM. Is there a way to maintain that or, add the DATE_DIM filter after ALLSELECT processing?

[Daniel29195 · Accepted solution]

@aashritsingh 

allselected(fact_table) ,  values(dim_date[date])

let me know if it works for you . 

[aashritsingh]

@Daniel29195 

This solution has worked. Thank you for the help 🙂