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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.