Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Need a help for calculating sum of the job elapsed time, here the data between the jobs are overlapped(means few jobs started in parallel).
Please help.
Here is the sample data.
From JOB_ID : 14 to 20 , the Start time and End Time are slightly overlapped ; and the JOB_ID 17 covers the overall period between(14 to 20)
Batch_Date | JOB_ID | PRCS_START_TIME | PRCS_END_TIME |
2023-01-13 | 1 | 2023-01-13 11:36:34 | 2023-01-13 11:37:18 |
2023-01-13 | 2 | 2023-01-13 11:37:34 | 2023-01-13 11:39:34 |
2023-01-13 | 3 | 2023-01-13 11:39:53 | 2023-01-13 11:44:20 |
2023-01-13 | 4 | 2023-01-13 11:41:03 | 2023-01-13 11:41:34 |
2023-01-13 | 5 | 2023-01-13 11:41:51 | 2023-01-13 11:43:30 |
2023-01-13 | 6 | 2023-01-13 11:44:36 | 2023-01-13 11:45:41 |
2023-01-13 | 7 | 2023-01-13 11:45:57 | 2023-01-13 11:47:25 |
2023-01-13 | 8 | 2023-01-13 11:47:04 | 2023-01-13 11:47:40 |
2023-01-13 | 9 | 2023-01-13 11:47:04 | 2023-01-13 11:47:40 |
2023-01-13 | 10 | 2023-01-13 11:47:04 | 2023-01-13 11:47:41 |
2023-01-13 | 11 | 2023-01-13 11:47:04 | 2023-01-13 11:47:41 |
2023-01-13 | 12 | 2023-01-13 11:47:04 | 2023-01-13 11:47:41 |
2023-01-13 | 13 | 2023-01-13 11:47:05 | 2023-01-13 11:47:42 |
2023-01-13 | 14 | 2023-01-13 11:47:41 | 2023-01-13 11:49:01 |
2023-01-13 | 15 | 2023-01-13 11:47:44 | 2023-01-13 11:48:20 |
2023-01-13 | 16 | 2023-01-13 11:47:58 | 2023-01-13 11:49:30 |
2023-01-13 | 17 | 2023-01-13 11:47:58 | 2023-01-13 11:50:12 |
2023-01-13 | 18 | 2023-01-13 11:48:00 | 2023-01-13 11:49:52 |
2023-01-13 | 19 | 2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
2023-01-13 | 20 | 2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
Excepted output:
Total Elapsed Time for the below group : 2.14 minutes
2023-01-13 11:47:41 | 2023-01-13 11:49:01 |
2023-01-13 11:47:44 | 2023-01-13 11:48:20 |
2023-01-13 11:47:58 | 2023-01-13 11:49:30 |
2023-01-13 11:47:58 | 2023-01-13 11:50:12 |
2023-01-13 11:48:00 | 2023-01-13 11:49:52 |
2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
Solved! Go to Solution.
Hi @Maximus,
You can try to use the following measure formula to get the max duration from current row context:
formula =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
[JOB_ID],
[PRCS_START_TIME],
[PRCS_END_TIME],
"Diff", DATEDIFF ( 'Table'[PRCS_START_TIME], 'Table'[PRCS_END_TIME], SECOND )
)
RETURN
MAXX ( summary, [Diff] )
Regards,
Xiaoxin Sheng
Hi @Maximus,
You can try to use the following measure formula to get the max duration from current row context:
formula =
VAR summary =
SUMMARIZE (
ALLSELECTED ( 'Table' ),
[JOB_ID],
[PRCS_START_TIME],
[PRCS_END_TIME],
"Diff", DATEDIFF ( 'Table'[PRCS_START_TIME], 'Table'[PRCS_END_TIME], SECOND )
)
RETURN
MAXX ( summary, [Diff] )
Regards,
Xiaoxin Sheng
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |