Forum Discussion

SHAKEDALROY's avatar
SHAKEDALROY
Helper I
5 years ago
Solved

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?

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, SHAKEDALROY 

     

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

    Test:

     

    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:

     

    Best Regards

    Allan

     

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

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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

     

     

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

     

     

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

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, SHAKEDALROY 

     

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

    Test:

     

    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:

     

    Best Regards

    Allan

     

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