/* Google reCAPTCHA to cut down on spam */

Why you should use conditional columns in Power BI

So, you’ve loaded a ton of data into Power BI and created a report to visually represent the key performance indicators (KPIs) you want to measure, but it’s not as well-organized as you’d hoped. Your data is ordered using the primary key field for each item, which doesn’t make sense when looking at a visualization. This post covers how to create conditional columns in Power BI and use them to sort data (KPIs, in this case).

The Process

The example in Figure 1 below is of a list of project timeline data loaded into Power BI. You can see that the Gantt chart items are sorted by project number instead of the status. Let’s walk through the steps of using conditional columns to sort KPI’s in Power BI so the visualization meets our requirements.

Power BI Gantt Chart Example

Figure 1: Project List in Gantt Chart View

For this report, the client requested that the projects shown be sorted in a specific order.

Requested Project Status Order:

  1. Under Construction
  2. Awarded
  3. Bid
  4. Prospect
  5. Completed
  6. Cancelled
  7. Lost
  8. Declined

We’ll start this off by first creating a conditional column inside of the Power Query Editor. We’ll open up Power Query Editor, add a conditional column, then add one new clause for each of the project status options. There are eight project statuses, so we’ll need to add a total of eight clauses to this conditional column.

simple choice field

Figure 2: Power Query Editor: Conditional Column

simple choice field

Figure 3: Adding Conditional Column Clause

Adding a Clause in Power BI

Figure 4: Adding Clauses to a Conditional Column

You’ll need to open up the Power Query advanced editor and insert the below script. Of course, you’ll want to change Project Status to the variable name in your visual that you want to sort to complete set up of this conditional column.

#"GanttChartSort Column" = Table.AddColumn(#"Replaced Value", "GanttChartSorting", each
if [Project Status] = "Under Construction" then 1 else 
if [Project Status] = "Awarded" then 2 else
if [Project Status] = "Bid" then 3 else 
if [Project Status] = "Prospect" then 4 else 
if [Project Status] = "Completed" then 5 else 
if [Project Status] = "Cancelled" then 6 else 
if [Project Status] = "Lost" then 7 else 
if [Project Status] = "Declined" then 8 else 9)
Close the advanced editor and apply your changes once you’ve set up the conditional column.

Figure 5: Closing and Applying Changes in Power BI

Next, open the data table view in Power BI Desktop.

Figure 6: Opening the Data Table View

Select the Project Status column so that the column header is highlighted.

Figure 7: Selecting the Project Status Column

Select “Sort by Column” located on the top toolbar and then select the conditional column that we just created inside of Power Query Editor.

Figure 8: Selecting Sort by Column

Figure 9: Selecting the Conditional Column

The Sort by column options can also be found in the Data Model view under Advanced Properties.

Figure 10: Advanced Properties

Return to the Gantt Chart visual to see if it is correctly sorted using the GanttChartSorting column.
Gantt Chart Sorting View

Figure 11: Checking the Gantt chart sorting

We chose to use the MAQ Gantt Chart visual. We found it had an issue where it would revert back to sorting the Project status in alphabetical order if the GanttChartSorting column wasn’t included. The problem was fixed by simply including the GanttChartSorting column in the Gantt chart.

Gantt Chart Sorting View 2

Figure 11b: Checking the Gantt chart sorting

In comparison, a basic Power BI table or Matrix table keeps the Project Status in the appropriate order without adding the GanttChartSorting column into the table or matrix.
Visual of Project Status Order in Power BI

Figure 12: Example of Power BI Basic Sort

After spending some time testing the different formatting options available with the MAQ Gantt Chart, I came across the Hierarchy layout format setting.

Turning this setting on changes the category section into a Matrix table.

The Hierarchy Layout toggle can be found in the visuals format options under the Category Labels dropdown.

Visual of the Hierarchy Layout Toggle in Power BI

Figure 13: Hierarchy Layout Toggle

Once the hierarchy label is enabled, move the Project Status column to the top of the Categories list to enable it as the top-level Category for your visual.
Changing visual to project status column

Figure 14: Moving the Project Status Column

Project Hierarchy View

Figure 15: Gantt Chart with proper sort order

If you would like each Project Status category to have its own colour assigned to it, you will need to add Project Status to the Legend field.
Changing visual to project status column

Figure 16: Adding Project Status to Legend field

To change colours for each project status in the Gantt chart, you can customize Bar Formatting in the Format Options, or you can choose to keep the default colours assigned by Power BI.

Note: Subcategories of a project status will have the same conditional bar formatting as the primary category.

Customizing Bar Formatting

Figure 17: Customizing Bar Formatting

Final Gantt with Subcategories expanded

Figure 18: Formatted Gantt Chart with Subcategories Expanded

Now we have a great looking Gantt Chart that is property sorted, categorized by Project Status, and includes conditional formatting.
Final Gantt chart with subcategories collapsed

Figure 19: Formatted Gantt Chart with Subcategories Collapsed

Have questions about using conditional formatting in Power BI to sort and display visuals in Power BI, or maybe an example of your own you’d like to share? Leave us a comment below.

Don’t forget to subscribe to our mailing list to get a notification whenever we publish a new blog.

Strengthen your sales team
with productivity solutions from iGlobe and Ant Text.

Strengthen your sales team

with productivity solutions from iGlobe and Ant Text.

Please fill in the form to be contacted about this unique offer.

Thank you. Someone will contact you shortly.