Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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] ) )
)
)
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
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 |
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!