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.

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

Highlighted

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)