get value based on max date and match

[alice11987]

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
2	Updated	09:33
3	Created	14: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 ID	Value	Latest Process Step	Number of events
1	x	Completed	3
2	y	Updated	2
3	z	Created	1

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

Thank you!

[smpa01]

@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]

Hi, 

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

thank you!

[smpa01]

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