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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MAVIE
Helper I
Helper I

Get earliest and latest date based off related IDs in the same table

Hi All,


I need to create measures to get the earliest and latest dates based off of a varying number of related rows.

My data look like the following:

ID   Type          RelatedID  StartDate   EndDate     
1Primary   
2Sub101-02-2022   25-01-2022  
3Sub115-03-202220-04-2022
4Primary   
5Sub410-02-202217-04-2022
6Sub431-05-202214-06-2022
7Sub405-03-202206-04-2022

There is a Primary type and a Sub-type, where only the Sub-type has a defined date range.

I then need to create a date range for the Primary type, based off the earliest StartDate and latest EndDate from the Sub-type with RelatedID. It is also possible for a Primary type to have no Sub-type, therefore it is possible that no date range can be created for some Primary types.

I have tried various methods but have run out of idea for how to solve this. 
It is also possible that two calculated columns would be a better solution. 

Any help would be greatly appreciated

1 ACCEPTED SOLUTION
_MG_
Frequent Visitor

Hey Mavie,

You could do that in a way using the implicit row context in calculated columns.
Result:

_MG__0-1652337610192.png

StartDatePrimary =
VAR CurrentID = Table1[ID]
VAR SubTable = Filter(Table1, AND(Table1[Type] = "Sub", Table1[RelatedID] = CurrentID))
return
MINX(SubTable, Table1[StartDate])
 
EndDatePrimary =
VAR CurrentID = Table1[ID]
VAR SubTable = Filter(Table1, AND(Table1[Type] = "Sub", Table1[RelatedID] = CurrentID))
return
MAXX(SubTable, Table1[EndDate])
 
StartDateFinal = IF(ISBLANK(Table1[StartDate]), Table1[StartDatePrimary], Table1[StartDate])
 
EndDateFinal = IF(ISBLANK(Table1[EndDate]), Table1[EndDatePrimary], Table1[EndDate])
 
I hope this will help you 🙂 

_MG_
 





View solution in original post

2 REPLIES 2
_MG_
Frequent Visitor

Hey Mavie,

You could do that in a way using the implicit row context in calculated columns.
Result:

_MG__0-1652337610192.png

StartDatePrimary =
VAR CurrentID = Table1[ID]
VAR SubTable = Filter(Table1, AND(Table1[Type] = "Sub", Table1[RelatedID] = CurrentID))
return
MINX(SubTable, Table1[StartDate])
 
EndDatePrimary =
VAR CurrentID = Table1[ID]
VAR SubTable = Filter(Table1, AND(Table1[Type] = "Sub", Table1[RelatedID] = CurrentID))
return
MAXX(SubTable, Table1[EndDate])
 
StartDateFinal = IF(ISBLANK(Table1[StartDate]), Table1[StartDatePrimary], Table1[StartDate])
 
EndDateFinal = IF(ISBLANK(Table1[EndDate]), Table1[EndDatePrimary], Table1[EndDate])
 
I hope this will help you 🙂 

_MG_
 





Thank you, this seems to work as I intended 😊

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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