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 matrix 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() )