Reply
Frequent Visitor
Posts: 3
Registered: ‎03-20-2017
Accepted Solution

quarter hour

This is my first Power BI project. Have a datetime field and want to have the x-axis be by quarter hour. I created a new column and, using nested if statements, reformatted the time to be at 15 minute intervals. However, when I use this column in a report, the x-axis is not in the correct order. It goes 10:15 am, 10:30 am, 10:45 am... 4:30 pm, 4:45 pm, 7:15 am, 7:30 am... 10:00 am.

 

I created a column for setting the sort order which is a concatenation of the hour and a 1, 2, 3, or 4  based on the minute but it was no change.

 

Thanks in advance for your consideration.


Accepted Solutions
Member
Posts: 99
Registered: ‎02-03-2017

Re: quarter hour

[ Edited ]

As mentioned by  @parry2k you are on the right track, just over engineering your sorting.

Sot by the original date time field and make sure that the data type is date time.

View solution in original post


All Replies
Super Contributor
Posts: 669
Registered: ‎07-22-2015

Re: quarter hour

You are on right track, creating sort column and then on modelling, chooise your hour column (then on axis) and sort if by sort column.

 

Can you share the screen shot of both these fields in a table or so to understand the problem?

Super Contributor
Posts: 2,655
Registered: ‎07-17-2016

Re: quarter hour

Hi @dickovan,

 

According to your description above, using Sort by Column option under Modeling tab should work in your scenario. 

 

Could you post your table structures with some sample data which can reproduce the issue? So that we can better assist on it. It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy

 

Regards

Member
Posts: 99
Registered: ‎02-03-2017

Re: quarter hour

[ Edited ]

As mentioned by  @parry2k you are on the right track, just over engineering your sorting.

Sot by the original date time field and make sure that the data type is date time.

Frequent Visitor
Posts: 3
Registered: ‎03-20-2017

Re: quarter hour

[ Edited ]

After noodling on this, given all of your input, I realized that the x-axis sequence was based on an alpha sort of the underlying time column (10, 11, 12, 1:, 2:, ...7:,  ) with the last column, 10:00, being based on times between 9:45:01 and 9:59:59.

 

So Power BI knew the column was time (it had formatted the times that I passed it from 2:15 to 2:15:00 PM) it was treating the values as alpha for the x-axis. I reformatted the column to TIME and all was well.

 

Thank you all for your ideas.