allexept not excluding filters

[bsheffer]

have a table with list of accounts, date_opened, and a calculated field _bin days open to active

I set a relative filter on date_opened for the past 12 calendar months

I created two measures

max_date =
calculate(
max('Dim View_MID_Properties'[DATE_OPENED]),
ALLEXCEPT(
'Dim View_MID_Properties',
'Dim View_MID_Properties'[DATE_OPENED]
)
)

min_date =
calculate(
min('Dim View_MID_Properties'[DATE_OPENED]),
ALLEXCEPT(
'Dim View_MID_Properties',
'Dim View_MID_Properties'[DATE_OPENED]
)
)

when I put these two measures in a table I get

8/1/2019  and 7/31/2020

but if I pull _bin days open to active into a page or visual filter and click on one of the bins the max date changes. 

8/5/2019 to 6/18/2020 or

8/1/2019 to 7/6/2020

Should it not be ignoring this filter?

I want my measures to ignore any page or visual filter except for the one on date_opened

[v-stephen-msft]

Hi @bsheffer ,

You should create your measures with ALL function instead of ALLEXCEPT function. The ALL function can ignore any filters that might have been applied, while the ALLEXCEPT function removes all context filters in the table except filters that have been applied to the specified columns. In this scenario, you don't need to keep the filters of DATE_OPENED column.

My sample data is this.

accounts	DATE_OPENED	_bin days open to active
aa	8/1/2019	100
bb	12/1/2019	60
cc	3/17/2020	20
dd	5/3/2020	30
ee	7/31/2020	20
ff	4/20/2020	5
gg	4/21/2020	10
hh	11/11/2019	50
ii	10/20/2019	60
jj	9/1/2019	55

In your measures which you use the ALLEXCEPT function, your max date and min date are gotten in the table after the date is filtered.

So, you can create your measures as follows.

min_date1 = 
CALCULATE (
    MIN ( 'Dim View_MID_Properties'[DATE_OPENED] ),
    ALL ( 'Dim View_MID_Properties' )
)

max_date1 = 
CALCULATE (
    MAX ( 'Dim View_MID_Properties'[DATE_OPENED] ),
    ALL ( 'Dim View_MID_Properties' )
)

Then if you click on one of the bins, the max or min date will not change. The result is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bsheffer]

Thanks Stephen, but I need the date filter.  I'm counting the number of accounts in a time period and if I use the all() function the date range minimum changes to 1966.  That was why I used allexcept(), which isn't functioning as advertised.  How do I preserve a date range for my calculations and not be affected by other filters that may have been set?

So ultimately I want to get a percentage of accounts using a set of bins for a date range.  Because I'm using a published dataset I cannot create any other tables or calculated fields.

[v-stephen-msft · Accepted solution]

Hi @bsheffer ,

You can refer to this post, he has a similar situation with you, and there is an article in the post explaining why your measure cannot ignore filters. You could create a new table that contains _bin days. But you said that your data source is a data set. As far as I know, I have no other solution. I suggest you contact the owner of the dataset and ask him to modify the model and create a new table containing _bin days as follows.

Download my PBI file to check.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bsheffer]

Thanks for following up.

Your reply indicates that the issue is the lack of a snowflake schema in my table which is creating an auto-exist situation that is ignoring my all(column) and allexcept(table, column) code in my dax.  Please confirm.  

[bsheffer]

I have resolved the issue.

I could not use allexcept because my table was not in a snowflake schema.  However using all(column) to remove each filter that was applied worked.  I had to use if(isfiltered(column) to test for the presense of filters to I could remove the right filters depending on which filters were set on the page or visual.

Thanks for everyone's help.

[Greg_Deckler]

@bsheffer - If I recall correctly, certain filters like page level filters pre-filter the data before it ever gets to DAX. Meaning, that you cannot ignore them because the data they are filtering doesn't even get to your DAX calculation.