Introduction

In the world of data visualization, presenting data in a way that’s both intuitive and insightful is key. Power BI, Microsoft’s interactive data visualization tool, offers extensive capabilities for data analysis and reporting. However, sometimes we encounter scenarios where the default sorting options don’t meet our needs. That’s where custom sorting comes into play. In this blog post, I’ll share how I sorted a column in a Power BI visualization based on a custom order by creating a new column with Power Query and sorting the target column based on the created order column.

Understand the Need for Custom Sorting

Before we dive into the solution, let’s discuss why we sometimes need to sort data in a way that isn’t automatically available in Power BI. Imagine you’re creating a histogram to show different income levels in Power BI. These income levels are listed in a column named “Attribute,” ranging from “Under $10,000 (including loss)” to “$150,000 and over.”

In Power BI, you can sort columns in two main ways: by the sum of their values or by the attribute names themselves. Sorting by the sum organizes them based on the total amount in each category, and sorting by attribute names arranges them alphabetically. Neither of these methods sorts the income levels in the order they are listed, which is how we logically understand income ranges to progress.

The figures below show what happens when you sort by each method:

However, we want to display these income levels in their listed order, from the lowest to the highest range. The default sorting options in Power BI don’t allow for this. In the next sections, we’ll go over how to achieve the desired custom sorting to present our data more logically.

Step 1: Create the “Sort Order” Column and Understand the Cyclic Reference Error

When implementing custom sorting in Power BI through DAX by adding a new column, you might encounter an error stating, “A cyclic reference was encountered during evaluation.” This error is a direct consequence of how DAX formulas are evaluated within Power BI’s data model, especially when attempting to sort a column based on another column that you’ve just created.

The Root of the Cyclic Reference Error

The cyclic reference error surfaces because the DAX engine in Power BI is designed to handle data in a relational way, evaluating expressions based on existing data in the model. When you define a new column using DAX that depends on the value of another column—which, in turn, might depend on the newly created column or is involved in a calculation chain leading back to the original column—a loop is created. This loop, or “cyclic reference,” cannot be resolved by Power BI because it implies that the value of a column depends on its own calculation, creating a paradox that the system cannot compute.

For example, using DAX to create a new column for sorting purposes based on the “Attribute” column, and then attempting to sort the “Attribute” column by this new “Sort Order” column, introduces a dependency loop. Power BI’s evaluation engine cannot process this because it’s akin to saying, “To know A, first tell me B, but to know B, I need to know A,” leading to an infinite loop without a starting point.

Power Query as a Solution

To circumvent this issue, we employ Power Query for pre-loading data transformations. Power Query operates linearly, allowing for step-by-step data manipulation that avoids the creation of dependency loops. When we use Power Query to add a new column for sorting, we effectively sidestep the cyclic reference problem by establishing a clear, unidirectional flow of data transformations.

Steps to Create the “Sort Order” Column in Power Query

1. Open Power Query Editor: In Power BI, go to the “Home” tab and select “Transform Data” to open the Power Query Editor.
2. Locate Your Table: In the Power Query Editor, find and click on the table you need to modify.
3. Add a Custom Column: Navigate to the “Add Column” tab and choose “Custom Column.” This is where you will define your new sort order.
4. Input the M Code for the Custom Column: In the formula space provided, enter the M code to create your “Sort Order” column. This code assigns a numerical value to each category or attribute in your data based on the desired sorting order. Here’s an example structure for the M code:

= if [Attribute] = "Under $10,000 (including loss)" then 1
else if [Attribute] = "$10,000 to $19,999" then 2
else if [Attribute] = "$20,000 to $29,999" then 3
else if [Attribute] = "$30,000 to $39,999" then 4
else if [Attribute] = "$40,000 to $49,999" then 5
else if [Attribute] = "$50,000 to $59,999" then 6
else if [Attribute] = "$60,000 to $69,999" then 7
else if [Attribute] = "$70,000 to $79,999" then 8
else if [Attribute] = "$80,000 to $89,999" then 9
else if [Attribute] = "$90,000 to $99,999" then 10
else if [Attribute] = "$100,000 to $149,999" then 11
else if [Attribute] = "$150,000 and over" then 12
else 13

After entering your code, click “OK” to add the column, then apply your changes and close the Power Query Editor to update your dataset in Power BI.

By defining the “Sort Order” column in Power Query before the data is loaded into the model, we ensure that each income bracket is assigned a specific order without creating a feedback loop. This method guarantees that when we sort the “Attribute” column using the newly created “Sort Order,” Power BI can process this request smoothly, free from the limitations of cyclic dependencies inherent in DAX within the data model context.

Step 2: Sorting the Target Column Based on the Created “Sort Order” Column

Once the index column is in place, the next step is to sort our target column based on this newly created order.

How to Apply the Custom Sort

1. Go back to the report view and select the visual that contains the column you wish to sort.
2. In the Fields pane, find the target column (In our case, “Attitude”) and select it.
3. In the “Column Tools” tab, select “Sort by column” and choose the index column you created as the column to sort by (In our case, “Sort Order”.)

Now, when you set the chart to sort the axis based on the “Attribute” column, it will display it in the correct way:

Conclusion

Custom sorting in Power BI allows for more flexibility in how data is presented, making reports and dashboards more intuitive and insightful. By following the steps outlined above, you can sort data in Power BI based on any custom order that suits your reporting needs.