cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Grouping times

Hi,

I have a Table with data about Traffic Reports

ViolationCode, IssuerKey, DateOfViolation,TimeOfViolation etc..

I need to create a vizualization that shows the amount of violations for every two hours.

 

Therfore,

I need to group a set of times throughout a day into different categrories ( Every two hours)

i.e. 8am-10pm is group 8 to 10 am. 10am to 12pm is group 10 to 12 am and so on.

 

The grouping/bin fucntion  is still not working for me.

 

can anyone please provide some help for a formula?

2 ACCEPTED SOLUTIONS
Super User III
Super User III

Hi @SHAKEDALROY 

Assuming you have a Table1[DateTime] column in your fact table, create another column that specifies the corresponding two-hour category.

 

Two-hour category =
VAR time_ =
    Table1[DateTime] - INT ( Table1[DateTime] )
RETURN
    SWITCH (
        TRUE (),
        time_ < 2 / 24, "00:00-02:00",
        time_ < 4 / 24, "02:00-04:00",
        time_ < 6 / 24, "04:00-06:00",
      // Fill in all the remaining options here....
      //...  
        time_ < 24 / 24, "22:00-00:00"
    )

 

or alternatively:

 

Two-hour category V2 =
VAR time_ = Table2[DateTime] - INT ( Table2[DateTime] )
VAR aux_ = FLOOR ( time_ * 24, 2 )
RETURN
    FORMAT ( aux_, "00" ) & "-" & FORMAT ( aux_ + 2, "00" )

 

You can then use this column in a chart for instance and easily build a measure that calculates the number of items per two-hour category

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

View solution in original post

Community Support
Community Support

Hi, @SHAKEDALROY 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Test:

a1.png

 

You may create a custom column with the following m codes.

let 
d = [Date],
h = Time.Hour(d)
in 
if h>=0 and h<2 then "0-2"
else if h<4 then "2-4"
else if h<6 then "4-6"
else if h<8 then "6-8"
else if h<10 then "8-10"
else if h<12 then "10-12"
else if h<14 then "12-14"
else if h<16 then "14-16"
else if h<18 then "16-18"
else if h<20 then "18-20"
else if h<22 then "20-22"
else if h<24 then "22-24"
else null

 

Result:

a2.png

 

Best Regards

Allan

 

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

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi, @SHAKEDALROY 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Test:

a1.png

 

You may create a custom column with the following m codes.

let 
d = [Date],
h = Time.Hour(d)
in 
if h>=0 and h<2 then "0-2"
else if h<4 then "2-4"
else if h<6 then "4-6"
else if h<8 then "6-8"
else if h<10 then "8-10"
else if h<12 then "10-12"
else if h<14 then "12-14"
else if h<16 then "14-16"
else if h<18 then "16-18"
else if h<20 then "18-20"
else if h<22 then "20-22"
else if h<24 then "22-24"
else null

 

Result:

a2.png

 

Best Regards

Allan

 

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

View solution in original post

Super User III
Super User III

Hi, @SHAKEDALROY , you might want to use these simple codes to create a bin table named Bins,

 

let
    Source = Table.AddColumn(Table.TransformColumns(Table.FromRows(List.Split({0..23},2), {"start","end"}), {{"end", each _+1}}), "bin", each Text.From([start]) & "h to " & Text.From([end]) & "h")
in
    Source

 

Screenshot 2020-12-05 174012.png

 

Then a DAX formula to count records in each period,

 

Count =
COUNTROWS (
    FILTER (
        Report,
        HOUR ( Report[TimeOfViolation] ) >= MIN ( Bins[start] )
            && HOUR ( Report[TimeOfViolation] ) < MIN ( Bins[end] )
    )
)

 

 

Screenshot 2020-12-05 220710.png

I might want to refer to the attached file for details.

 

Super User III
Super User III

Hi @SHAKEDALROY 

Assuming you have a Table1[DateTime] column in your fact table, create another column that specifies the corresponding two-hour category.

 

Two-hour category =
VAR time_ =
    Table1[DateTime] - INT ( Table1[DateTime] )
RETURN
    SWITCH (
        TRUE (),
        time_ < 2 / 24, "00:00-02:00",
        time_ < 4 / 24, "02:00-04:00",
        time_ < 6 / 24, "04:00-06:00",
      // Fill in all the remaining options here....
      //...  
        time_ < 24 / 24, "22:00-00:00"
    )

 

or alternatively:

 

Two-hour category V2 =
VAR time_ = Table2[DateTime] - INT ( Table2[DateTime] )
VAR aux_ = FLOOR ( time_ * 24, 2 )
RETURN
    FORMAT ( aux_, "00" ) & "-" & FORMAT ( aux_ + 2, "00" )

 

You can then use this column in a chart for instance and easily build a measure that calculates the number of items per two-hour category

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors