Forum Discussion

alice11987's avatar
alice11987
Helper I
3 years ago

get value based on max date and match

Cases Table

Case IDValue
1x
2y
3z

 

Events Table

Case IDProcess StepTimeStamp
1Created13:45
1Updated15:50
1Completed16:55
2Created08:00
2Updated09:33
3Created14:00

 

Based on the two tables, I would like to create a column in Case Table with the "Latest Process Step" to get the Process Step of latest Timestamp & Total number of events:

 

Case IDValueLatest Process StepNumber of events
1xCompleted3
2yUpdated2
3zCreated1

 

How should I write the DAX to get the "Latest Process Step" and "Number of events"?

 

Thank you!

Need Help

3 Replies

  • smpa01's avatar
    smpa01
    Community 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
      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