Today Microsoft announced that it is now supporting icon sets to be used for conditional formatting in Power BI. This feature has been available in Excel, like forever, and it great to see it available now in Power BI.
Set Up Power BI Conditional Formatting Using Icons
I have a number of measures in my Power BI Workbook to demonstrate the new icons as shown below.
Total Sales = SUM(Sales[ExtendedAmount])
Total Sales Prior Year = CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, YEAR))
Change vs Prior Year = [Total Sales] - [Total Sales Prior Year]
% Change vs Prior Year = DIVIDE([Change vs Prior Year], [Total Sales Prior Year])
The new icon feature is available under the conditional formatting menu. Below I have a table using some of the measures above. To add a Power BI conditional formatting icons set:
- Find the measure in the values section of the visual (a table or matrix)
- Click the menu next to the measure you want to apply conditional formatting.
- Select Conditional Formatting
- Select Icons
The default settings are:
- Format by rules
- Based on the value in the selected measure
- And 3 icons are provided evenly split across the range of numbers returned by the measure.
With the default settings, this is what my table visual looks like
Note how the icons above have both shape and colour so you can differentiate between them even if you are colour blind. This is best practice.
You can also change the default formatting to work on the hard coded number settings that you specify. In the example below I have changed the settings to work on absolute numbers instead of percentages (note the changes in the highlighted boxes). Also note that I have set the minimum and maximum numbers shown as 1 and 2. To do this, simply delete the value in these boxes. Thanks to Chris Webb for finally helping me understand how this works.
This is what the table looks like with the above settings.
Icon Sets for conditional formatting in Power BI
There is a large (but not unlimited) range of standard icons that can be selected from the icon menu, as shown below.
Icon Theme Files
If you are not satisfied with the standard icons, you can add your own icon sets using a theme file.   Here is a sample theme file that increases the default font size for visuals to 15pt (who doesn’t want that). You will need to unzip the file and extract the json document. I have added some sample icon SVG code I got from Microsoft to this file too, and also a static gif file that I found on the internet. After I loaded the theme file, you can see the 3 new icons that have been added (shown below). If you know how to write these SVG icons and/or can find other online icons, you can alter the JSON file to add new icons too.
I wrote about the theme files here if you would like more information.
Icon Measures
The last way (and may be most exciting) to add icons in Power BI is to write a measure. Here is a sample that I got from Microsoft with a few small changes that I made myself.
- Line 4 through 6 is SVG code that will draw a vector graphic as the icon
- Line 7 is a named reference to one of the standard icons
- Line 8 is a gif file that I found on the Internet
You can download the source code for this measure here.
Once you have a measure like this, you can change the conditional formatting as follows
If you know of any good, free icon sets that can be linked online for this purpose, please post the link in the comments below.
SVG code format
I don’t pretend to deeply understand the following code, but I was able to work out how to write an SVG icon using the following format.
This is the place holder/wrapper. Just replace the SVG code in the location indicated
“data:image/svg+xml;utf8, <svg xmlns=’http://www.w3.org/2000/svg’ x=’0px’ y=’0px’ width=’100′ height=’100′ viewBox=’0 0 100 100′> PLACE YOUR SVG CODE HERE </svg>”
Here is some sample SVG code for a yellow circle with a green border . Insert this in the space indicated above.
<circle cx=’50’ cy=’50’ r=’40’ stroke=’green’ stroke-width=’4′ fill=’yellow’ />
I just followed the learning from here https://www.w3schools.com/graphics/svg_intro.asp and used single quotes instead of double quotes.
Note: A few people have reported issues using SVG code when publishing the PBIX online. It seems this is related to the way some browsers interpret the # symbol for colour. You should replace the # symbol with %23 in your SVG code to resolve this, then it should work online.
SVG Editor
This was the best online SVG editor I could find https://editor.method.ac/
base64 code format
You can also encode an image to base64 format and embed that directly in the icon measure. Here is an example.
"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACYAAAAvCAIAAAAAUC8xAAAACXBIWX MAAC4jAAAuIwF4pT92AAACaUlEQVR42u2YP0hbQRzHtYoFBa0WFRGx0k6SgpkkONgtLoEumTp2cn PrVHBxiWOHOHTQIXMlu0RcS6Crb3KyGV2ef+qf9vPytZenxuT9ufeK0B/H4+5y3Od+f+9IT88/lP XRoZPZ8d9zEzHb/tTowkB/d96P6bH4MNM4ehcq+mnp5edPp86hG0NOvm4dv1vsTj2aecmiq48fXE vya3OjC1UqntW/u/bkcm21E1VI17Z0oiaE7ERNDolcFwuivh98nhLytPHzemVZiJauiSJFvXn7Wl UiClLuoZ1Xv4VNG1poJIWC85JOrMdcIRR1DiMiz/f3CAcvKJruCWXeiEj0u1nKmjhMA4lgWC8clr JSNw0kpV+xQCclpAkE/JoSUuFKw7zpGdbLkGIB86YXPmSIIuiispM4EobqDo6kDIFnBnVRmhPwEz Nc9W1tHhFpCrS/mWzBzVrQNphDI9GM46vOoR/K6RXBvGfqYsEM/YeIiGR3L/FXltladeBh2qgYmd bWx4GQuurg4bmLrS+P7WWsyhr8+tgbKhCSLeAB9kprMy7i3JqBkBwZkr5KjMSRUk7hEJMXqxT8Rz 5tZC6Xy2Qy+Xy+XC77h6VSiWG1Ws38Ffp2kNqrUqnQqdfrfGHXajXYUDVfa0qj0bCGZDvD5guGIW CoQlo2rNRC6DiOY5CC6SvhV2tIOU8kg8SqmjSGtamlfzspjYWxKp2kDOtH3gtgefeJ5GX8wh3kKX oHye2YHFLvtBZye3xYY67GsG/wIPp5T5nmJbg7OXKLfPGs130zZfHfrbYNxKv+vtbfBVAPFheS4+ 1l5+/wUpY/NGyhoiw9IhYAAAAASUVORK5CYII="
The pattern to use is to source the base64 code. I used this site https://onlinepngtools.com/convert-png-to-base64 and selected line chunking to create multiple lines
Then I prefixed the code with the following.
data:image/png;base64,
and wrapped the lot in double quotes.
Edit 24th July, 2019:
Reid Havens also has a blog on this topic. You an see that article here.
Where can I find a list of standard Power BI Icons?
Read my blog article here to find a list of Power BI Icon Names.
Like replacing # with %23, how do we replace the character + ?
Hi Matt,
thank you. Is there a way to create a slicer with the icons created?
Thanks,
Leonardo
Not as a slicer. But you could use a table as a slicer instead. Also, it is possible to use some standard icons in table names, but I can’t seem to find a link to relevant articles
Hi! On Icon Measures, how would we write to not apply icon if field value is blank?
how about =if(selectedvalue(table[Column]=blank(),”do something”)
Would it look something like this?
Icons Measure = IF (Engagements[Margin in % Engagements=blank(), “CircleEmpty”, (SWITCH(TRUE(), Engagements[Margin in % Engagements] >= sum(‘FOP-Plan'[FOP Margin Plan]), “TriangleHigh” , Engagements[Margin in % Engagements] >= sum(‘FOP-Plan'[FOP Margin Plan]) * .97 , “TriangleMedium” , Engagements[Margin in % Engagements] < sum('FOP-Plan'[FOP Margin Plan]) * .97 , "TriangleLow")
Thank you Matt, was pulling my hair out at how to change the default % settings 🙂
I’m still new at this and cannot understand the “calendar” part in Step 2. I have followed the steps here and am getting no disparity between sales variances from year to year. How did you get Calendar[Date] in step#2 after DATEADD?
Do you have a calendar table? You need one to write this function. Have a read of my article here https://exceleratorbi.com.au/power-pivot-calendar-tables/
I set this up but in the service, it just displays a big X and says “See Details”. Of course the details offer no help.
Thanks for reporting this – sorry, I didn’t test online. It seems some browsers cannot resolve the # symbol. You need to replace the # with %23. Eg
polygon fill=’#84C28A’
with this
polygon fill=’%2384C28A’
I have updated this in the sample file
Even if we replace %23 also internet explorer 11 is not showing up the conditional formatting icon. But chrome is working fine, What changes do I need to do for this IE11 compatability issue. Any changes to SVG code.
SVG Code: Having issue with internet explorer 11. Icon is not visible. Chrome is working fine.
“icons”: {
,”RedRightArrow”: {“description”: “Red Arrow Right”, “url”: “data:image/svg+xml;utf8, “}
Power BI Report server is not showing up below icon(red color middle direction) in Internet explorer11 but working fine chrome browser. What changes do I need to do to the SVG code to display red color icon in IE11(power bi report server).
{
“name”: “PowerBI.Tips & Havens Consulting Icon Set”,
“icons”: {
,”RedRightArrow”: {“description”: “Red Arrow Right”, “url”: “data:image/svg+xml;utf8, “}
}
}
No Total Conditional formatting functionality still?
Nope. Have you either created an idea for it, or voted for someone else’s idea?
Hi Matt,
Thanks for the post. I actually downloaded the formula made a few changes in it for my purposes and it worked great. Question: In addition to “StarMediumLight” is there a way to get access to all the names of the Standard Icons.
Thanks,
Rob
Hey Rob. I did ask, but didn’t get a response. I will follow up
A list will be in my blog post tomorrow (13th Aug 2019)
Waiting For this post!!!
Hi Matt, awesome blog post – thanks a lot for the detailed instructions!! I’ve successfully incorporated the icons using both the measure approach and through my custom theme. It all works perfectly within the Power BI desktop; however, when I publish to web the icons do not display. I’ve tested this out using your theme as well, and the up and down arrows don’t display (however, the GIF does). Based on this, my guess would be that the SVG syntax is not yet compatible with the Power BI service (as the GIF just links to a URL). Just wondering if you’ve experienced the same issues, and if you have any work arounds? Any help would be appreciated! Cheers, Alice
oooo. I haven’t tested it online. I will have to check this out. Thanks for alerting me to this.,
OK, I spoke to my colleagues and it seems that some browsers do not correctly interpret the # in the SVG code. Please replace the # symbol with %23 in the SVG code and try again.
eg, replace this
polygon fill=’#84C28A’
with this
polygon fill=’%2384C28A’
I have updated the defaults.json doc
Hi Matt,
Under the default settings in your example, any % change less than 33% should show the red icon. The second line in the table visual (Bike Stands) has a % change of 9.2% and has a yellow icon. Based on the rules I believe it should be a red icon. I’m running into a similar issue with my data and I’m wondering if it is a bug or perhaps caused by my measure’s dax code?
Thanks,
Dane
That’s not how it works. Under the default settings, the tool analyses the range of data points. When you look at my data, the smallest value is -26.1% and the largest value is 74.1%. This is the range. This range is then split into 3 sections. 0 to 33% of the range, 34 to 66% of the range, 67 to 100% of the range. In my example, the cut over point from the first sector to the second sector is 6.97, hence 9.4% is in the middle sector.
Thank you for the clarification. Keep up the good work. – Dane
Hi Matt,
I’m trying to replicate a report I have in excel but in Power BI, setting conditional formatting using Icons.
I can’t see the Icon options in my Power BI sessions….any ideas why this may be the case? Does this only work or calculated fields?
Thanks
Paul
No, it works on any numeric column or measure. Do you have the July version of Desktop?
This is a great write-up on the new feature. Thanks Matt!
It’s a great and helpful feature. Thanks for sharing
Matt you are great!
This is a fantastic feature. Thanks for the writeup as always Matt