Forum Discussion

valcat27's avatar
valcat27
Icon for Helper III rankHelper III
5 years ago
Solved

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 ...
Need Help
  • valcat27's avatar
    valcat27
    5 years ago

    Hello Anonymous ,

     

    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,

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

 

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.

 

  • valcat27's avatar
    valcat27
    Icon for Helper III rankHelper III
    5 years ago

    Hello Anonymous,

     

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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? 

       

      Best Regards,

      Yuna

       

      • valcat27's avatar
        valcat27
        Icon for Helper III rankHelper III
        5 years ago

        Hello Anonymous,

         

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

        (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:

         

        Is this the information you were asking for?

         

        Thanks