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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The final configuration should look like the diagram below.
So, after clicking “Ok” we can now see our RAG icons on the table visual as configured. See diagram below.
Next Steps
- Check out the 135+ Power BI tips on MSSQLTips.com.
- Try this tip out in your own data as business requires.
