Are you struggling to sort Power BI columns with another column? If so, you’re not alone. Several users reported they are getting errors while using the Sort by Column feature.
After encountering the same issue, I dived deep into this matter and found some reasons and effective solutions to resolve it.
In this article, I will address the primary reasons and demonstrate five methods to fix the Sort by Column not working issue.
Without further discussion, let’s begin!
Why is Sort by Column Not Working in Power BI?
Primarily, the Sort by Column didn’t work due to unsupported data type, inaccurate relationship between the tables, and duplicate values in the same column. Besides, if you select the column in the wrong order, Power BI will show an error and fail to sort the column by another column.
When you sort a text column by numeric values, Power BI cannot sort because of inaccurate data type. To understand more, consider the subsequent discussions.
Here are the reasons why the Sort by Column does not work:
Unsupported Data Type
Not all data types can be sorted in Power BI. You must use a data type compatible with Power BI sorting to sort a column.
For instance, Power BI won’t let you sort a text column based on numerical values. Moreover, if you try to sort a hidden column, you will get an error because Power BI cannot sort hidden columns.
Inaccurate Relationship
If you’re using multiple tables and creating several relationships between them, you will encounter the Sort by Column not functioning error because of inaccurate relationships.
Duplicate Values in the Column
Another common reason behind the Sort by Column not working error is duplicate values in the same column. When a column contains duplicate values, Power BI fails to sort them based on the cell values.
Wrong Column is Selected
If you’re trying to sort a column by another column, there is a great chance that you’ve selected the wrong column, and that’s leading to this annoying error.
Whatever is causing this issue can be fixed with simple and easy-to-follow methods. Move down to the next section to solve the Sort by Column not working problem of Power BI.
How to Fix Power BI Sort by Column Not Working
To fix the Sort by Column not working issue of Microsoft Power BI, clear the unnecessary sort, ensure to select the correct column, and use compatible data type. Moreover, you can use a number column to sort text columns and sort columns without any sort order.
The following options may vary depending on your system. However, by performing subsequent methods, you can effortlessly resolve the Sort by Column not working error of Power BI.
Here are the methods to fix the Sort by Column not working in Power BI:
1. Clear the Sort
Let’s start the fixing by clearing the unnecessary sorts from the affected column. This method is helpful if the Sort by Column option is grayed out.
To fix the issue, click on the column with a sort and select Clear Sort. It will immediately remove the sort. Now click on another column and revert to the first column. When you select the column again, you will no longer see the grayed-out Sort by Column option.
2. Select the Correct Column
The Sort by Column feature won’t work unless you select the correct column. So, ensure you’ve selected the appropriate column to sort by.
For instance, if you want to sort by Month Number column, first select the Month column and then sort it by Month Number.
3. Ensure the Data Type is Compatible
You won’t be able to use the Sort by Column feature unless the column data is compatible with sorting. Ensure the column contains a supported data type for sorting.
Let me clarify with an example. Suppose you’re trying to sort a text column. In that case, you cannot sort it based on numerical values.
To check the data type, right-click the column and choose Data Type. If the data type doesn’t match, convert it to a suitable or compatible format.
4. Use a Number Column to Sort by Column
If you sort a text column by another column with numbers, you will get an error displaying the “We can’t sort the Month column by Month Number” message.
Fortunately, you can effortlessly resolve the issue by following the subsequent instructions.
- Click on Month Number to bring it into the visualization.
- Select the Month column under the Fields tab.
- Click Sort by Column from the Column tools tab.
- Select Month Number from the dropdown menu.
It will instantly sort the Month column by the Month Number column. In case you don’t want to show the Month Number column in the visuals, you can uncheck the Month Number from the Data Table.
5. Sort Column that hasn’t a Sort Order
If you’re using a switch statement to sort a column based on the number column, you will probably get an error saying, “Error returned: A circular dependency was detected.”
In such scenarios, you cannot call a column value because of circular dependency. However, there is a workaround to resolve this issue.
Here’s how to sort column that hasn’t a sort order:
- Go to the Home tab and select Enter data.
- Create a new table. Add two rows, one for the category and another for the item order.
- Rename the table and click Load to insert the new table data.
- Go to the Model View.
- Click on Manage relationship and select New.
- Create a new relationship between the newly created table and the main table.
- Return to the Table view.
- Right-click on the main table and select the New column.
- Type the following syntax:
=RELATED(table[column]) - Hit Enter.
Now, return to the Report view, and you will be able to sort the column by another column without any issue.
Wrap Up
If the Sort by Column option is grayed out, clear the sort from that column. In case the option is visible and shows you an error while sorting, use the supported data type and select the columns sequentially.
Are you still getting errors? Then, use a number column to sort your text column or sort the column with another table using the RELATED syntax.
If you’ve further queries, don’t hesitate to comment below.
