cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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!






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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors