Custom Sorting the x-axis with time value

[Mann]

Hi All,

I created a simple line graph with Time values in X axis and Measure dropped to value area as shown.
X axis: Column "Time" coming from Table A having time values like (08:00 AM, 08:05 AM, 08:10 AM... for 24 hours round the clock for multiple dates)
Y axis: Simple Measure (Sum of some column in Table A)

By defualt Power BI takes sorting from 12AM to 12AM. I need to show it from 8AM to 8AM.

I followed these steps:

1) Created a sort order column "Sort" in table A in Power Query such that: For rows having time value: 8:00 AM is given "1" and 8:05AM is given "2" and so on.

2) When I sort column "Time" with "Sort" I get a straight line in line graph. X axis still show values from 12:00 AM to 12:00 AM.

How can I achieve the sorting from 8:00 AM to 8:00AM (24hours)?

Please advice?

[v-piga-msft]

Hi @Mann,

Please have a try if you change the type of the X axis to be categorical.

In addition, if it is convenient, could you share your data sample which could reproduce your scenario so that we can copy and test.

Best  Regards,

Cherry

[Mann]

Thanks Cherry for reaching out.

I have shared the test file link in my post. Categorical won't help because x-axis is having data values for every 5 min for 24 hours. It will make this axis long. I am trying to achieve this using continuous only.

[Mann]

Hi Guys,

Just checking in if this is something which we can't achieve in Power BI? Please let me know what you think?

[Anonymous]

see if this is what you had in mind:

 

Key here is use the the Movement Hour from yoru Order table in the chart, be sure to sort that column  by your sort column in that table, and keep the formatting as text.  When switched to a time it defaults to 12:00:00 for some reason.  

[parry2k]

@Mann I looked at your pbix file and here is what you need to do to make it works:

- Sort "Movement Hour" in order tabe by sort order.

- drop "Movement Hour" on x-axis and application_id from main table on values

- on format page, change "type" of x-axis to "categorical" instead of "continuous"

and that will do it.

[Mann · Accepted solution]

Hi Parry,

Thanks for the reply. If I make x axis categorical then it extends the length of x axis which is not the requirement. So instead of using time value on x-axis, I used date time column and used a time slicer. Everytime user selects any date or date range graphs changes accordingly but keeps continuous x axis.

[v-piga-msft]

Hi @Mann,

It seems that you have find a workaround. Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[parry2k]

@Mann you are taking right approach by adding sort column and then sorting your time colum with "sort" column, it is expected to work. Can you share you pbix by chance to further look into it?

[Mann]

Hi Parry,

Thanks for connecting on this. Sorry couldn't reply on this yersterday.

I have created a Test.pbix file and placed it here Test.PBIX. It also contains two sample files I am using as a source to this pbix file.