Power BI RAG Icons Custom Conditional Formatting

By:   |   Comments (4)   |   Related: > Power BI Formatting


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.

Learn more about Power BI in this 3 hour training course.


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, October 12, 2021 - 2:44:56 AM - Suzan van Echtelt Back To Top (89325)
This is great, thank you so much.

Thursday, May 6, 2021 - 6:39:16 PM - James Back To Top (88655)
Great tip! Great instructions. Thank you!

Thursday, January 7, 2021 - 11:08:40 AM - Aigbe Kenneth Omorodion Back To Top (88016)
Hi John, although am not 100% sure why the errors might have occurred at your side, but my first suspicion would be that there might be something wrong with your JSON code while you might have been editing it. That is where the whole trick its. However, if you can provide me withy more details of the issues I should be able to help on this. Do not forget that just because the JSON code was saved successfully does not mean it was written accurately. Thanks!

Wednesday, January 6, 2021 - 8:52:13 PM - John Back To Top (88012)
Followed your instructions. Created the json theme file, imported it without a problem, but the icon images show as broken links. Any thoughts as to why that is?














get free sql tips
agree to terms