Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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
nijos
Frequent Visitor

Calculated column to find lowest date with a filter on status value per ordernumber

โ€Ž02-12-2024 11:59 AM

I have this table with date, status and ordernumber.

 

DateStatusOrderNumber
01/01/2024101000
01/02/2024201000
01/03/2024301000
01/04/2024301000
01/05/2024401000
01/06/2024501000
01/01/2025101100
01/02/2025201100
01/06/2025301100
01/04/2025301100
01/05/2025301100
01/06/2025501100

 

For each OrderNumber, I want to find the first date that is on a row with status 30.

In a calculated column, that row must get a 1, the other rows should get a 0.

 

DateStatusOrderNumberCalculated Column
01/01/20241010000
01/02/20242010000
01/03/20243010001
01/04/20243010000
01/05/20244010000
01/06/20245010000
01/01/20251011000
01/02/20252011000
01/06/20253011000
01/04/20253011001
01/05/20253011000
01/06/20255011000

 

Thank you for your help on this.

Message 1 of 2
317 Views
0
1 ACCEPTED SOLUTION
nijos
Frequent Visitor

โ€Ž02-12-2024 12:29 PM
Calculated Column =
VAR CurrentOrder = 'Table'[OrderNumber]
VAR FilteredTable =
    FILTER ( 'Table', 'Table'[Status] = 30 && 'Table'[OrderNumber] = CurrentOrder )
VAR Result =
    CALCULATE ( MIN ( 'Table'[Date] ), FilteredTable )
RETURN
    IF ( Result = 'Table'[Date], 1, 0 )

View solution in original post

Message 2 of 2
286 Views
0
1 REPLY 1
nijos
Frequent Visitor

โ€Ž02-12-2024 12:29 PM
Calculated Column =
VAR CurrentOrder = 'Table'[OrderNumber]
VAR FilteredTable =
    FILTER ( 'Table', 'Table'[Status] = 30 && 'Table'[OrderNumber] = CurrentOrder )
VAR Result =
    CALCULATE ( MIN ( 'Table'[Date] ), FilteredTable )
RETURN
    IF ( Result = 'Table'[Date], 1, 0 )
Message 2 of 2
287 Views
0

Helpful resources

Announcements
November Fabric Update Carousel

Fabric Monthly Update - November 2025

Check out the November 2025 Fabric update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

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.

View All
Users online (27)