Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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)
This worked great! Thanks so much @Greg_Deckler for the solution. First time I have used VAR as well. Learning time.
Cheers.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |