ALLEXCEPT not working properly

[DmitryKo]

I have a calendar table named CT and a sample dataset named Tbl:

Date	Key	Value
04.08.2022	A	3
07.08.2022	A	7
05.08.2022	B	11
07.08.2022	B	13

CT is linked to Tbl through Date. A measure is defined to return the date when given key had a value in the fact table:

LastTimeHadValue = MAXX(
    FILTER(
        ALLEXCEPT(Tbl, Tbl[Key]),
        Tbl[Date] <= MAX(CT[Date])
    ),
    Tbl[Date]
)

According to the documentation, ALLEXCEPT() should remove all context filters on Tbl, except for the context filters applied to the Tbl[Key] column.

I expect this:

Date	Key	LastTimeHadValue
05.08.2022	A	04.08.2022
05.08.2022	B	05.08.2022
06.08.2022	A	04.08.2022
06.08.2022	B	05.08.2022

But instead get this (some incorrect values are marked with red)

It just returns last date from the fact table (Tbl) when any key had a value, not when key corresponding to the current row had a value - thus ignoring the context filter on Tbl[Key] which it's supposed to keep.

Can anyone explain why?

[ValtteriN]

Hi,

Here ALLEXCEPT is used within FILTER and MAXX. So the issue is with their interaction. Here is a simpler way to do this: 

Last_time_had_value =

var _cdate =MAX('Calendar'[Date]) return

CALCULATE(MAX('Table (7)'[Date]),ALL('Calendar'),'Table (7)'[Date]<_cdate)

Since DAX keeps filter context with the key the only filter we need to remove is the time context (thats why we are using ALL).


End result:

I am using calendar[date] in the column and my data structure is as follows:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[DmitryKo]

"Here ALLEXCEPT is used within FILTER and MAXX. So the issue is with their interaction"

So what exactly is the issue here? Why isn't it working as intended?
Also in your code, why do we need to include ALL(CalendarTable) into the filter condition? Not quite getting this part. Is it because otherwise we'd have only one date from calendar as a part of our filter context and we need to get rid of this in order to get proper value?