Log In
Register
Home
Active Member
March 13, 2021
Offline
Hi guys,
I am new to Power BI and I hope to do a support dashboard on whether a robot job ended according to the scheduled time slot.
Scheduled Job Timings:
Job|start_time|end_time
ABC|12:00 am|1:00 am
EDF|1:00 am|2:00 am
GHI|2:00 am|3:00 am
.......
compliance table:
Job|end_time|Is_Compliance
ABC|20-Feb-17 07:02 pm|N
EDF|5/10/2016 1:30:00 AM|Y
GHI|12-Dec-16 03:59 am|
JKL|10-Feb-17 04:38 am|
MNO|06-Feb-17 10:00 am|
PQR|07-Nov-16 03:25 am|
STU|20/1/2017 6:45|
VW|08-Dec-16 02:52 pm|
XYZ|16-Dec-16 04:19 pm|
how do I populate the Is_Compliance value? Attached is the powerbi file with the data above. Would really appreciate some guidance. tx.
July 16, 2010
Offline
Hi Michael,
Welcome to our forum! There are some assumptions missing from the information provided. The Job Compliance table has dates and time in one column and the Scheduled Job Time table only contains times. In the attached file I've assumed it doesn't matter if the job start date is more than 1 day prior.
You'll see in Power Query I've split the time out from the Date/Time end_time and then merged it with the Scheduled Job Timings table. This is an Excel file for simplicity, but the steps are the same in Power BI.
Mynda
Active Member
March 13, 2021
Offline
Hey Mynda,
Thanks for the invite and making me feel welcome. You are right! The date plays an important role in the end_time column. I should have name end_time as job_time. As there are several entries in the job_compliance table for the same job, my intention was to get the latest job "ended" record for the current date and check compliancy. For eg, when I am running the report for today (14 Mar), job ABC has 2 "jobs" in the job_compliancy table. I only want the latest "ended" job (end_time=14/3/2021 7:02:00 PM, key=1234567). Then check the compliancy. The jobs MNO, PQR, and STU will not be counted / displayed in today's dashboard.
I have attached the modified job_compliancy excel for your reference. I am learning on the job and am picking up PowerBI fundamentals 🙂
July 16, 2010
Offline
Hi Michael,
Please see example file attached.
Note: I used this technique to Number Grouped Data in Power Query. And I added Table.Buffer to the Filtered Rows1 step to prevent Power Query losing the sort order.
Hope that helps.
Mynda
Active Member
March 13, 2021
Offline
hi Mynda,
It really helps. Just found out about another scenario though. Some of the jobs are schedule to also run on other parts of the day, shown in red as follows,
How do we cater to such scenario? tx.
| Job | start_time | end_time |
| ABC | 12:00 AM | 1:00 AM |
| EDF | 1:00 AM | 2:00 AM |
| GHI | 2:00 AM | 3:00 AM |
| JKL | 3:00 AM | 4:00 AM |
| MNO | 4:00 AM | 5:00 AM |
| PQR | 5:00 AM | 6:00 AM |
| STU | 6:00 AM | 7:00 AM |
| VW | 7:00 AM | 8:00 AM |
| XYZ | 8:00 AM | 9:00 AM |
| ABC | 9:00 AM | 10:00 AM |
| JKL | 10:00 AM | 11:00 AM |
| PQR | 11:00 AM | 12:00 PM |
July 16, 2010
Offline
I don't know. You need to tell me what the rules/logic would be.
Also, the examples above don't correlate to any of the 'execution ended' times in your original data, so right now they would simply be ignored. Perhaps you can provide a sample file that contains all scenarios and the desired result. Please just provide one file with one sheet containing the two tables and example of your desired result so I can see it all in one place and don't have to keep recreating a file from what you send.
Thanks,
Mynda
1 Guest(s)
