Conditional Formatting Using Icons In Power BI - Excelerator BI

Conditional Formatting Using Icons In Power BI

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 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 conditional formatting icon set:

  1. Find the measure in the values section of the visual (a table or matrix)
  2. Click the menu next to the measure you want to apply conditional formatting.
  3. Select Conditional Formatting
  4. Select Icons

Conditional Formatting

The default settings are:

  1. Format by rules
  2. Based on the value in the selected measure
  3. And 3 icons are provided evenly split across the range of numbers returned by the measure.

Icons

With the default settings, this is what my table visual looks like

3 Visual

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.

4. Visual

This is what the table looks like with the above settings.

5. Table

Icon Sets

There is a large (but not unlimited) range of standard icons that can be selected from the icon menu, as shown below.

6. Icons

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.

7. Themes

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 is to write a measure.  Here is a sample that I got from Microsoft with a few small changes that I made myself.

8. Measures

  • 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

9. Percent

Icons

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.

20 thoughts on “Conditional Formatting Using Icons In Power BI”

  1. 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.

    1. 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

  2. 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

  3. 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

    1. 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

  4. 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

    1. 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.

  5. 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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top