Forum Discussion

DmitryKo's avatar
DmitryKo
Icon for Helper IV rankHelper IV
4 years ago

ALLEXCEPT not working properly

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

DateKeyValue
04.08.2022A3
07.08.2022A7
05.08.2022B11
07.08.2022B13


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:

DateKeyLastTimeHadValue
05.08.2022A04.08.2022
05.08.2022B05.08.2022
06.08.2022A04.08.2022
06.08.2022B05.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?

DAX

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      DmitryKo
      Icon for Helper IV rankHelper IV
      4 years ago

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