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
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
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.

Top Solution Authors