SVG Sparklines - Line

3351 Views
Highlighted
Advisor
Posts: 359
Registered: ‎06-26-2015

SVG Sparklines - Line

[ Edited ]

Power BI now allows the dynamic creation of Scalable Vector Graphics (SVG) images. Prior to the August 2018 version, it was possible to build SVG content into custom columns, but now it is possible to create measures that dynamically update to account for user selections and filters. 

 

For additional detail on the method, please see this post on the DataVeld blog: Use SVG Images in Power BI: Part 3

 

 

Get the DAX: SVG Sparkline "Line" measure formula on GitHub

 

 

Substitute your own measure in place of [Measure Value], a date or numeric column for 'Table'[Date] for the X axis, and a column for 'Table'[Category] that will appear on the same table row as the sparkline.

 

The x axis is built by establishing the min and max values for each date and adjusting the range so that the min corresponds to 0 and max corresponds to 100.

 

The y axis is built by finding the min and max measure values and adjusting the range from 0 to 100 similar to the x axis. The measure value's position is based on ([Measure Value] - Min) / (Max - Min), a key calculation that I added awhile ago as another Quick Measure. In the case of the y axis though, SVG works from the top to the bottom (0 at top), so 100 - [Y] makes it render correctly. Otherwise, the chart would appear upside down.

 

After building a table of X and Y coordinates, CONCATENATEX is the magic function that puts the points together into a line.

 

IF(HASONEVALUE('Table'[Category])[...] helps hides the total in a Table or Matrix because the total renders as text and not an image.

 

Once the sparkline measure is ready, set the Data Category to Image URL and add the sparkline measure to a Table or Matrix visual.

 

In addition, change the LineColor variable from static to dynamic to create conditional color formatting. In this example, if the Margin % is not greater than 20%, the sparkline should be grey instead of green.

 

//Note: use %23 instead of # for Firefox compatibility
VAR LineColor = IF([Margin] > 0.20,"%2301B8AA","%23BBBBBB")

 

Create additional variables for stroke width or any applicable SVG property and subsitute new variables into the SVG text as needed.

 

Thanks to my BlueGranite colleague Meagan Longoria for providing a challenge that first made me investigate whether it was possible to dynamically build SVG content in Power BI. Thanks as well to Phil Seamark for suggesting optimizations to my original DAX.

 

Attachment
Frequent Visitor
Posts: 10
Registered: ‎08-29-2018

Re: SVG Sparklines - Line

Hi David,

 

Is it possiblle to add two sparlines in the same column, for comparison purpose?

Advisor
Posts: 359
Registered: ‎06-26-2015

Re: SVG Sparklines - Line

You would need to figure out the DAX to separate your lines into different series based on whatever column you need, but I don't see why it couldn't be possible.

Frequent Visitor
Posts: 5
Registered: ‎12-04-2017

Re: SVG Sparklines - Line

Is it possible to make the sparkline stretch (or Fill) along the x axis when the width of the column is adjusted in the table? I was hoping to make the sparkline not be so condensed.  

Occasional Visitor
Posts: 1
Registered: ‎11-27-2018

Re: SVG Sparklines - Line

Hi,

 

Is it possible to use this when the report is connected to live connection.

 

I was able to get the Image URL but I am unable to change the data category for the calculated measure to ImageURL. Only Uncategorized, Longitude and Latitude options are available, rest are greyed out.

 

Thanks,

Krishna

Frequent Visitor
Posts: 5
Registered: ‎12-04-2018

Re: SVG Sparklines - Line

David great post!

Thanks for sharing it. Trying to emulate your model I only get the values string and not the charts. Do am I missing something?

Advisor
Posts: 359
Registered: ‎06-26-2015

Re: SVG Sparklines - Line

Did you include the "data:image/svg+xml;utf8," prefix before the SVG text and then set the measure's data category to ImageURL?

Frequent Visitor
Posts: 5
Registered: ‎12-04-2018

Re: SVG Sparklines - Line

I think so. This is what I have:

 

VAR SVGImageURL = IF(HASONEVALUE('Diario'[Cadena]),
"data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
"<polyline fill='none' stroke='" & LineColor &
"' stroke-width='3' points='" & Lines &
"'/></svg>",
BLANK())
 
 
Advisor
Posts: 359
Registered: ‎06-26-2015

Re: SVG Sparklines - Line

Apart from where this forum transformed my reply from an actual colon symbol to &colun, the variable looks good. Is 'Diario'[Cadena] in your table then as well?

Frequent Visitor
Posts: 5
Registered: ‎12-04-2018

Re: SVG Sparklines - Line

I am using Diario[Cadena] as category and yes,  I have it in my table. Any other idea of what could be wrong?