Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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!
@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] ) )
)
)
Hi,
Thank you for your prompt help! May I know why do we need "ALLEXCEPT" function here?
thank you!
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 |