Pull a Max Date from Related Fields

[quincy_p]

Hello - I am trying to create a service deadline field - from my data we get a 'Parent' Work Order which has numerous associated Child Work Orders - these child Work Orders have a Deadline field already that is associated to their product, the Parent ones do not have this as their is technically no product on the WO, just the Child related to it.

So I need a column query that will find the max PM_Deadline from the CHild WOs and match it to the parent. To do so I have the following fields in the table 'Work Order ALL'

[Id] - every WO has a unique ID

[PM_Deadline] - only present on Child WO

[Work_Order_Quote] - only present on Child WO but this value provides the ID of the Parent WO.

So you can see in the tables below that in Tab 1, Id matches Work_Order_Quote__c in Table 2. Is there anyway to use this field in order to pull the date PM_deadline on to the Id in the top table (ideally the max date as the dates will not always be the same). 

Tabe 1:

Id	Parent_WO	PM_Deadline	QParent_WO	Work_Order_Quote__c
a4B3q0000047FOaEAM	WO-05247934		WO-05247934

Tabe 2: 

Id	Name	Parent_WO	Work_Order_Quote__c	PM_Deadline
a4B3q0000047FOfEAM	WO-05247935	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOgEAM	WO-05247936	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOhEAM	WO-05247937	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOiEAM	WO-05247938	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOjEAM	WO-05247939	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOkEAM	WO-05247940	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOlEAM	WO-05247941	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOmEAM	WO-05247942	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00
a4B3q0000047FOnEAM	WO-05247943	WO-05247934	a4B3q0000047FOaEAM	8/31/2022 0:00

[DataInsights]

@quincy_p,

Here are two calculated columns you can add to Table1.

The first one uses FILTER and does not require a relationship between the tables.

PM_Deadline FILTER = 
VAR vId = Table1[Id]
VAR vResult =
    MAXX (
        FILTER ( Table2, Table2[Work_Order_Quote__c] = vId ),
        Table2[PM_Deadline]
    )
RETURN
    vResult

The second one uses RELATEDTABLE and requires a relationship between the tables. Use this one if possible due to better performance since it utilizes the relationship.

PM_Deadline RELATEDTABLE = 
MAXX ( RELATEDTABLE ( Table2 ), Table2[PM_Deadline] )

[quincy_p]

Hi DataInsights - thanks for the quick reply! And sorry for the confusion but everything is in the same table called 'Work Order ALL' - I was trying to use the 2 tables of data to visualize the issue I was having.

That being said, I was thinking that it just may be easier to do a separate table anyways so I will have a crack at it and come back if it works.