Next Level Power BI – SVG Databars

Power BI table and matrix visuals can render SVG object elements in a column or measure to display in-line web content. You can build dynamic content like KPI indicators, databars and sparklines by altering SVG element content using DAX expressions. In this post, I will show you how to create databars that show negative and positive values with different color fills based on category values. Of course, Power BI has built-in databars in the conditional formatting options for numeric columns but you cannot alter the color and other attributes dynamically – at least not yet. For the time being, this technique provides more flexibility than the built-in databar feature.

Read This First

In this post from earlier this week, I introduced a simple technique to produce symbols and graphics using columns or a measure to render SVG images in a table visual. I recommend that you read that post to understand the simple implementation of this technique.

Dynamic Databars

Instead of a graphic symbol, the databar really just consists of a rectangle that has a variable start and end position. I want to change the fill color layer based on whether the value is negative or positive, and based on a category value in my data. You could apply any imaginable business logic to change the color or other attributes. I also want to layer formatted text over the top of the databar rectangle. Check out the example below showing a series of records. My simplified demo dataset causes the same value to repeat across the four Flintstones characters but this Databar measure could be applied to any set of data. Notice that negative values are red and the bar “starts” at the center and is proportionally longer as it extends to the left of center for larger negative values. The same is true for positive values that start in the center of the cell and move to the right.

Before we examine the code to generate the dynamic databar, let’s look at the rendered SVG element for a couple of these cells. If I hover over the cell for ID 1 and “Barney”, the SVG definition is displayed in the tooltip.

One fun thing about writing this blog post and showing you fragments of HTML script is that it is a little challenging to copy and paste the script into this post without it being interpreted and rendered as a graphic. This is one reason I’ve elected to use screen capture images.

Examine the above script and you will see the “<rect” element opening tag followed by attributes that specify the x axis, y axis and height values. These attributes draw the rectangle starting at 79 pixels from the left-most edge of the viewBox (left side of the cell), 80 pixels tall and 181 pixels wide. Keep in mind that the graphic does get rescaled with the table so these are only relative measurements. The fill property of the style attribute fills the rectangle with the color Red.

There is also a “<text” element with similar attributes that place centered (anchored to the middle of the viewBox) text at the mid-point of the viewBox width of 300 pixels. For simplicity, there are a few attributes I’m not mentioning but it’s fairly simple to recognize them and figure out what’s going on.

Hovering over the cell for ID 9 and “Barney” with the value 1495, the x and y axis values cause a DodgerBlue filled rectangle to start at 260 pixels and to be 236 pixels wide.

Show Me the DAX

The DAX code for the DataBar measure is below. The actual expression code is fairly simple but it took a little trail-and-error to work-out the measurements and scaling logic. Rather than building the expression in-line, I’ve invested work up-front to put all the values into variables. First, we get the minimum value for all the selected ID rows (using the MINX iterator function with the ALLSELECTED filtering function) and store that in the MinValue variable. The MaxValue variable is similar with the added condition to handle negative or positive values. This may all seem a bit verbose but its really just math.

There is a disconnected table in my sample data model named Flintstone Characters with character names that I place onto the Columns axis of the matrix visual. In a real-world solution, this could be a field like product categories, geographic regions or anything else. The BarFill variable is set to a color value (which could be either a web color name or Hexadecimal value) based on the selected character name. When grouping a visual on a value, the SELECTEDVALUE function will return a single value. After resolving all the variable values, this measure emits the SVG markup similar to the examples shown previously.

DataBar =
VAR MinValue = MINX(ALLSELECTED(‘Values'[ID]), calculate(SUM(‘Values'[Value])))
VAR MaxValue = MAXX(ALLSELECTED(‘Values'[ID]), calculate(SUM(‘Values'[Value]))) + IF(MinValue<0, ABS(MinValue), 0)
VAR BarValue = SUM(‘Values'[Value])
VAR FormattedValue = FORMAT( BarValue, “#,##0” ) –“#,##0.00” –“#,##0.0 %”
VAR svgWidth = 600
VAR svgHeight = 200
VAR svgFontStyle = “normal 70px sans-serif”
VAR BarHeight = 80
Var BarRange = ABS(MinValue) + MaxValue
VAR BarValueFactor = DIVIDE(svgWidth, BarRange)
VAR BarWidth = INT( ABS(BarValue) * BarValueFactor )
VAR BarZero = INT( DIVIDE(ABS(MinValue), MaxValue) * svgWidth )
VAR BarStart = IF(BarValue < 0, BarZero – BarWidth, BarZero)
VAR BarOpacity = “0.5”
VAR BarFill = IF( BarValue < 0, “Red”,
SWITCH( SELECTEDVALUE(‘Flintstone Characters'[Character]),
“Fred”, “DarkOrange”,
“Wilma”, “Purple”,
“Barney”, “DodgerBlue”,
“Betty”, “DarkGreen”
)
)
VAR svg_start = “data:image/svg+xml;utf8,” VAR svg_end = “”
VAR svg_rect = “”
VAR svg_text = “” & FormattedValue & “”
// x=(half of width width when text is centered)
RETURN
svg_start & svg_rect & svg_text & svg_end

download this code

Like in the Symbols example in the earlier post, you must select the measure and set the Data Category in the Modeling ribbon to Image URL. After that, the measure can be dropped into a matrix visual grouped on the ID and Character fields, to produce the databar visuals I showed in the first example above.

It Takes a Village

A few experts in the community have created SVG sample projects with various techniques. David Eversveld has blogged about generating SVG images in this series. When I started working out the code for the DataBar technique, I reached out to some friends in the MVP community and Darren Gosbell was kind enough to help-out. In the process Darren discovered a bug (or an undesired behavior) in Power BI that ignores the aspect ratio directive in the SVG element and draws a square image even when you don’t want it to. He posted a suggestion the Power BI Ideas forum and I encourage you to go vote to help get this fix prioritized by the Power BI product team. Before this is corrected, the technique is useful but will take-up more vertical space than you might prefer.

Creating in-line Sparklines

On a related topic, Brian Grant has developed a method to produce SVG trend line charts, commonly known as “sparklines”, using a creative string replacement technique. You can find a tutorial for his solution here.

download sample PBIX file