topic Re: Get earliest and latest date based off related IDs in the same table in DAX Commands and Tips

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

MAVIE — Wed, 11 May 2022 20:18:29 GMT

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

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

_MG_ — Thu, 12 May 2022 06:41:15 GMT

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

I hope this will help you 🙂

_MG_

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

MAVIE — Thu, 12 May 2022 08:58:15 GMT

Thank you, this seems to work as I intended 😊