Lookup in related table and filter by value

[elliotdixon]

Hi

Hopefully you folks can help out with this as I am stuck.

I have two tables that are linked by a Run_ID

The activity table shows the status changes of runs and I want to know the difference between what is actually happening and what was planned.

I want to know the difference in time between JobRun[RunPickupTime] and Activity[ActivityTime] where Activity[RunStatus]=4

As there may be duplicate rows where the Activity[RunStatus] is the same I need to also limit it to show the time with the lowest Activity[ActivityID]

Table	JobRun
JobRun[Run_ID]	JobRun[RunPickupTime]
258450	2/05/2018 7:40

Table	Activity
Activity[Activity_Run_ID]	Activity[ActivityTime]	Activity[RunStatus]	Activity[ActivityID]
258450	2/05/2018 6:33	1	2278
258450	2/05/2018 10:39	4	2387
258450	2/05/2018 10:40	4	2498
258450	2/05/2018 10:40	5	3123

Cheers in advance.

ED

[Greg_Deckler · Accepted solution]

Try this calculated column out (in JobRun):

Column = 
VAR tmpTable = FILTER(RELATEDTABLE(Activity),[RunStatus]=4)
VAR minActivityID = MINX(tmpTable,[ActivityID])
VAR tmpTable1 = FILTER(tmpTable,[ActivityID]=minActivityID)
RETURN DATEDIFF([RunPickupTime],MAXX(tmpTable1,[ActivityTime]),MINUTE)

[elliotdixon]

This worked great! Thanks so much @Greg_Deckler for the solution. First time I have used VAR as well. Learning time.

Cheers.