Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |