Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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.
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.
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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