DAX Pattern to Sort Non-Unique Names

[WishAskedSooner]

Hello All,

Have you ever tried to sort a column only to be greeted by this message along with the desire to throw your keyboard across the room? I have countless times.

Perhaps I would have been pacified if the Error simply stated:

"The 'RowName' column has non-unique values, and Power BI doesn't know how to break the ties. Make it unique then this error will go away."

This makes more intuitive sense and points the developer in the better direction than "Sort by another column."

I don't recall the source of this solution, but it involves creating a ranking on the SortID column then using that ranking to append repeating non-printing characters to the Row Name, thereby making a unique calculated column.

UnqName = 
    VAR Ranking = RANKX('Table', [SortID])
    VAR Res = [RowName] & REPT(UNICHAR(8204), Ranking - 1)
RETURN
    Res

However, there are a couple of problems with this pattern:

1. What about the Row Names that are unique? I don't really want to touch those.

2. What if the table is several hundred or thousands of rows? Then, we will be appending hundred/thousands of these non-printing characters. Seems dangerous.

So, I have come up with a DAX pattern that resolves the above issues. To start, let's say we have the following table:

Table = 
   DATATABLE(
      "RowName", STRING,
      "SortID", INTEGER,
      {
         {"LabelA", 1},
         {"LabelB", 2},
         {"LabelC", 3},
         {"LabelD", 4},
         {"LabelB", 5},
         {"LabelB", 6},
         {"LabelA", 7}
      }
   )

If you try to sort RowName on SortID, you will get the dreaded error because LabelA and LabelB are non-unique, as you can easily see. LabelC and LabelD, however, are unique. So, the requirement becomes:

1. Create a Calculated Column that appends repeating non-printing characters for only the non-unique labels.

2. Additionally, if I use the pattern above, it will append six copies (recall it uses Ranking minus 1) of the non-printing character to LabelA with SortID 7. What I would really like is a way to append only as many characters as needed to break ties. For example: if I filter the table by LabelA, I get two rows. What I would like to do is then create an index on this smaller table, then instead of appending six non-printing characters, I only append two. This is the second requirement.

So let's get started.

Click New Column, and call it UnqRowName. The first step is to set up the variables we will need in our DAX code which consist of the current row-context RowName and SortID as these will used later in our DAX statement:

UnqRowName = 
    VAR _RN = 'Table'[RowName]
    VAR _SID = 'Table'[SortID]
RETURN
    _RN

 Now, we need to filter 'Table' by the current [RowName] which we have captured in the _RN variable. We will store the filtered table in a variable as well as it will be re-used throughout our code:

VAR _Filt = FILTER(ALL('Table'[RowName], 'Table'[SortID]), 'Table'[RowName] = _RN)

You'll notice that we have invoked the ALL() function for completeness, but we don't really need it since there is no active filter-context in a calculated column. Furthermore, we have only grabbed the RowName and SortID columns from the Table. If you're actual Table is like mine, there are many more columns than those two, but we want to keep our DAX code as efficient as possible.

Now, if we were to put this into a DAX Query View, we would see that the output is just as expected for LabelA:

DEFINE
  VAR _RN = "LabelA"
  VAR _Filt = FILTER(ALL('Table'[RowName], 'Table'[SortID]), 'Table'[RowName] = _RN)
EVALUATE
  _Filt

So, now we just need to add an Index to the above filtered table. This is fairly straightforward using the ADDCOLUMNS function with RANKX. We will create a new variable for this called _Index:

VAR _Index =
   ADDCOLUMNS(
      _Filt,
      "Rank", RANKX(_Filt, 'Table'[SortID], , ASC)
   )
EVALUATE
   _Index

Perfect! Now, we just have to return the [Rank] of the above table filtered by SortID. This is easily done by wrapping our code with FILTER and SELECTCOLUMNS:

VAR _Index =
   SELECTCOLUMNS(
      FILTER(
         ADDCOLUMNS(
            _Filt,
            "Rank", RANKX(_Filt, 'Table'[SortID], , ASC)
         ),
         'Table'[SortID] = 7
      ),
      [Rank]
   )

We have hard-coded the SortID filter for testing only. With that complete, we can return to the DAX code in our Calculated Column and copy the _Index variable from our DAX query. The only adjustment we need to make is using the _SID variable instead of the value of 7 we hard-coded in the DAX query above for testing.

UnqRowName = 
   VAR _RN = 'Table'[RowName]
   VAR _SID = 'Table'[SortID]
   VAR _Filt = FILTER(ALL('Table'[RowName], 'Table'[SortID]), 'Table'[RowName] = _RN)
   VAR _Index =
      SELECTCOLUMNS(
         FILTER(
            ADDCOLUMNS(
               _Filt,
               "Rank", RANKX(_Filt, 'Table'[SortID], , ASC)
            ),
            'Table'[SortID] = _SID
         ),
         [Rank]
      )

RETURN
   _Index

Now, we are nearly done. As you can see in the UnqRowName column, which is currently returning the _Index variable, all the unique Labels have a value of 1 and the non-unique Labels increment based on the number of non-unique entries for that Label only. Thus LabelA in row 7 has a value of 2 since there are only two instances of LabelA. This is just what we wanted!

The final step is quite simple: just return the RowName with the non-printing character appended and repeating _Index times. We will also add a minus 1 like before. This will leave the unique Row Names untouched and append one less character to the non-unique Row Names satisfying both of our requirements. Here is the Final Solution:

UnqRowName = 
   VAR _RN = 'Table'[RowName]
   VAR _SID = 'Table'[SortID]
   VAR _Filt = FILTER(ALL('Table'[RowName], 'Table'[SortID]), 'Table'[RowName] = _RN)
   VAR _Index =
      SELECTCOLUMNS(
         FILTER(
            ADDCOLUMNS(
               _Filt,
               "Rank", RANKX(_Filt, 'Table'[SortID], , ASC)
            ),
            'Table'[SortID] = _SID
         ),
         [Rank]
      )

RETURN
    _RN & REPT(UNICHAR(8204), _Index - 1)

Now, we can sort the UnqRowName column by the SortID column, and no error is returned. And when we use the UnqRowName column in visuals it will be sorted per the SortID column. Hooray! We are done!

I hope you found this pattern helpful. While it is surprisingly simple, it took me many hours of scratching my head, testing, debugging, etc. to work it out. However, I feel it was worth it, not just for the efficiency gained in my Data Model, but for the sharpening of my DAX skills as well.

Thanks for reading. Feel free to comment, and I look forward to your feedback!

[Anonymous · Accepted solution]

Thank you danextian 

Hi, @WishAskedSooner 

The only sorting column is indeed what Power BI currently requires. The solution you shared is very useful.
You can reply here and mark it as a solution so that others in the community can quickly find the solution when they encounter similar needs.
Thank you again for sharing, your solution is really great, and it will promote the development of the community together with us.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @WishAskedSooner 

Nice work!

The GUI doesn't allow more than one sort value for every distinct value in a column being sorted but, alternatively, you can also force such behavior by using use the Sort by Column property in Tabular Editor.

Below is a sample DAX query returned by performance analyzer when a column custom sorted is added to a viz. The  Index/Sort column isn't visible in the viz but is added to the DAX query as well.

[WishAskedSooner]

Hi @danextian,

Thank you for your reply! I am not familiar with Tabular Editor unfortunately. In fact, I barely know my way around in DAX and the DAX Query View.

But, you are right. The problem with sorting one column by another is the cardinality/granularity must match, and there are of two approaches:

1. Update the RowName column to match the granularity of SortID

2. Update SortID to match the granularity of RowName

The first one is the pattern I outlined. The second one involves using a sort column with non-unique values which is possible as long as the sort column has matching non-unique values, but they must match precisely. For example, in the table below, RowName can be sorted on SortID because their granularity match:

However, there is a weird bug in PowerBI which can confuse developers. If you tried to sort on RowName previously and got the error, updating the granularity of SortID, and trying to reapply the sort will still generate the error. This is misleading because if you create a brand new table of the above with the matching granularity, you won't get an error.

Finally, I know that the pattern I outlined can also be executed in PowerQuery.

So ultimately, there are many options and which one to deploy depends on one's requirements.

[danextian]

Tabular editor is an one of the external tools for Power BI. There are  properties in there that are not found in the GUI including why when creating a numeric parameter, you get to have a slider slicer that allows just a single value. Most, it not all, of these tools can be downloaded using the Business Ops app. https://community.fabric.microsoft.com/t5/Community-Blog/Business-Ops-The-largest-collection-of-Powe...