cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Lookup in related table and filter by value

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Lookup in related table and filter by value

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Responsive Resident
Responsive Resident

Re: Lookup in related table and filter by value

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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors