• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on PowerBI visualization and DAX/ query - show utilization by day per job

micgiggs

New Member
hi guys,

I am new to Power BI and I need to come out with a VM/ server utilization dashboard. The problem statement.:- Job table contains the start, end time of a job and the VM/ host name which runs the job. The log table contains the individual transactions of a job. Both tables are connected via common field Key & jobkey. A VM may host more than 1 job and a job might run till the next day.
How do I show in a visualization, the VM utilization per job per day?
For eg, I would like to show job MNO's utilization that runs 2 days on the same VM as job GHI. Utilization should be shown in 2 graphs:
  • jobs utilization percentage on the VM, per day
  • and another graph illustrating jobs utilization (in minutes) on the VM, per day.
The challenge would be showing the utilization per day for jobs that span multiple days.
I have included the Power BI file and the supporting source excel. Would really appreciate any assistance.
excel (https://drive.google.com/file/d/1K7k1lim4UUbGTPCYaepSqJTxcKeTiqeV/view?usp=sharing)
pbix - graph for 17/08 is incorrect, as it should be 1198 minutes, not 1440 min. (https://drive.google.com/file/d/18eRW7HmViAjFj-5Ur1AUb_qNoInEqfez/view?usp=sharing)
Preview (pls. see excel link for details)
job table
Job,start_time,end_time,Message,Key,HostName,is_compliance
ABC,3/18/2021 19:00,3/18/2021 19:02,ABC execution ended,1234567,VM01,FALSE
EDF,18/03/2021 1:20,18/03/2021 1:25,EDF execution ended,30405060,VM02,
GHI,18/03/2021 3:50,18/03/2021 3:55,GHI execution ended,98765432,VM03,
MNO,3/17/2021 4:02,3/18/2021 4:02,MNO execution ended,6666666,VM03,
ABC,18/03/2021 18:00,18/03/2021 18:02,ABC execution ended,2234567,VM01,
ABC,18/03/2021 0:00,18/03/2021 0:02,ABC execution ended,10000007,VM01,
EDF,18/03/2021 3:15,18/03/2021 3:45,EDF execution ended,70809010,VM02,
.............

log table
Job,timestamp,log_message,jobkey
ABC,3/18/2021 19:00,ABC execution started,1234567
ABC,3/18/2021 19:01,Copy excel,1234567
ABC,3/18/2021 19:02,ABC execution ended,1234568
EDF,18/03/2021 1:21,Open connection,30405060
EDF,18/03/2021 1:22,Run processing script,30405060
EDF,18/03/2021 1:24,FTP file,30405061
EDF,18/03/2021 1:25,EDF execution ended,30405060
GHI,18/03/2021 3:50,GHI execution started,98765432
GHI,18/03/2021 3:51,Open connection,98765433
GHI,18/03/2021 3:52,Run processing script,98765433
GHI,18/03/2021 3:54,FTP file,98765433
GHI,18/03/2021 3:55,GHI execution ended,98765433
MNO,3/17/2021 4:02,MNO execution started,6666666
MNO,3/17/2021 4:01,batch1 run,6666666
MNO,3/17/2021 9:01,batch2 run,6666666
MNO,3/17/2021 23:00,batch2 run retry,6666666
MNO,3/18/2021 4:02,MNO execution ended,6666666
...............

Expected output
73988
 
Top