Building Basic SVG Sparkline/Small-Multiples Measures in DAX

Sparklines and small multiples are incredibly powerful. They allow you to add a tiny simple graph to a table or a matric that conveys the basic idea or shape of the data in a way that is compact enough to allow for a chart per row of a table, or per intersection of a matrix.

In Power BI however, these solutions don’t come as a simple one-click option. In fact, up until now they have been essentially impossible to add to a table or matrix.

With the August release of Power BI however we now have the ability to code measures as returning image urls, further we can trick this feature into return SVG images that aren’t stored somewhere on the web, but are instead generated in real time for your tables and matrices. That means these charts are 100% dynamic and respond to any slicing, dicing, or cross filtering a user can throw at them. This is an awesome technique, but it’s hard to understand and implement. In this video we will walk you through a simple but powerful implementation that you can recreate for your own solutions.

The Code:

My Sparkies = 
VAR vBaseText =
"data:image/svg+xml;utf8,

  
"
VAR vImgWidth = 320
VAR vImgHeight = 200

VAR vMonthList  = VALUES( DimDate[MonthID] )
VAR vMonthFirst = MINX( vMonthList, DimDate[MonthID] )
VAR vMonthLast  = MAXX( vMonthList, DimDate[MonthID] )

VAR vSalesMax = MAXX( vMonthList, [Total Sales] + 0)
VAR vSalesMin = MINX( vMonthList, [Total Sales] + 0)

VAR vPoints =
CONCATENATEX(
    vMonthList,
    VAR vcPointX =
        INT(
            DIVIDE( DimDate[MonthID] - vMonthFirst, vMonthLast - vMonthFirst )
            * vImgWidth
        )
    VAR vcPointY =
        vImgHeight
        - INT(
            DIVIDE( [Total Sales] - vSalesMin, vSalesMax - vSalesMin )
            * vImgHeight
            * 0.90
            + ( vImgHeight * 0.05 )
        )
    RETURN vcPointX & "," & vcPointY,
    ",  ",
    DimDate[MonthID]
)

VAR vReturn = SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( vBaseText, "#ImgWidth", vImgWidth ), "#ImgHeight", vImgHeight ), "#Points", vPoints )
RETURN IF( [Total Sales], vReturn, BLANK() )
Share this:
Brian Grant
Brian Grant
briang@cgspro.com

Brian Grant is a masterful Senior Analytics Consultant by day, and a Power BI legend by night. A passion for Power BI drove him to holistically master the tool from all three perspectives: M, DAX, and the visualization layer. Brian is a constant teacher who leads Power BI training sessions, while sharing his wealth of expertise through video and blogging resources.



Ready to wrangle your data?