Sort by column

Sort by Column is a feature in power BI with which you can change the default sort order of a column. This would typically be with ordinal fields of type character. Some examples are for instance the result of a questionnaire (strongly agree, agree, neutral, disagree, strongly disagree) or month names (January, February, etc.). Guy in a Cube shows how this works in this video.

How it works

Month name is a character field and would be sorted by default alphabetically. To change this you can use Sort by Column under modeling in the ribbon to select a different column to sort by, in this case month number. A requirement for this to work is that every unique option in your original column has a unique number in the column you use to sort by. In the case of the month names you can use the month numbers (1 to 12). It is not possible to use the quarter number (1 to 4) because three months would have the same sort order.

Strange behavior

When playing around with this functionality I found out a strange behavior, a feature or bug? You decide (I consider it a bug). It happened when I didn’t have enough space to use the whole month name and decided to just take the first 3 letters of every month. Sorting this by the month number still works. Not happy the result I decided to change to only the first letter for every month. When I tried to sort by month number I got an error message:

This error message is expected because now the really short month abbreviation of one letter will have multiple sort orders. The letter J (January, June, July) has three sort orders: 1, 6, 7. Trying to use month number to sort the column will result in this error. So far so good.

A strange thing happens when I did the following.

1. Create an abbreviation of three letters using a LEFT(MonthName, 3)
2. Change the sort order to Month number
3. Change the abbreviation to 1 letter by changing LEFT( ,3) to LEFT( ,1).

Now no error is given and the sort order works like a charm! See the table below:

A line chart will also show the correct result.

It would be great if the story ends here but it doesn’t. When I tried to use the same really short month name and data in a bar graph this happened:

As you can see the sort order still work but now the values for several months are not shown. Probably this is due to the fact that the engine can’t resolve the request because of the combination of the letter and the multiple sort orders. I can’t figure out why it works fine with the table. What do you think? Bug or feature?

Solution

And now for a way to make this work. A method I found through google is to create a calculated column (either DAX or Power Query) that includes the desired initial letter of the month, followed by zero-width blank characters (such as Unicode character 8203), repeated a different number of times for each month. Using this will create the desired 1 letter abbreviation you can sort by the month number and which shows all data when used in either a table or bar chart.

Month really short = LEFT( MonthName, 1) & REPT( UNICHAR ( 8203 ), MonthNr) )