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.

Highlighted

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

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,086)