Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to categorize the time values into different categories

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. 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Nope, even this doesn't work fine! 

       

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        hi Anonymous 

        seems strange. i would propose the same. 

         

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

  • Thennarasu_R's avatar
    Thennarasu_R
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    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