Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bcastillo
Frequent Visitor

Issues with order in column

Hello, 

 

I am working on a line chart and the x-axis is supposed to have rolling quarters but power bi is sorting the quarters for me and they are out of order. Here is the picture of my data and what I am getting:

 

example2.PNGexamples.PNG

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @bcastillo

I can reproduce your problem.

My "Quarterly data" column is of text type, when I add it to the axis of the line chart,it will sort by another column of number type by default. picture below is an example.

2.png

 

One workaround is:

create a Index column in Query editor

1.png

Then in Report View, select "Quarterly data" column, then select on the Modeling->Sort by column, click on the "Index" column.

Next on the Line chart, select the three dots on the upper-right of it, select "Sort by"->"Quarterly data".

4.png

5.png

 

 

 

Best Regards

Maggie

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @bcastillo

I can reproduce your problem.

My "Quarterly data" column is of text type, when I add it to the axis of the line chart,it will sort by another column of number type by default. picture below is an example.

2.png

 

One workaround is:

create a Index column in Query editor

1.png

Then in Report View, select "Quarterly data" column, then select on the Modeling->Sort by column, click on the "Index" column.

Next on the Line chart, select the three dots on the upper-right of it, select "Sort by"->"Quarterly data".

4.png

5.png

 

 

 

Best Regards

Maggie

Seward12533
Solution Sage
Solution Sage

Need to add a column wiht a numeric year quarter. If you dont' have it in your date table you can add by adding a calculated column

Year Quarter Number = YEAR([date]&FORMAT([date],"Q")

Then form the Table View Select the Text column for the Q-YR and choose sort by and select Sort By.

 

 

If you don't see a sort option at all there may be a sort otpion you need to change the option in the AXIS properties for type and choose categorical instead of continuous.

 

capture20180808163555640.png

Also, I do not have the option to select continuos, if I click it it goes right back to categorical

screenshot.PNG

I am getting an error message when I use that code 

Spoiler
Cannot convert value 'Q2-17Q2-17' of type Text to type Date.

 

Format will require it in date format. Instead just extract the year and quarter number from your text.
Something like VALUE(RIGHT([Quarterly Data],2)&MID([Quarterky Data],2,1])

May need to play with the start on the Mid I always get confused if it starts with 0 or 1 as the first character.

screenshot2.PNG

 

When I try to change the format as a Date it gives me this error

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.