Power BI RAG Icons Custom Conditional Formatting


By:   |   Updated: 2020-08-28   |   Comments   |   Related: More > Power BI


Problem

Power BI desktop comes with some default icons styles which can be used in conditional formatting tasks. However, there are certain project requirements which might require us to use some custom icons not available by default in Power BI.

In July 2019, the Power BI team came up with a new feature known as "Icon Sets" which allows users to be able to create custom icons that fits their project requirements. But how we can create a custom visual with the RAG symbols (Red, Amber, Green Symbols) and use within Power BI conditional formatting?  This article will address this need.

Solution

To demonstrate how this can be achieved, I will be using the Products table of the Northwind database as the data source and walk through each step in the process.

Step 1: Connect the data source with Power BI Desktop

The SQL Server database needs to be connected to Power BI desktop as shown in the diagrams below.

How to get data from SQL Server source to Power BI

You can enter the server name, the database name and optionally write out the SQL queries you would want to use. In this tip I have imported the table directly into Power BI desktop as shown in the diagram below.

Importing Products table to Power BI desktop.

The diagrams below shows what we are trying to achieve here. We are trying to show an icon with the R withing a Red circle, an A within an Amber circle, and a G within a Green circle to represent where data points on the UnitsInStock field is less than 33, between 33 and 67, and above 67, respectively.

Table visual showing Unitstock values

However, the default icon styles already in Power BI desktop do not support this requirement, at least currently, as can be seen in the diagram below.

Snapshot showing default Icon Styles in Power BI desktop

Step 2: Download the icons

Since we do not have the icons we need, we need to download the icons externally to Power BI desktop. I would recommend going to icons8.com. Type on the search bar "r inside circle", this should now display some "R inside circle icons" of which you just need to select the one that matches your requirement. In this demo, I have selected the one with the dark background as seen in the diagram below, and then I selected "Recolor" and changed the background color from black to red.

Snapshot showing how to customise icons in icons8.com 1

Then select "Done" and click "Download" to download the icon to a local folder. Do this for the other icons too i.e. A (amber) and G (Green) by searching them on the icons8 site and changing their background colors and downloading them too.

Snapshot showing how to customise icons in icons8.com 2

Step 3: Convert the icons to Base 64 using Base 64 encoder

To convert the icons downloaded to a base 64 we can search for "base64 image" on google and do this with this website, which is quite easy to use. All you need to do is drag the downloaded files into the section created on the site, as seen in the diagram below, or click on the section created to navigate to the files. Then click on the "show code" option for each converted file to copy the codes.

Snapshot showing how to convert images to base64.
Snapshot showing how to copy base64 code.

Do the above for all downloaded files, then paste the copied code to a text document. I would recommend pasting in Notepad ++ as you would be writing out the json code shortly.

Step 4: Convert the icons codes to json files

To make this part easier, you donít have to know how to code json or any background in writing code. I have provided the basic structure of the code below; all you need do is paste the codes you copied from each base 64 icon files into the section that states "put your red icon base 64 code here".

{
"name": "RAGIcons",
"icons": {
	"RedIcon": {"description": "Red", "url": "PUT YOUR RED ICON BASE 64 CODE HERE"}
	,"AmberIcon": {"description": "Amber", "url": "PUT YOUR AMBER ICON BASE 64 CODE HERE"}
	,"GreenIcon": {"description": "Green", "url": "PUT YOUR GREEN ICON BASE 64 CODE HERE"}
	}
}

Then save the code as a JSON file as shown in the diagram below.

Snapshot showing how to save converted icons as json format.

Step 5: Import saved JSON to Power BI themes

After saving the JSON file, it is now time to import this as a theme into Power BI desktop. By the way, the JSON file when saved should look like the diagram below, else it would not be successfully imported into Power BI.

Snapshot showing how the json file should look like after saving.

To import the file into Power BI, click on "View" tab and select the drop down beside themes and then select "Browse for themes" as shown in the diagram below.

Snapshot showing how to import created json as themes in Power BI desktop.

Then navigate to the location where the JSON file was saved earlier to import it. You should now be able to see a message for successful import as shown below.

Snapshot showing confirmation of imported json file.

Step 6: Configure the conditional formatting as required

Going back to the purpose of the task, we can now configure the conditional formatting with our new custom RAG icons. Now we can see the Icon styles of Power BI desktop for conditional formatting. See diagrams below.

Snapshot showing how to navigate to setup conditional formatting.
Snapshot showing newly created custom icon styles in icon sets.

The final configuration should look like the diagram below.

Snapshot showing the new custom RAG icon styles used in the conditional formatting.

So, after clicking "Ok" we can now see our RAG icons on the table visual as configured. See diagram below.

Snapshot showing final work of the RAG icons configuration.
Next Steps
  • Check out the 135+ Power BI tips on MSSQLTips.com.
  • Try this tip out in your own data as business requires.


Last Updated: 2020-08-28


get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Data Analytics and BI professional mostly in Microsoft BI stack of tools. He is a BI Consultant with Avanade.

View all my tips





Comments For This Article





download





Recommended Reading

Power BI Histogram Example using DAX

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

Power BI Conditional Formatting for Matrix and Table Visuals

Deploy Reports from Development to Test to Production using the Power BI Deployment Pipelines








get free sql tips
agree to terms


Learn more about SQL Server tools