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.





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

 

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.





Highlighted
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"
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")

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 133 members 1,830 guests
Please welcome our newest community members: