Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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])
    )

     


     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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])
)