Opinions

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.

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.

—
Conditional Formatting Using Icons In Power BI
Share this