cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 1 Primary 2 Sub 1 01-02-2022 25-01-2022 3 Sub 1 15-03-2022 20-04-2022 4 Primary 5 Sub 4 10-02-2022 17-04-2022 6 Sub 4 31-05-2022 14-06-2022 7 Sub 4 05-03-2022 06-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
Frequent Visitor

Hey Mavie,

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

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])

_MG_

2 REPLIES 2
Frequent Visitor

Hey Mavie,

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

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])

_MG_

Helper I

Thank you, this seems to work as I intended 😊

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors