Forum Discussion

MAAbdullah47's avatar
MAAbdullah47
Helper V
9 years ago

Creating conditional column based on time for another column

Dear All     I created 3 global variables with name: Morning, Noon and evening and I set default values '11:00' ,'16:00' and '23:59' for each variable respectively. Now I need to add new condition ...
Show and Tell
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Are you doing this in Power Query (M) code or in the data model and want to use DAX?

MAAbdullah47's avatar
MAAbdullah47
Helper V
9 years ago

The User group moved and I couldn't found the answer.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Here is one way and you do not need any kind of global variable or anything, just a calculated column like this:

     

    Column = IF([Time]<TIME(11,0,0),"Morning",IF([Time]<TIME(16,0,0),"Noon","Evening"))

    You could modify this to just do less than 16:00:00 be Lunch, otherwise "Dinner".

    • MAAbdullah47's avatar
      MAAbdullah47
      Helper V
      9 years ago

      Thank you but I still don't know where I put this command when I create a new field (conditional column)? 

    • MAAbdullah47's avatar
      MAAbdullah47
      Helper V
      9 years ago

      Ok I got it, but There is an error said: 

       

      "DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago
        MAAbdullah47 wrote:

        Ok I got it, but There is an error said: 

         

        "DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

        What is the type of "orders.opened_at", I think it is a TEXT type, that's why you get such error.

        Try to convert it to a Date/Time type and apply

        Meal = IF(HOUR('order product'[orders.opened_at])<11,"Breakfast",IF(HOUR('order product'[orders.opened_at])<18,"Lunch","Dinner"))

        You'll do more conversion work if "orders.opened_at" is not a valid date format text.

    • MAAbdullah47's avatar
      MAAbdullah47
      Helper V
      9 years ago

      The text is here:

       

      Meal = IF('order product'[orders.opened_at]<TIME(11,0,0),"Breakfast",IF('order product'[orders.opened_at]<TIME(18,0,0),"Lunch","Dinner"))