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

[Anonymous]

hi guys,

I have this 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 (https://drive.google.com/file/d/18eRW7HmViAjFj-5Ur1AUb_qNoInEqfez/view?usp=sharing)

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,,3/18/2021 1:30,EDF execution ended,89101112,,TRUE,,,,
GHI,,3/18/2021 3:59,GHI execution ended,10111213,,,,,,
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,,,,,
JKL,,3/18/2021 4:38,JKL execution ended,5555555,,,,,,
MNO,3/17/2021 4:02,3/18/2021 4:02,MNO execution ended,6666666,VM03,,,,,

.............

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

[Anonymous · Accepted solution]

Hi guys,

Found the solution. Basically use Dax (item 1) then summarize in another table. The visualization will then read from the new table

1)  DAX to get the duration between job start time and end time (duration is derived for each day, for job that overlaps midnight)

MinuteNo Revised =

DATEDIFF(

MAX('Log'[SD],'Log'[DateOnly]),

MIN('Log'[ED], 'Log'[DateOnly]+1),

MINUTE

)

2) Total Util =
SUMMARIZECOLUMNS(
Logs[ProcessName],
Logs[JobKey],
Logs[Logs-timestamp DateOnly],
Logs[HostName],
Logs[SD],
Logs[ED],
Logs[MinuteNo Revised],
"Minutes Util", AVERAGE(Logs[MinuteNo Revised])
)

 

[V-lianl-msft]

See if it will help:

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374255 

[Anonymous]

Hi @V-lianl-msft ,

 tx for the link but it doesn't really help, as I require utilization % per day, per job on each VM, like my expected output.  I need to show job MNO's utilization spans 2 days across VM3, sharing VM with job GHI