Forum Discussion

alice11987's avatar
alice11987
Icon for Helper I rankHelper 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
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago

alice11987  I would let you figure out "Number of Events" measure by yourself. I assumed cases and events are related.

 

For the first one, you can use this

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

 

 

alice11987's avatar
alice11987
Icon for Helper I rankHelper 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
    Icon for Community Champion rankCommunity 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