cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aront Regular Visitor
Regular Visitor

sort doesn't work well on non-alphabetic text fields

When you choose a sort field that is not in the report, it ignores that sort, even if it chose it as default. You can't force it to accept sort order from query - it seems to choose it's own random way of sorting.   And because there is no manual row sort either you have to resort to some ugly workarounds.

 

Our use case: we have a count by day of week. We set up a code so that we can properly sort those in query, but when data is displayed it is totally random order. In tabular form we can add the code and sort (ugly but workable) but when we do some visualization the "code" makes no sense so there seems to be absolutely no way to properly sort (even thought the code field is chosen as the sort key in the data modeling). We have same proble with Seasons and all kinds of similar fields where alphabetic sort is NOT correct even though the field is text.

 

Any ideas out there?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator WillT
Moderator

Re: sort doesn't work well on non-alphabetic text fields

Hi there - we have a feature called 'Sort By' that should be able to help you here, for example making weekdays or month names appear in the right order in visuals. More details on that here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/ Hope that helps you!

View solution in original post

5 REPLIES 5
Moderator WillT
Moderator

Re: sort doesn't work well on non-alphabetic text fields

Hi there - we have a feature called 'Sort By' that should be able to help you here, for example making weekdays or month names appear in the right order in visuals. More details on that here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/ Hope that helps you!

View solution in original post

aront Regular Visitor
Regular Visitor

Re: sort doesn't work well on non-alphabetic text fields

Thanks. Worked perfectly and solved my problem. I had already had a numeric index field to use to get it to sort the way I wanted. I was temporarily using that as the axis label. This is so much better! Smiley Happy

 

SNik Regular Visitor
Regular Visitor

Re: sort doesn't work well on non-alphabetic text fields

I have the same issue but its age banding and i am using a Slicer,

I have ...

Age    AgeBand

  1       [1-10]

  2       [1-10]

  3       [1-10]

  .

  .

 22   [21-30]

 23   [21-30]

 .

 .

and i am only using a Slicer and only displaying the [AgeBand] field , and i have sorted by [Age] and etc... but the [AgeBand] is not sorting correctly in the slicer.

the link doesn't work for slicers, any suggestion?

 

Sincerely
Nik- Shahriar Nikkhah
greggyb New Contributor
New Contributor

Re: sort doesn't work well on non-alphabetic text fields

@SNik, slicers follow the sort order defined in the data model, including allowing the use of the Sort By feature as described above. The sort-by field must 1:1 or 1:N with the field to be sorted. Your sample data is N:1 for sort-by field:field to be sorted, so you cannot define the sort column in your data model.

 

You should create a field, AgeBandSort which maps 1:1 with AgeBand, and includes integers in the order you'd like (e.g. 1 for all [1-10], 2 for all [11-20], and so on).

Highlighted
nixonyx Regular Visitor
Regular Visitor

Re: sort doesn't work well on non-alphabetic text fields

In the example supplied on the sort by column instructions it says "the data source table for Stage Label (in an Excel worksheet) has another column".

 

Does that mean I have to add another column to my data source in order to sort by the order I want, or can I create a look up table? I tried the latter but when I click on the column in data view and select sort on column, I am only presented with columns from that table. This is not helpful as I would prefer not to have to add extra processing to the source file before the report can be run.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 807 guests
Please welcome our newest community members: