The sort by column feature of Power BI hides some potentially unwanted complexities. This blog post shows how you can avoid having to sort a column by another column while maintaining a custom sort order.

The problem

Here’s a typical example: you sort month names by month numbers. Let’s say you want to remove filters from month name by using ALL or REMOVEFILTERS. To make it work, you’ve got to remove filters from month number too, otherwise the filters will stay.

Some people are very disappointed that Power BI works this way:

Even experienced professionals fall into this trap. For a more detailed description of the problem, see “Side effects of the Sort By Column setting in DAX” by SQLBI.

The solution

Building on my previous blog post, where I sorted duplicate values, I realized that we can prepend zero-width spaces to integrate the sort order.

For example, to create a pre-sorted single-letter day name column, we can use the following code in DAX:

Day Name Abbr Sorted = 
CONCATENATE(
    REPT(UNICHAR(8203), 'Calendar DAX'[Day of Week]),
    LEFT('Calendar DAX'[Day Name], 1)
)

Here’s the corresponding code in M:

Text.Repeat(Character.FromNumber(8203), [Day of Week]) & Text.Start([Day Name], 1)

The only catch is that to sort it correctly, you’ll have to sort it in descending order:

If you’d like to avoid that, you can make a small tweak to the code:

Day Name Abbr Sorted Asc = 
CONCATENATE(
    REPT(UNICHAR(8203), 7 - 'Calendar DAX'[Day of Week]),
    LEFT('Calendar DAX'[Day Name], 1)
)

NB: In this example, 7 is the number of distinct day names, which is known in advance — it’s the maximum Day of Week value.

Conclusions

If you’re going to use this feature, you should be aware that the explain the increase/decrease feature won’t work. Otherwise this may be a good solution especially when you’d like to avoid having extra columns in your model.

Sample file: Built-in column sort order in Power BI.pbix (43 KB)

Happy sorting 🙂

For more great content, check out the Resource Centre

About the Author:

I am a Microsoft BI aficionado who began his career in business and finance, but now lives and breathes all things data: storage, ETL, modelling, analysis and visualization. I am especially passionate about Power BI and its family of products.

Reference:

Maslyuk, D. (2021). Built-in column sort order in Power BI. Available at: https://xxlbi.com/blog/built-in-column-sort-order-power-bi/ [Accessed: 4th March 2021].

Find more great Power Platform content here.