Forum Discussion

dickovan's avatar
dickovan
Regular Visitor
9 years ago
Solved

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.

  • hugoberry's avatar
    hugoberry
    9 years ago

    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.

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    9 years ago

    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?

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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.:smileyhappy:

     

    Regards

    • hugoberry's avatar
      hugoberry
      Icon for Responsive Resident rankResponsive Resident
      9 years ago

      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.

      • dickovan's avatar
        dickovan
        Regular Visitor
        9 years ago

        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.