I have previously written about how to sort a column by another column, and I used Month Names as an example. However, still, many are unaware that the same technique with slight modifications can be applied to any other columns. You can have a text column in your slicer (product category for example), and sort it based on a different order than the normal alphabetical order. In this post, I am going to show you how to do a custom sort order for a column in Power BI.
Sample Visuals
In the below example, I have visuals that are sorted by the EnglishEducation alphabetically. In Power BI, when you sort a column, if the column is text, it will be sorted alphabetically;
Most of the time, with the text fields, you want the sorting to be alphabetical. However, there are scenarios in every business that some categories are more important than the others, and you might want them to come up first. for example, what if you want the sorting to be: High School, Bachelors, Partial College, Graduate Degree, and the at the end Partial High School? This is a customized ordering. The good news is that this is possible. Let me explain how?
Sort Order Value Cardinality
You can set a column to be sorted by another column in Power BI, you can learn about that as an example here. However, there is a requirement for that; If you are sorting a column by another column, both columns should have the same cardinality. What does that mean? Let’s see.
Below is a table with all EnglishEducation values and their custom sort order:
This is a fine table, because we do not have two different sort order values per unique EnglishEducation. But now imagine something like below
This table has repeated values in the EnglishEducation column (which is absolutely fine), but then the Sort Order for each of those values is not unique. The highlighted section above shows that the Bachelors somewhere mentioned with the Sort Order 3, and somewhere with Sort Order 2. This is wrong! If you wanted to sort this column yourself manually, what would that mean for you? should Bachelors goes 2nd in the list or 3rd?! It is confusing and incorrect. So having a sort order column like that is not going to work. You need to have them both at the same cardinality.
The Column to be sorted, and the sort order column MUST HAVE the same cardinality.
Sort Order Table
Having a reference table, that you have one value per row for EnglishEducation is perfect because then it is easier to set up a sort order and make sure it is not repetitive. Like the below example:
However, sometimes, the column that we want to sort is not the key column or the unique column in the existing table. for example, in the Customer table, the key column is CustomerKey, and everything else might be repeated;
Adding a sort order column in this table can be hard, especially if the number of unique values in the EnglishEducation column is high. If the values are low, you might end up writing a conditional column in Power Query, or a conditional statement in DAX, and come up with a custom sort order value based on the value. but let’s assume we have too many values. In those cases, creating a Sort Order Table is a very good solution.
A sort order table is a table that has a row per each value in the column that we want to be sorted (let’s call it; label column), and the sort order of that value (a numeric column most preferably, we can call it sort order column).
Below is an example of a Sort Order Table for EnglishEducation:
Where should you create the Sort Order Table?
The sort order table can be created anywhere, even in Power BI, using the option for the Enter Data:
This is an easy option to set up a sort order table very fast. However, I do not recommend it in production implementations. The reason is that whenever you want to go and edit a sort order (even after months of publishing the report into production), you have to open the PBIX file, apply the change, and then publish to the website again, and if you have Power BI Apps on top of it, you also need to update that!
I do recommend creating and storing the Sort Order Table in a proper data storage system that can be accessed through the Power BI report and be refreshed (either through a gateway or without it).
I suggest things such as SQL Server database table, Excel file, CDS Entity, etc for keeping the sort order table.
Combining the Sort Order Table with the Data Table
After creating the sort order table, the next step is to get data from it (my table was in an Excel file in OneDrive for Business, but you can keep it in any other sources you like)
This table now can be combined into your data table (the table that has the label column in it). This is possible using the Merge Queries option;
The merge can be done based on the field that is the label column: EnglishEduction in my example
And after the Merge, the sort order column can be expanded from the underlying table;
This then brings the sort order into this table as another column:
You then can disable the load of the Sort order table (because this table’s data is now part of the main data table and not needed anymore);
Now you can load the data into Power BI using Close & Apply.
Sort By Column
The last step is to do the sort by column in Power BI. This action can be done in the Model tab, or Data tab, or even Report tab of Power BI, Here is how we can do it in the Data tab;
After this action, you can see that the sorting in all visuals changed respectively:
The beauty of this approach is that, whenever you decide to change the sort order, you can go and edit the Sort Order table, and the next refresh of Power BI will pick the new sort order and present the data respective to that new order.
What if the Label Column is a DAX Calculated Column?
Sometimes, the label column is a DAX calculated column, In those cases, you cannot use Merge, because that column does not yet exists at the Power Query stage. In those scenarios, you can just load the sort order table as is, then create the relationship to the main data table using that.
For example; EnglishEducation (groups) is a field that is generated in Power BI using the grouping in Power BI, and Power Query is not aware of that. in that case, I can have a table like below;
Because Power Query cannot recognize the EnglishEduction (groups) column, you can bring the sort order table into the model, and create a relationship to the sort order table using the Label Column.
Now using the RELATED function in DAX, you can create a calculated column inside the main data table that fetches the Sort Order column;
You can now hide the Sort Order table, as it won’t be needed in the report view;
In the end, when you have the sort order column in the same table as the label column, you can do the sort by column easily;
And the result is all sorted as you can see in the below screenshot:
Summary
In Power BI, You can set any custom order you want. the trick is to do these steps:
- Create a sort order table with a sort order column that has the same cardinality as the label column.
- Combine that table with the rest of the model (Using Power Query and Merge, or The relationship and calculated column)
- Sort the label column by the sort order column
- Make sure the sort order table is stored somewhere that can be edited without opening the PBIX file easily, and the Power BI file can be refreshed automatically based on that.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.