CPU consumption chart in DAX (events in progress)

[Anonymous]

Hi guys,

While I'm not an expert in DAX (rather tyro) I have the following issue.

I need to build a report based on xls file, where I need to show the CPU cores utylizaiton - generated by the jobs (running in parallel), on the moment in time.

In the calculation I need to include those jobs which started before my check but still running (consuming) now and those which just started and will run for some time in the future - but active in the particular second.

In other words this is probably quite common KPI of CPU consumption, but I was not able to find or develope a DAX formula which may provide a full solution.

In some article of Gerhard there is a similar issue described but for me despite of the couting of the jobs - I'd need to include also the CPU cores utylization generated by the jobs - see the ActiveCores formula:

ActiveCores =

CALCULATE (

    SUM (Data_Jan_Mai[No Of Cores]),

    GENERATE (

        VALUES ( DateTable[Date] ),

        FILTER (

            Data_Jan_Mai,

            CONTAINS (

                DATESBETWEEN (

                    DateTable[Date],

                    Data_Jan_Mai[Start Time],

                    Data_Jan_Mai[End time]

                ),

                [Date], DateTable[Date]

            )

        )

    )

)

On the chart solution I would need to see the CPU cores utylization (Y-axis) on the timeline (X-axis - but I think the date should be independend from the job start/end so without relation and provide full timeline). It would be great (but it's optional) to have some hierarchy - like for year/month/day/hour/minute/second on X-axis.

Anyway I'd need to go down to the minutes if not the seconds to see the particual consumption ans all the peaks.

Important note:

If there are some jobs active in particular hour or minute they might start and stop within the given period several times. So we cant's just sum them all together (agregate CPU consumption there) but take only the one which consumes the most CPU at the moment in time.

Attached there is a sample xls file (data source), with potential chart and probable solution formula in Excel (Active CPUs column).

I hope I clarified this case somehow but please let me know in case of questions.

Thanks in advance for your help.

Regards

W

Here are the data source and pbix

[v-chenwuz-msft]

Hi @Anonymous ,

I tried to do it at the minute level, referring to the pbix file.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-chenwuz-msft,

Thanks a lot for this.

In the mean time I was able together with my friend work out a proper DAX formula - which calculate the values properly.

Right now the only problem is with its performance - as it's quite slow. It takes ~20secs to popup results on the chart.

So I'd appreaciate some advise to tuning it.

Here is how it goes right now and down bewlow there is a pbix file for your reference.

DAX formula:

No Of Active Cores =

var minDate = MIN('Date'[DateTime])

var maxDate = MAX('Date'[DateTime])

return

    SUMX

    (

        CALCULATETABLE

        (

            'Data_Jan_Mai',

            ALL ('Date'),

            'Data_Jan_Mai'[Start Time] <= maxDate && 'Data_Jan_Mai'[End time] >= minDate            

        ),

        'Data_Jan_Mai'[No Of Cores]

    )

Thanks in advance for your help.

Regards

W

Link to pbix is --> here