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
Highlighted
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
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)