topic Peak Hour Traffic in DAX Commands and Tips

Peak Hour Traffic

daciga — Sat, 14 Sep 2019 22:54:46 GMT

Hi all,

Need help with my dax calculation. How do I get the Peak Hour Traffic calculation in power bi. Below is the sample metrics.

Thanks a lot.

Re: Peak Hour Traffic

Nathaniel_C — Sat, 14 Sep 2019 23:15:17 GMT

Hi daciga ,

We need more info, what does your data look like?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Peak Hour Traffic

daciga — Sat, 14 Sep 2019 23:34:36 GMT

Hello Nathaniel,

Kilndly see attached CSV file. In the data its obvious that the Max Call Time is 2PM. What I need to know is the DAX formulation to find the desired computation. I have looked over the same topic here in PBI community but none of them captured my DAX problem.

Many thanks,

Re: Peak Hour Traffic

Nathaniel_C — Sat, 14 Sep 2019 23:53:02 GMT

Hi daciga ,

Thank you for the file, but I see no 2 PM calls. Also What is Group_time? So are you aggregating these by the hour?

Re: Peak Hour Traffic

daciga — Sun, 15 Sep 2019 00:04:20 GMT

Hello Nathaniel,

Yes exactly. you may remove the group time since it should not be included in the calculation. What is needed is the number of calls per hour and which ever is the time with most calls (i.e. 2PM in my example) should be the "Max Call Time".

Other calculation here in PBI community only solved the average calls per time as shown in their computation. Whenever I tried the MAXIMUM formulation it only produces the maximum time (i.e. 24 Hours or simply 24) as the output.

Thanks again.

Re: Peak Hour Traffic

daciga — Sun, 15 Sep 2019 00:09:14 GMT

Its 2AM and not 2PM. My mistake.

Re: Peak Hour Traffic

Nathaniel_C — Sun, 15 Sep 2019 01:49:44 GMT

Hi daciga

I have a visual for you. First I added a new column and ended with table. The new column just peeled off the hour. I changed it to a whole number type. Then created a matrix by dropping Calls[Hours] which groups the different hours. Then created a measure that sums up the number of calls per hour. So as you see, 2 has the most calls 12.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

= Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time]))

Count = SUM('Calls'[Hour])

Re: Peak Hour Traffic

Nathaniel_C — Sun, 15 Sep 2019 03:07:03 GMT

Hi daciga ,

For the max count as a card.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Count max per Hour = 
MAXX(KEEPFILTERS(VALUES('Calls'[Hour])), CALCULATE([Count]))

Gives you:

Re: Peak Hour Traffic

daciga — Sun, 15 Sep 2019 05:30:14 GMT

Hello Nat,

Thank you for this excellent calculation! The next challenge would be is how to show in card the most frequent time or most calls that entered into the system say, 2AM in our data as the peak time.

Thanks,

Re: Peak Hour Traffic

Nathaniel_C — Sun, 15 Sep 2019 12:49:24 GMT

daciga ,

Ok, try this:

New table which I called Summary, which I did in Power BI using SUMMARIZE()

Summary = SUMMARIZE(Calls,Calls[Hour],"HoursSummary",Sum(Calls[Hour]))

Then I added a column, using LOOKUPVALUE()

High hour = LOOKUPVALUE(Summary[Hour],Summary[HoursSummary],[Count max per Hour])

which gave me this table,

and created a measure,

Peak Hour Calls = max(Summary[High hour])

which I could display in card.

You could change it to Peak Hour or Peak Hour for Calls to be more descriptive.

Thanks for posting this interesting problem!

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Peak Hour Traffic

Nathaniel_C — Sun, 15 Sep 2019 23:50:14 GMT

Hi daciga ,

Went back to Power Query, changed the type of Calls[Time] to text and added a new column with if Calls [Time] contains AM then 1 or else if Calls [Time] contains PM then 2 or else null. This way we can add AM/PM back in to the result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTKyMjJQcPRVitWJVjIC800sYXxjMN8QLm8C4ZvD+KYQPly9GZhvCldvDuQbI+m3gPDNYHxLIN8EyTxDA6CAKcxBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Hour"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Hour", Int64.Type}, {"Time", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "AM/PM", each if Text.Contains([Time], "AM") then 1 else if Text.Contains([Time], "PM") then 2 else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"AM/PM", Int64.Type}})
in
    #"Changed Type2"

Then added a new column in Power BISummary[AM/PM2] to use LOOKUPVALUE () to search the CALLS table.

AM/PM2 = LOOKUPVALUE(Calls[AM/PM],Calls[Hour],Summary[High hour])

Then rewrote Peak Hour Calls to surround what we had with a CONCATENATE() and included an IF to pick AM or PM.

Peak Hour Calls = CONCATENATE(FORMAT(max(Summary[High hour]),"General Number"), If(MAX(Summary[AM/PM2]) = 1," AM"," PM"))

You can expand the if to include the null possibility.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Peak Hour Traffic

daciga — Mon, 16 Sep 2019 12:03:06 GMT

Hello again,

The computation worked!

However, when I add more calls or 5,000 calls for 7 days and use a slicer to group the calls per day, the peak hour card you made didnt work or remain static at 2AM. What could possibly went wrong?

Many thanks.

Re: Peak Hour Traffic

Nathaniel_C — Mon, 16 Sep 2019 14:41:11 GMT

Hi daciga ,

When you say you add more calls...do you mean calls on the same day? Or is it a second day that is the issue?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Peak Hour Traffic

Nathaniel_C — Mon, 16 Sep 2019 23:06:32 GMT

Hi daciga ,

Ok it looks like I used SUM at one point, when I should have used COUNT. So going back to the beginning I added more calls spread into the PM. Midnight is 12 AM by convention.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc+7DcQgDIDhVU7UKfALQrob4KTrUfZfI44dHET5f4Blek+QtoQH5s/3l86tJ7TmNpqsIc7Zu44W77hfrCXuV22a3u/eZXTT5mkeZAWZFoJ7Q8Ajv4I2Q+H/ANmbCdgAMEAcKKA4cEB1kIB9hbYA+qr6lwH3qnXaA3EFej7jQ84L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.Hour([Time])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Hour"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Time] < #time(12, 0, 0) then 1 else 2),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "AM/PM"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"AM/PM", Int64.Type}})
in
    #"Changed Type1"

So, now we have 23 calls.

2 tables

This table created by:

Summary = SUMMARIZE(Calls,Calls[Hour],"HoursSummary",COUNT(Calls[Hour]))

Calls per hour = max(Summary[HoursSummary])

Lookup Hour = LOOKUPVALUE(Summary[Hour],Summary[HoursSummary],Summary[Calls per hour])

Lookup AM/PM = LOOKUPVALUE(Calls[AM/PM],Calls[Hour],MAX(Summary[Lookup Hour]))

Then measures:

Highest # of Calls per Hour = Max(Summary[Calls per hour])

Peak Hour for Calls = 
IF(Value(MAX(Summary[Lookup Hour]))>12,CONCATENATE(Value(Max(Summary[Lookup Hour]))-12," PM"),
CONCATENATE(Value(max(Summary[Lookup Hour])), " AM"))

Total Calls = Count(Calls[Hour])

and the visuals

You may have to do a little work as the Time() function calls the hour from midnight to 1 AM the 0 hour as you can see by the top value in this picture.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Peak Hour Traffic

daciga — Tue, 17 Sep 2019 13:27:17 GMT

First of all, thank you for your effort into doing the calculation!

Some slight problem. I cannot seemed to go pass through the lookup hour calculation for I keep on getting an error message on that part.

Re: Peak Hour Traffic

Nathaniel_C — Tue, 17 Sep 2019 14:22:33 GMT

Hi daciga ,

PBIX for Peak Hours

Here is the file for what I have so far. What is the error that you are getting?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Peak Hour Traffic

Anonymous — Fri, 27 Mar 2020 22:12:55 GMT

Nathaniel_C Your answer helped me a lot to get started with my problem in hand.

I have posted it here, https://community.powerbi.com/t5/Desktop/Find-peakhour-and-PeakCount-based-on-time-based-data-for/m-p/996135#M473599

Could you please help me on that?

I need to do the same but data needs to be filtered by selected Date as well. Thanks

Re: Peak Hour Traffic

btfly_x23 — Tue, 03 May 2022 08:11:06 GMT

How did you calculate the HoursSummary - where does it refer to?

Re: Peak Hour Traffic

btfly_x23 — Mon, 16 May 2022 13:05:24 GMT

I have the same task to determine the Peak Hours Traffic, but, in my case, I do not have HoursSummary given. Therefore, I would be interested to know how can I display the Peak Hours in the visual "Card" without having this info "HoursSummary"? Or is it better to use charts for visualisation of peak hours?

Re: Peak Hour Traffic

btfly_x23 — Wed, 18 May 2022 07:28:37 GMT

daciga Nathaniel_C could you clarify my question above, please?