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.
Figure 1: Project List in Gantt Chart View
Requested Project Status Order:
- Under Construction
- Awarded
- Bid
- Prospect
- Completed
- Cancelled
- Lost
- 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.
Figure 2: Power Query Editor: Conditional Column
Figure 3: Adding Conditional Column Clause
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)
Figure 5: Closing and Applying Changes in Power BI
Figure 6: Opening the Data Table View
Figure 7: Selecting the Project Status Column
Figure 8: Selecting Sort by Column
Figure 9: Selecting the Conditional Column
Figure 10: Advanced Properties
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.
Figure 11b: Checking the Gantt chart sorting
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.
Figure 13: Hierarchy Layout Toggle
Figure 14: Moving the Project Status Column
Figure 15: Gantt Chart with proper sort order
Figure 16: Adding Project Status to Legend field
Note: Subcategories of a project status will have the same conditional bar formatting as the primary category.
Figure 17: Customizing Bar Formatting
Figure 18: Formatted Gantt Chart with Subcategories Expanded
Figure 19: Formatted Gantt Chart with Subcategories Collapsed
Don’t forget to subscribe to our mailing list to get a notification whenever we publish a new blog.
