With so many different methods to visualize data – just within Power BI – this can be a daunting choice. Let’s see… there are a plethora of standard visuals that can be used in creative ways. A software developer can create a custom visual if they have the time and chops for that kind of thing. You can use a custom visual generator tool like Charticulator or the Chart designer from PowerBI.Tips. And, you can render unique visuals for statistical analysis using R or Python. The technique I’m going to demonstrate in this series doesn’t require a lot of work or special skills so it is relatively simple and easy to use.
In this first post in a series called “Next Level Power BI”, I demonstrate how to create a simple graphical symbol as a Scalable Vector Graphic – just to get started. In a later post, we will expand the technique to create dynamic databars. The same technique can be used to create icons, sparklines and practically any imaginable graphic can be produced with calculated column or measure.
I’ll start with a simple example. Let’s say that you need to display a set of symbols to correspond to a data condition of some kind. For simplicity, let’s just start with the graphics. Here are examples of three symbols displayed in a table visual on a Power BI report page:
Finding the SVG markup for these symbols can be as simple as a web search. You can also create your own using vector graphic editing tools like Adobe Illustrator. The markup for these three symbols is shown here in Power Query. In different Power BI solutions, I have stored and exported the SVG markup in Excel. It’s just text so you can store it anyway you prefer. All three symbols are defined as a series of spatial points in the path element but you can draw shapes, text and a variety of things. There are several good SVG references on the web but I found this collection from Jakob Jenkov to be particularly useful: http://tutorials.jenkov.com/svg/index.html
In this solution, I just used the Enter Data feature and then copied and pasted the SVG markup directly into the query – one row per image.
We’re not quite done because Power BI requires the following header element to be appended to the beginning of each graphic:
data:image/svg+xml;utf8,<?xml version=”1.0″ encoding=”iso-8859-1″>
You can approach this a few different ways but I added a custom column named Definition and then removed the original column using this expression:
After closing and applying the query, to render the SVG image which exists in the Definition column within a report; it must be categorized as an Image URL. In the report designer, select the column from the Field list and then choose the Data Category on the Modeling ribbon.
The column is now usable as an image in a table or matrix visual, which should appear like the first screen image above.
To create a measure to display only a single image, you can create a measure like this one that returns only one value from the column:
Spreadsheet Symbol =
CALCULATE( MIN( ‘Images'[Definition] ),
‘Images'[Image Name] = “Spreadsheet”
The measure must also be categorized as an Image URL and then it can be used as an individual symbol or image in a report within a table visual.
That covers the bare bones basics of SVG image columns and measures. There is so much that you can do, expanding on this technique by injecting dynamic logic into the measure code. I’ll cover that in a subsequent post.