Forum Discussion

alice11987's avatar
alice11987
Helper I
3 years ago

get value based on max date and match

Cases Table Case ID Value 1 x 2 y 3 z   Events Table Case ID Process Step TimeStamp 1 Created 13:45 1 Updated 15:50 1 Completed 16:55 2 Created 08:00 ...
Need Help
alice11987's avatar
alice11987
Helper I
3 years ago

Hi, 

 

Thank you for your prompt help! May I know why do we need "ALLEXCEPT" function here?

 

thank you!

smpa01's avatar
smpa01
Community Champion
3 years ago

This does this

FILTER (
        events,
        events[TimeStamp]
            = CALCULATE ( MAX ( events[TimeStamp] ), ALLEXCEPT ( events, events[Case ID] ) )
    )

 

Case ID Process Step TimeStamp filterExpressionInternallyReturns-ALLEXCPT retains the filter on CASEID while doing this
1 Created 13:45 16:55
1 Updated 15:50 16:55
1 Completed 16:55 16:55
2 Created 8:00 9:33
2 Updated 9:33 9:33
3 Created 14:00 14:00