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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RustyNails
Helper III
Helper III

DAX to return the corresponding value of a max date

Hi, I have a table that looks like this:

Order IDLink IDOrder DateOrder Status
10150166/17/2019Y
10250164/20/2022N
10350167/23/2018Y
11150145/5/2020N
11250141/4/2021Y

 

Each Link ID can have multiple Order ID's. What I'm trying to do is look up Orders based on the Link ID, and return the Max of Order Date, and that Max Order Date's corresponding Order Status. Like Below (Link Date and Link Status):

 

Order IDLink IDOrder DateOrder StatusLink DateLink Status
10150166/17/2019Y4/20/2022N
10250164/20/2022N4/20/2022N
10350167/23/2018Y4/20/2022N
11150145/5/2020N1/4/2021Y
11250141/4/2021Y1/4/2021

Y

 

I was able to work out the Link Date DAX formula, but I'm unable to fetch it's corresponding Link Status.


Link Date: 

 

 

 

 

 

CALCULATE(
    MAX(Table[Order Date]),
    FILTER((Table), Table[Link ID] = earlier(Table[Link ID]))
)

 

 

 

 

 

 

How do I get the corresponding Order Status for the Link Date?

Any help is appreciated.

Edit: I goofed up the formatting, but here's a clearer picture

 

RustyNails_0-1663603773880.png

 



1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@RustyNails Try:

Link Status =
VAR __LinkID = MAX(Table[Link ID])
VAR __LinkDate = 
CALCULATE(
    MAX(Table[Order Date]),
    FILTER((Table), Table[Link ID] = __LinkID )
)
RETURN
  MAXX(FILTER(ALL(Table),Table[Link ID] = __LinkID && Table[Order Date] = __LinkDate),[Link Status])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@RustyNails Try:

Link Status =
VAR __LinkID = MAX(Table[Link ID])
VAR __LinkDate = 
CALCULATE(
    MAX(Table[Order Date]),
    FILTER((Table), Table[Link ID] = __LinkID )
)
RETURN
  MAXX(FILTER(ALL(Table),Table[Link ID] = __LinkID && Table[Order Date] = __LinkDate),[Link Status])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

You're a genius. Thank you for the quick response!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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