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

Daypart calculations for new column

 

I am looking to create a new column in my dataset that classifies time between 9am and 5pm into one bucket ( say "business hours")  and 5pm to 8 am as "after hours". I have a [TIME] column on which this is based.

 

I am totally new to PowerBI and getting myself confused between the difference between when the DAX language is needed and when the M language is needed. I am doing this on the PowerBI desktop.  Strange - I cant find any discussion about daypart manipulations ( maybe didnt look enough).

 

My query to create the column:

 

if ( [Time] >= "9:00:00 AM" and [Time] <= "5:00:00 PM" ) 
then "Business hours" 
else "After hours"

 

Getting "error" values in the new column created. Tried many iterations without success.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Daypart calculations for new column

here is something i tried, i created hour column using DAX , hope it is helpful

hour1.PNG

 

Dax Expression

 

Hours = if(sample2[Time1] >= time(9,0,0) && sample2[Time1] <= time(17,0,0), "Business", "After")





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

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

juju Member
Member

Re: Daypart calculations for new column

The solution by parry2k works if you are using DAX to create the formula for the new column ( directly from the Report tab). But when trying to addd a new "custom column" from the query editor, looks like the M language is used there instead ( confusing!!). Here is the M langauge that worked for me there :

 

 

if [Time] >= Time.FromText("09:00:00") and [Time] <= Time.FromText("17:00:00") 
then "Business Hours"
else "After Hours"

 

The comparable DAX formula from the Report tab is:

 

 

if ([Time] >= time(9,0,0) && [Time] <= time(17,0,0) ) 
, "Business hours" 
, "After hours")

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Daypart calculations for new column

I think following will work, may be some syntax issue but just to give you an idea, add a column and create following DAX:

 

if (Table[Time] >= time(9,0,0) && Table[Time] <= time(17,0,0) ) 
, "Business hours" 
, "After hours")





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

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Daypart calculations for new column

here is something i tried, i created hour column using DAX , hope it is helpful

hour1.PNG

 

Dax Expression

 

Hours = if(sample2[Time1] >= time(9,0,0) && sample2[Time1] <= time(17,0,0), "Business", "After")





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

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

juju Member
Member

Re: Daypart calculations for new column

Strange: I am using this ( modified from your code above) still not working. Getting a "token literal expected" error in the editor:

 

I have only one table so didint use the qualified table reference name. the error is on the "&&" part.

 

if([Time] >= time(9,0,0) && [Time] <= time(17,0,0), "Business", "After")

 

FYI I am doing this from the query editor screen and clicking on "custom column" under "add column" tab on the ribbon.
juju Member
Member

Re: Daypart calculations for new column

If i change it to below, the syntax error goes away but now new error : "Expression Error " The name 'time' wasnt recognized. Make sure its spelled correctly "!!

 

Notice "time' in lowercaps:

 

if [Time] >= time(9,0,0) and [Time] <= time(5,0,0) then "9am-5pm" else "6pm-8am"
Highlighted
juju Member
Member

Re: Daypart calculations for new column

This worked for me only when I created the formula from the "Report " tab using the Dax editor there like you did in your exmaple. The same formula throws errors when doing so from the query editor ( via "edit queries" on ribbon)

juju Member
Member

Re: Daypart calculations for new column

The solution by parry2k works if you are using DAX to create the formula for the new column ( directly from the Report tab). But when trying to addd a new "custom column" from the query editor, looks like the M language is used there instead ( confusing!!). Here is the M langauge that worked for me there :

 

 

if [Time] >= Time.FromText("09:00:00") and [Time] <= Time.FromText("17:00:00") 
then "Business Hours"
else "After Hours"

 

The comparable DAX formula from the Report tab is:

 

 

if ([Time] >= time(9,0,0) && [Time] <= time(17,0,0) ) 
, "Business hours" 
, "After hours")

 

View solution in original post

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: 44 members 1,079 guests
Please welcome our newest community members: