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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
sagar_r01
Microsoft Employee
Microsoft Employee

Flag based on 2 columns (Status and Max Date within group)

I am trying to get a calculated column as "Flag" which should populate "Yes" or "No" based on below conditions

 

1. Status = "Failed"

2. The date should be the latest within the Name group

 

Below is the sample table and expected output of the Flag Outcome

for example - the first row for XYZ satisfies the above condition and should be YES.

 

NameStatusDateFlag
ABCCompleted4/29/2020 4:02No
ABCCompleted4/28/2020 4:02No
ABCFailed4/16/2020 5:02No
ABCFailed4/15/2020 5:02No
ABCCompleted4/14/2020 5:04No
ABCCompleted4/13/2020 5:02No
XYZFailed4/26/2020 11:03Yes
XYZFailed4/25/2020 11:03No
XYZFailed4/24/2020 11:03No
XYZFailed4/23/2020 11:03No
XYZCompleted4/22/2020 11:03No
XYZCompleted4/21/2020 11:03No
XYZCompleted4/20/2020 11:03No
XYZFailed4/19/2020 11:03No
XYZCompleted4/18/2020 11:03No
PQRFailed10/24/2019 11:32Yes
PQRFailed10/24/2019 11:32No
PQRFailed10/24/2019 11:32No
LMNFailed3/6/2020 11:03No
LMNFailed3/5/2020 11:03No
LMNFailed3/4/2020 11:03No
LMNFailed3/3/2020 11:03No
LMNCompleted3/2/2020 11:03No
LMNCompleted3/2/2020 10:36No
DEFFailed4/28/2020 15:12Yes
DEFFailed4/28/2020 15:12No
DEFFailed4/28/2020 15:12No
DEFFailed4/28/2020 15:12No
DEFCompleted4/27/2020 13:32No
DEFCompleted4/16/2020 12:22No
DEFCompleted3/18/2020 19:23No
DEFCompleted2/24/2020 10:26No

 

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

Perhaps something along the lines of:

 

Flag = 
  VAR __MaxDate = MAXX(FILTER('Table',[Name] = EARLIER('Table'[Name])),[Date])
RETURN
  IF([Status] = "Failed" && [Date] = __MaxDate,"Yes","No")


Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thank You! This was great!

Glad it worked for you! 🙂


Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.