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

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 355 members 3,688 guests
Please welcome our newest community members: