How to categorize the time values into different categories

[Anonymous]

Hi everyone, I have a TIME column in my table. I want to categorize 24 hours of a day into different categories as per the snapshot below. I tried using SWITCH() formula but it's not working. Can somebody help me to solve this, please? I am attaching the snapshot below. 

Quick responses are very much appreciated. Thanks in advance. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

2. create a new column with below dax formula

Time_category =
VAR cur_time = [Time]
VAR _category =
    SWITCH (
        TRUE (),
        cur_time >= TIME ( 6, 0, 0 )
            && cur_time < TIME ( 11, 59, 59 ), "Breakfast",
        cur_time >= TIME ( 12, 0, 0 )
            && cur_time < TIME ( 15, 59, 59 ), "Lunch",
        cur_time >= TIME ( 16, 0, 0 )
            && cur_time < TIME ( 19, 59, 59 ), "Evening Snacks",
        cur_time >= TIME ( 20, 0, 0 )
            && cur_time < TIME ( 23, 59, 59 ), "Dinner",
        cur_time >= TIME ( 0, 1, 0 )
            && cur_time < TIME ( 5, 59, 59 ), "Midnight Craving"
    )
RETURN
    _category

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thennarasu_R]

Hi !
@Anonymous 

Switch(True(),

Time Column >=Time column=Time(5,0,0) && [Time] < =time(12,0,0) , "MidNight Carving",

Time Column >=Time column=Time(12,1,1) && [Time] <= time(16,0,0) , "Breakfast",

Time Column >=Time column=Time(16,1,1) && [Time] < =time(16,0,0) , "Lunch",

Time Column >=Time column=Time(6,1,1)  , "Evening Snack"

)

Thanks ,
Thennarasu R

[Anonymous]

Hi @Thennarasu_R , this shows value error

[amitchandak]

@Anonymous , Seem fine , Try a new column like and check

Switch(True(),

[Time] < time(5,0,0) , "MidNight Carving",

[Time] < time(12,0,0) , "Breakfast",

[Time] < time(16,0,0) , "Lunch",

[Time] < time(16,0,0) , "Evening Snack",

"Dinner"

)

[Anonymous]

Nope, even this doesn't work fine! 

[FreemanZ]

hi @Anonymous 

seems strange. i would propose the same. 

BTW, what is the data type of the Time column?