Apply Conditional Formatting For A Text Column Using Icons In Power BI

Overview

 

In this article, we will talk about how we can apply conditional formatting using OOTB icons for text column in Power BI.

 

Example

 

We have the following data source and the following columns.

• Sales Team
• Project Status
• Profit Status
• Forecast Status
• Budget Status

 

We need the end result to be something like what is shown in the below screenshot.

 

 

We need icons for the different Statuses, as shown in the below table.

 

Status	Icon
Excellent	Green
Average	Yellow
Below Average	Red
No Status	(-) Dash

 

Download Code

 

You can download the sample data source file and Power BI file using GitHub.

 

Now, let’s get started!

 

Step 1 - Load Power BI Data

 

Load the data source in Power BI. Add the Status in the tables. The table will look like below.

 

 

Step 2 - Add Conditional Columns in PowerQuery

 

Now as we mentioned in the above table, we need different Icons for different Status Value.

 

We have Status Value in the Text column. We can’t apply conditional formatting on Text columns in Power BI directly.

 

In order to apply conditional formatting on text columns, we need to create one numeric column which shows different stages in numeric format.

 

Let’s create an additional column which will add numeric value.

1. Click on Edit Queries from Home tab.
   
   
   
   
2. We have different Status columns as showing in the below screen.
   
   
   
   
3. From Add Column, click on Conditional Column.
   
   
   
   
4. It will Open the Pop Up to add the Conditional Column.
   
   
1. Add the New Column Name. We are creating numeric value for the Project Status. So, we have added the column name as “Project Status Rank”.
   
   
   
   
2. Then we will add the conditions shown in below screen.
   
   
   
   
5. In the same way, we will add the conditional columns for Budget Status, Forecast Status, Profit Status.
   
   
   
   
   
   
   
   
6. All the conditional columns have been created.
   
   

Step 3 - Add Conditional Formatting using Icons in Power BI

1. Convert all Status column to the Decimal Number data type.
2. Select individual status column and go to Modelling from the ribbon and select Datatype = Decimal Number.
   
   
   
   
3. Let’s apply Conditional formatting using Icons for Project Status column.
   
   
1. Click on down arrow for Project Status Column and click on Conditional formatting.
2. Click on Icons
   
   
   
   
3. Set the following values as shown in the screenshot.
   
   
   
   
   1. Format by = Rules
   2. Based on field = For Project Status, we created numeric column “ProjectStatusrank”. So, we will set “ProjectStatusRank”.
   3. Summarization = Sum
   4. Icon Layout= We want to set the icons left to the text. So, we will set Left of the data.
   
   
   
   
   5. Icon alignment = Set Icon alignment as per your wish.
   6. Style = Select different styles based on your choice. There are so many OOTB icons.
   7. Add Rules as shown in the below screen.
   8. Click on OK.
   
   The result will look like following screen.
   
   
   
4. Now, apply conditional formatting for other columns as well.
   
   Profit Status
   
   
   
   Forecast Status
   
    
   
   Budget Status
   
   
   
   
5. The end result will look like in the following screen.
   
   

Conclusion

 

This is how we can easily apply conditional formatting using Icons on the text columns in Power BI. I hope you love this article! Stay connected with me for more amazing articles!

 

Happy Reporting!!