Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.