Highlighting specific rows in Power BI is a necessity for reporting. While it is an extremely common request, the way to set it up in Power BI can be a bit frustrating, especially if you’re used to working in Microsoft Excel.
We’ll take a look at how to set it up and explain some of the limitations that you should be aware of.
Because Power BI lacks a built-in change color button for highlighting columns, Power developers have to use conditional formatting.
But don’t worry, conditional formatting is super versatile and easy to setup.
Let’s take a look!
Highlighting Individual Rows in Power BI With Conditional Formatting
The first example we are going to look at is a Matrix visual that is configured to show values as rows. While not a necessity, we generally recommend placing values as rows in a Matrix when building out financial reports. It makes it easier to move rows around, change the order, and create subtotal calculations.
It also makes it easier to apply conditional formatting to highlight full rows.
To get started, follow these steps:
Step 1.) Click the down arrow next to the name of the measure or field name that you want to highlight.
Navigate to: Conditional Formatting > Background Color
In the next screen, select Rules, from the Format Style Dropdown Box. It is located in the top right of the page.
Step 2.) Modify the Conditional Formatting Rules so that the values are always going to be selected.
Our favorite method is to change the condition to if the values are >= 0% and <= 100% which ensures that numbers will be highlighted in almost all instances.
You could also set something like > -1 billion and < 1 billion or another number that is outrageously outside of the range of your reporting values.
The result of the selections above are a visual where the values of all columns in the Gross Profit row are highlighted.
Note: To Highlight the Total column along with the values update the “Apply To” selection in the conditional formatting window from “Values Only” to “Values and Totals” There is also an option where you can highlight “Totals Only”
If you’re curious about the other conditional formatting options available, Microsoft has an in-depth guide explaining all of the different use cases for it.
Highlight Individual Rows in Power BI with an Index Column
Another workaround that you can use to tell Power BI to highlight specific cells in a Matrix visual is to use an index column. The method still utilizes Conditional Formatting, but assigns a static value to each row that becomes the basis for the highlighting.
Take a look at the example below.
The columns to the right, Cookie Category and Total Sales are standard Matrix visual columns.
Step 1.) We added a third column on the left called Item ID. This column could be setup by manually entering data into a table, or by importing a separate Excel spreadsheet. The idea is that you have to assign the aggregated category in the Matrix to a specific number that you’ll be able to reference with conditional formatting.
Note: We need to have an index column in the Matrix visual for it to be referenced in conditional formatting. Don’t worry, we will remove it in a later step so users won’t be able to see it.
Step 2.) Assign a conditional formatting rule based on the Sum of the Index Column
You may have to get creative here, but because we have a one to one relationship between our Item ID and categories while only using whole numbers it makes our formula easier.
We chose to conditionally formation based on > an off number and less than or equal to one of our Item ID#s
By using specific criteria, we force Power BI to have only one option of which row to highlight.
The result looks like the following:
Step 3.) Hide the Item ID # Column from our Matrix VIsual
To hide a column in Power BI take the following steps.
- Select the Matrix Visual
- Go to “Format Your Visual” on the Visualizations Pane
- Disable Text Wrap under Column Headers
- Adjust the width of a column by selecting the edge of the column header in the Matrix visual. Drag it to the left to make it smaller to the point that it cannot be seen.
Once Column Headers text wrap has been disabled, you can drag a column to the left to make it smaller. Because text wrap is off it will not make the column headers themselves taller.
This doesn’t fully hide a column from existence, but it will hide it so end users will be unable to see it.
Note: You will need to repeat this step for every column in your Matrix because conditional formatting is assigned at the field level.
Changing the column width is one of our favorite methods for including a column in a Matrix but changing it so that it is invisible to end users.
For even more information about how to set conditional formatting for a column based on values in another column, Patrick from Guy in a Cube does a great job explaining some of the additional considerations of using this feature.
Highlighting Individual Cells in Power BI Matrix Visuals
While there is no direct or easy way to highlight an individual cell within a Power BI Matrix, you can use the technique above to highlight based on an index column and place the column being conditionally highlighted into the middle of a Matrix visual.
While it’s not the most dynamic use of conditional formatting, it may be just good enough to get the job done!
Removing Conditional Formatting
Our final note is how to remove conditional formatting. Once you’ve added conditional formatting to a field in Power BI, you can use the same button to edit it. However, you need to use the drop down menu option below Conditional Formatting to Remove Conditional Formatting.
We don’t know why Microsoft chose to set this up as a separate section, but you can’t delete conditional formatting from the edit and add screen.
Conclusion
To highlight a row in Power BI, use Conditional Formatting. Assign a rule that ensure that all values will be captured between the condition criteria. >0% and <= 100% typically will get the job done.
When possible, try placing values on rows in a Power BI Matrix by adjusting the setting under Format Your Visual. It typically makes working with field values easier.
Another option is to create an index column that assigns a specific value to each row in your Matrix. This column is then used to apply conditional formatting. Turn off column header Text Wrap and shrink the index column to the point that it is no longer visible to create the illusion that the index column doesn’t exist for the end users.