Built-in column sort order in Power BI

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.

Now the day names are sorted correctly in ascending order without sorting by another column:

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 🙂

  • Ricardo Rincón

    Great technique Daniil, a few months ago inspired by the article https://www.sqlbi.com/articles/sorting-duplicated-names-in-a-level-of-a-hierarchy-with-dax/ build something similar with DAX but to order numeric metrics in text format, what do you think?

    define
    var number = 15 // number to prepare for sort.
    var lenNumber = LEN(INT(number)) // Get the number of digits of the integer part of the number (can be done in any other way)
    var numberFormat = FORMAT(number, “0.00;-0.00;0.00”) // format the number as you want to see it
    var MaxDigits= 20 // digits of the largest possible number (or just 20)
    var preSpaces= REPT( UNICHAR(8203) , MaxDigits – lenNumber ) // Concatenate blanks to the number until you reach Maximum Digits
    var retorno = preSpaces & numberFormat
    evaluate
    {
    retorno
    }

    • Thanks for sharing, Ricardo! I can see the code already produces good results, though not always:
      DEFINE
      VAR SampleNumbers = { "-1", "5", "10" }
      VAR Test =
      ADDCOLUMNS (
      SampleNumbers,
      "Test",
      VAR number =
      VALUE ( [Value] ) // number to prepare for sort.
      VAR lenNumber =
      LEN ( INT ( number ) ) // Get the number of digits of the integer part of the number (can be done in any other way)
      VAR numberFormat =
      FORMAT ( number, "0.00;-0.00;0.00" ) // format the number as you want to see it
      VAR MaxDigits = 20 // digits of the largest possible number (or just 20)
      VAR preSpaces =
      REPT ( UNICHAR ( 8203 ), MaxDigits - lenNumber ) // Concatenate blanks to the number until you reach Maximum Digits
      VAR retorno = preSpaces & numberFormat
      RETURN
      retorno
      )
      EVALUATE
      Test
      ORDER BY [Test] ASC
      Here’s an alternative solution I would consider:
      DEFINE
      VAR SampleNumbersAsText = { "-1", "5", "10" }
      VAR SampleNumbers = SELECTCOLUMNS(SampleNumbersAsText, "Value", VALUE([Value]))
      VAR MaxLen = MAXX(SampleNumbersAsText, LEN([Value]))
      VAR NumberCount = COUNTROWS(DISTINCT(SampleNumbersAsText))
      VAR Test =
      ADDCOLUMNS (
      SampleNumbers,
      "Test",
      VAR NumberRank =
      RANKX(SampleNumbers, [Value], , ASC)
      VAR numberFormat =
      FORMAT ( [Value], "0.00;-0.00;0.00" )
      VAR preSpaces =
      REPT ( UNICHAR ( 8203 ), NumberCount - NumberRank )
      VAR retorno = preSpaces & numberFormat
      RETURN
      retorno
      )
      EVALUATE
      Test
      ORDER BY [Test] ASC

      • Ricardo Rincón

        Daniil, thanks!!!, that was really fast, you are great.