cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elliotdixon
Responsive Resident
Responsive Resident

Lookup in related table and filter by value

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]

 

TableJobRun
JobRun[Run_ID]JobRun[RunPickupTime]
2584502/05/2018 7:40

 

TableActivity  
Activity[Activity_Run_ID]Activity[ActivityTime]Activity[RunStatus]Activity[ActivityID]
2584502/05/2018 6:3312278
2584502/05/2018 10:3942387
2584502/05/2018 10:4042498
2584502/05/2018 10:4053123

 

Cheers in advance.

ED

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

 

Cheers.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors