Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alice11987
Helper I
Helper I

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!

3 REPLIES 3
smpa01
Super User
Super User

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

 

smpa01_0-1677729038628.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.