cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
valcat27
Helper III
Helper III

Sort chronologically x axis based on date and time column

Hello all, 

 

I built a scatter chart with a date and time column on the x axis, but I am having some problems sorting it by date. I have already tried to adapt some solutions for similar problems but I couldn't solve it yet. 

 

My date column has the general format (14/03/2001 13:30:55) and when I select to sort the visual by this column, it does not sort it chronologically and I cannot understand how it is sorted. The first dates on the chart when sorting it ascending by date are:

- 05-09-2011 00:00:00

- 01-06-2011 00:00:00

- 07-09-2012 00:00:00

- 17-02-2012 00:00:00

- 09-09-2011 00:00:00

- 01-02-2012 00:00:00 ...

 

I tried to create a new column based on this one with the format "YYYYMMDDHNNSS" and sort the first column by this new one but it returned an error of circular dependency. I also thought to follow the same logic, but creating a new column with a sequence of numerical values in the same table sorted by the date column (since on the data tab date column is sorted correctly, chronologically), but I do not know how to do it neither if it makes sense. 

 

Can anyone help me?

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Hello @v-yuaj-msft ,

 

Thank you for your answer. 

Comparing with your data, I thought everything was equal, but then I noticed that the problem was related to the legend. In fact, when I excluded it, dates are sorted chronologically.

 

I think the problem is related to the model structure. The chosen column for the legend is from another table.  This table is not connected directly to the table that I'm using for the values on the y and x axis and the relationship between both tables are many to many.  Now I'm trying to recreate the model to be possible to include the legend and keep x axis sorted chronologically.

 

Best regards,

View solution in original post

18 REPLIES 18
v-yuaj-msft
Community Support
Community Support

Hi @valcat27 ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem?

 

Best Regards,

Yuna

v-yuaj-msft
Community Support
Community Support

Hi,

Based on your description, I did a test. Unfortunately, I cannot reproduce your issue. Please verify that my steps are as follows. Is there anything I missed?

v-yuaj-msft_0-1612935290541.png

 

v-yuaj-msft_1-1612935290543.png

v-yuaj-msft_2-1612935310833.png

v-yuaj-msft_3-1612935327911.png

 

Best Regards,

Yuna

 

Hello @v-yuaj-msft ,

 

Thank you for your answer. 

Comparing with your data, I thought everything was equal, but then I noticed that the problem was related to the legend. In fact, when I excluded it, dates are sorted chronologically.

 

I think the problem is related to the model structure. The chosen column for the legend is from another table.  This table is not connected directly to the table that I'm using for the values on the y and x axis and the relationship between both tables are many to many.  Now I'm trying to recreate the model to be possible to include the legend and keep x axis sorted chronologically.

 

Best regards,

View solution in original post

Hello,

I encouter the same issue : using a scatter chart with date as X-axis and it is not sorted chroinologically when I use a legend. I think I understood the sorting strategy. Power BI plots the first column type of the legend using the chronological order. If another type of the legend contains data on the same date it is also plotted in the chronological order. If there is no data on a given date of the first type it is skipped. Once first column is read, it follows with next column type,... Better than my explanation, hear is a picture that is probably easier to understand 

However, I don't have the solution to solve this issue. The only thing I can think of is to create a fake null value on each date for all types but it doesn't seem to be a robust solution.

Does anyone have an idea on how to solve it.

Thanks in advance for you support. 

Best regards,

Benjamin2021-08-03_19h52_01.png

Hello, I found a solution by showing blank values as described in the post 

https://community.powerbi.com/t5/Desktop/Sort-x-axis-by-time-instead-of-legend-scatter-chart-without...

It is quite close to what I had in mind but easier.

v-yuaj-msft
Community Support
Community Support

Hi @valcat27 ,

 

Actually I did nothing. I just created the date and imported it into Power BI Desktop. I suggest that you can modify your date format, because when I import the sample data, "17-02-2001" shows the TEXT format. Power BI Desktop currently cannot convert TEXT format to date format. You can modify the format of the date column of the data source to "mm/dd/yyyy". I tested the following sample data without any problems.

v-yuaj-msft_0-1612748258732.pngv-yuaj-msft_1-1612748279246.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hello @v-yuaj-msft,

 

I'm sorry, but I don't understand your solution. Where should I exactly change the format? 

I imported my data directly from SQL Server and Power BI recognize this column's data type as date/time. On data left tab, I tried to change the data format to different formats, but the visual continues with the same incorrect sorting. 

Hi @valcat27 ,

 

Sorry for that I assuming you are using an Excel file as data source. Could you please share a screenshot of your date column in SQL server with me to show me more the date column details? What's the data type of your date column in SQL Server? 

v-yuaj-msft_0-1612857165011.png

 

Best Regards,

Yuna

 

Hello @v-yuaj-msft,

 

In SQL server the type of my date column is datetime.

valcat27_0-1612890573366.png

(I ran this code to get the output above:

select COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Table1')

 

This is an example of some values in that column in SQL:

valcat27_1-1612890573369.png

 

Is this the information you were asking for?

 

Thanks

v-yuaj-msft
Community Support
Community Support

Hi @valcat27 ,

 

I did a test. Is the following result what you want?

v-yuaj-msft_0-1612490669525.png

Best Regards,

Yuna

Hello Yuna, 

Yes, it is.  How can I do the same with my data?

parry2k
Super User
Super User

@valcat27 if you click on "sort by"  are you sure it is the same column as used on x-axis. I think you mentioned it is, but can you share the screenshot, it doesn't make sense at all.

 

 

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yes, I’m sure. I also tried to change it to “sort by y –axis” to validate and the dates appear more random (than before) like 2018, 2019, 2016, 2014, 2019, 2019, 2019. Moreover, when I select “sort by date column” and “sort descending”, the first dates appearing in the visual are in fact the last years, but again sorted only partially chronologically.

valcat27_0-1612452450427.png

 

 

Hi @valcat27 ,

 

Apologies for jumping in here.

This is weird. Is it possible for you to share your pbix file after removing any sensitive information?

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




I'm sorry but unfortunately I cannot share my pbix file... I cannot share more than the type of print screen /information that I have shared here

valcat27
Helper III
Helper III

 @Pragati11 and @parry2k thank you for your answers. 

 

The data that I can share is a bit limited, but I hope it is sufficient. 

1)The figure below shows how the visual looks like and, as you can see, x axis is not sorted chronologically.

valcat27_0-1612357966395.png

 

2) To sort the visual, on “More options” (…), I selected “Sort ascending” and “Sort by date column”.

valcat27_1-1612357966399.png

 

3) On data tab/section, selecting the date column, in the table, I can verify that values are sorted chronologically and the column type is Date/Time.

 

valcat27_2-1612357966404.png

        

valcat27_3-1612357966407.png

 

 

 

Pragati11
Super User
Super User

Hi @valcat27 ,

 

First thing you haven't attached any screesnhots around you issue with the visual, do add it so makes the understanding better.

The column should be of DATETIME format. Check this again. Also share some screenshots from data where this column has date datatype.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




parry2k
Super User
Super User

@valcat27 what is your column type? Is it date/time? Can you share the screenshot of where you are sorting in the chart and how it looks like?






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.