Forum Discussion

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

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

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
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
  Helper V
  9 years ago
  Still, It said, "Cannot convert value '' of type Text to type Date."
- MAAbdullah47
  Helper V
  9 years ago
  Please note the column contents in text all of them are correct 100% , I did this checking through R in the following command:
  
  if( class( order_product$orders.opened_at ) == "try-error" || is.na( order_product$orders.opened_at ) ) print( "That wasn't correct!" )
  
  It didn't give me any warning or errors.
  
  So the assumption of text error is not possible, the problem from power bi editor.
- MAAbdullah47
  Helper V
  9 years ago
  Let me know how to convert the column into date time, I found many difficulties without clear instructions for altering the data type, I'll be appreciated if you can help me.