cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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 column based on column name called time_open_at check if the time <= Morning then it's breakfast, and BTW Morning And Noon then 'Lunch' otherwise if > Noon and < Evening then 'Dinner' how shall I do this?

21 REPLIES 21
Super User
Super User

Re: Creating conditional column based on time for another column

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Creating conditional column based on time for another column

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Re: Creating conditional column based on time for another column

I'm Using It In Power BI DiskTop, I'm still new Kindly give me the way of doing it regardless using M or any other methods.

Re: Creating conditional column based on time for another column

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

Highlighted
Super User
Super User

Re: Creating conditional column based on time for another column

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Re: Creating conditional column based on time for another column

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

Re: Creating conditional column based on time for another column

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

Re: Creating conditional column based on time for another column

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

Re: Creating conditional column based on time for another column

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)