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() )
Share This:
Twitter
Facebook
LinkedIn

Stay up to date with the latest customer data news, expert guidance, and resources.

Iteration and Error Handling in M Query

All iteration roads in M Query eventually lead to the List.Generate function. In strengthening our data manipulation skills through Power Query, we absolutely need this looping function.

More Resources

Trusted By Over 6 Million End Users​

Industry leaders and over 6 million end users choose SkyPoint as their comprehensive and compliant Modern Data Stack Platform. A Gold Certified Microsoft Partner, SkyPoint Cloud turns siloed customer data into connected customer experiences.