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

        Still, It said, "Cannot convert value '' of type Text to type Date."

  • 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"))

  • MAAbdullah47's avatar
    MAAbdullah47
    Helper V
    9 years ago

    I tried to work around:

     

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

     

    But still not working