Forum Discussion

Drewz's avatar
Drewz
Helper II
6 years ago

Dynamically Find Peak Hour Time

Hi All, I've been searching the internet and the Power BI community for ages and I can't find anything that matches what I'm trying to do, so I'm stumped.    I have intersection vehicle count data...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Drewz - See page 2. I'm still not sure exactly what you want but I did a chart that had the time in the correct format and date as well as created an Hour column and showed it that way. If you really want to chart to essentially only show peak traffic, you would need to implement what I call a Complex Selector. You can see several examples here.

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Your PBIX is attached with updates. (below sig)

Drewz's avatar
Drewz
Helper II
6 years ago

Hi Greg_Deckler ,

Thanks for the updated pbix. The tricky part that is that the hour when the traffic volume is highest may not be 7 am to 7:59 am (e.g all the hours that start with 7). It might be from 7:45 am to 8:44 am, and therefore my calculation would need to work out to sum the values at 7:45, 8:00, 8:15 and 8:30 am. And each site will have a different peak hour, so I want to dynamically calculate when the peak hour occured depending on what site is selected using a slicer. If I can dynamically work out when the peak our occurs, then I'd be able to create a slicer to show either peak hour count data or all count data.

 

Did my explanation help?

 

Thanks for the tip about complex selectors. I had a quick look, but I'll need some time to study them to understand how they work.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Drewz - Yes, this helps greatly, let me see what I can do with this additional information.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Drewz - I've been giving this some thought. If your data comes in every 15 minutes, you should be able to have a table of your times in 15 minute increments. If you use that in your visual, you should be able to construct a measure that takes that time and filters your fact table for >= MAX('TimeTable'[time]) && <= (MAX('TimeTable'[time]) + 1/24)

     

    I'm thinking that should work.

    • Drewz's avatar
      Drewz
      Helper II
      5 years ago

      Hi Greg_Deckler,

      I made a bit of progress, but I'm stuck again. Here's a link to my updated pbix file.

       

      I wasn't sure how to progress your suggestion with the MAX filters specifically, but I have a separate TimeTable as suggested and I used the following measure to give me running total in hours. 

       

      RollingHourlyVehicles = Var FromTimeID = SELECTEDVALUE(TimeTable[TimeIndex])
      Var ToTimeID = FromTimeID + 3
      Return CALCULATE([Total Vehicles],
      FILTER(ALL('TimeTable'), TimeTable[TimeIndex]>=FromTimeID && TimeTable[TimeIndex]<=ToTimeID)
      )
       

      and the following measure to give me the peak hour

      PeakHour = CALCULATE(MAXX(VALUES(TimeTable[Actual Time]), [RollingHourlyVehicles]))

       

      Only problem is that in a card the peak hour is a single value of the highest count (which is what I want), but in a table context it is different for each time. How do I extract the peakhour as a single value? e.g. just as the number 4009. I can't figure out how to get the max value of a measure. Next step, I can see that the peak hour starts at 7:45 am (it counts the traffic data for 7:45, 8:00, 7:15 and 7:30 am). But how would I extract the peak hour time as a value so that I can use it as a filter on my data?  

       

       

      Any help greatfully received as I've been battling this for hours.