Column Sorting

[i820017]

Hey Everybody,

I am trying to sort the following matrix differently.

I want the '>1 & <5' column and the '>=5' column to be switched.

I have created a 'SortOrder' column as seen below.

Then, in the data view, I highlighted the 'LabelKey' column and then clicked on 'Sort by Column'. When I sorted by 'SortOrder', I got this error.

I have a few questions.

First, what is the best way to switch the '>1 & <5' column and the '>=5'?

Second, why am I getting the error above? I have used the 'Sort by Column' before, and I have never gotten this error before.

Thanks

Dan 

[Anonymous · Accepted solution]

Hi @i820017 

How did you get SortOrder column in Power Query? I build a sample and add and idex column in Power Query.

And I can sort the LabelKey or LabelDesc column by this Index column. We can see the '>1 & <5' column and the '>=5' column have been switched.

Best Regards,

Rico Zhou

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

[i820017]

Adding that index column worked for me...thanks

[i820017]

I apologize for being unclear. The SortOrder column in DAX is not a calculated column. It is part of a table that I created in the Query Editor in Power BI as seen below.

[AlexisOlson]

Strange. I've never gotten this type of circular reference error with all columns defined in the query editor. Not sure what's going on then.

[AlexisOlson]

The error is likely because you defined SortOrder in DAX so that it's dependent on the label. Thus LabelKey depends on SortOrder depends on LabelKey, a circular reference.

The workaround is to add the sorting column in the query editor instead of as a calculated column in DAX.