Sparklines in Power BI with SVG

Question

I am creating some sparklines for a Power BI report using svg and manage to create the sparkline as expected. What I need is to change the color of the line after some specific point (x-axis is date and want to change based on date. So, after month 62 for example the line to be displayed in a different color. This is the code I have so far:

Sparkline = 
VAR vBAseText=
"data:image/svg+xml;utf8,
<svg width=""#ImgWidth"" height=""#ImgHeight"" version=""1.1"" xmlns=""http://www.w3.org/2000/svg"" style=""background: #ffffff"">
<polyline points=""#Points"" stroke=""#LineColor"" fill=""transparent"" stroke-width=""2""/>
</svg>"
VAR vImgWidth=120
VAR vImgHeight=120


VAR vMonthList=Values(D_Calendar[Date_ID])
VAR vMonthFirst= MAXX(vMonthList,D_Calendar[Date_ID])-12
VAR vMonthLast= MAXX(vMonthList,D_Calendar[Date_ID])
VAR vMonthSelected=MAXX(vMonthList,D_Calendar[Date_ID])-6



VAR vSalesMIN=MINX(vMonthList,[Units]+0)
VAR vSalesMAX=MAXX(vMonthList,[Units]+0)

Var vPoints= CONCATENATEX(vMonthList,
                            VAR vcPointX=
                                    INT(
                                        DIVIDE(D_Calendar[Date_ID]-vMonthFirst,vMonthLast-vMonthFirst)*vImgWidth
                                    )
                            VAR vcPointY=vImgHeight-
                                    INT(
                                        DIVIDE([Units]-vSalesMIN,vSalesMAX-vSalesMIN)*vImgHeight
                                    )

RETURN vcPointX & "," & vcPointY, ", ",D_Calendar[Date_ID]
) 
VAR vLineColor="%23550055"

                   
VAR vReturn=SUbstitute(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(vBAseText,"#ImgWidth",vImgWidth),"#ImgHeight",vImgHeight),"#Points",vPoints),"#LineColor",vLineColor)
Return vReturn

I didn't know you could do this! Then I stumbled across the "Power BI Sidetools" External Tool that generates the DAX. mind blown Thanks for posting this question. I learned a lot! See my solution below. — TheRizza
– TheRizza, Commented Dec 15, 2021 at 16:03

TheRizza · Accepted Answer · 2021-12-15 16:00:20Z

Here's a sample that works. I built two separate lines separated by a hardcoded month number, but you could make this a variable too.

Sparkline Line = 
// Static line color - use %23 instead of # for Firefox compatibility
VAR Color1 = "%23FF0000" //Red
VAR Color2 = "%230000FF" //Blue

// "Date" field used in this example along the X axis
VAR XMinMonthNo = 0
VAR XMaxMonthNo = DATEDIFF(CALCULATE( MIN('Orders'[OrderDate]), ALL('Orders') ),
                        CALCULATE( MAX('Orders'[OrderDate]), ALL('Orders') ),MONTH)

// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = MINX( VALUES( 'OrderDate'[MonthNo] ) , CALCULATE( [Total Sales] ) )
VAR YMaxValue = MAXX( VALUES( 'OrderDate'[MonthNo] ) , CALCULATE( [Total Sales] ) )

// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable1 = ADDCOLUMNS(
    SUMMARIZE( FILTER('OrderDate', 'OrderDate'[MonthNo] <= 7) , 'OrderDate'[MonthNo] ),
        "X",INT(100 * DIVIDE( 'OrderDate'[MonthNo] - XMinMonthNo, XMaxMonthNo - XMinMonthNo)),
        "Y",INT(100 * DIVIDE( [Total Sales] - YMinValue, YMaxValue - YMinValue)))
VAR SparklineTable2 = ADDCOLUMNS(
    SUMMARIZE( FILTER('OrderDate', 'OrderDate'[MonthNo] >= 7) , 'OrderDate'[MonthNo] ),
        "X",INT(100 * DIVIDE( 'OrderDate'[MonthNo] - XMinMonthNo, XMaxMonthNo - XMinMonthNo)),
        "Y",INT(100 * DIVIDE( [Total Sales] - YMinValue, YMaxValue - YMinValue)))

// Concatenate X & Y coordinates to build the sparkline
VAR Line1 = CONCATENATEX( SparklineTable1 , [X] & "," & 100-[Y]," ", 'OrderDate'[MonthNo])
VAR Line2 = CONCATENATEX( SparklineTable2 , [X] & "," & 100-[Y]," ", 'OrderDate'[MonthNo])

// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = IF( HASONEVALUE( 'Categories'[CategoryName] ) ,
    "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='" & Color1 & 
     "' stroke-width='3' points='" & Line1 & 
     "'/><polyline fill='none' stroke='" & Color2 & 
     "' stroke-width='3' points='" & Line2 & 
     "'/></svg>",
     BLANK())
RETURN SVGImageURL

// This measure has been generated by Power BI Sidetools DAX generator
// and then modified to do lines in 2 colors
// It used the template 'DAX sparklines'  from David Eldersveld, Mike Carlo
// URL : https://community.powerbi.com/t5/Quick-Measures-Gallery/SVG-Sparklines-Line/td-p/486271
// URL : https://gist.github.com/deldersveld/62523ca8350ac97797131560cb317677
// URL : https://app.powerbi.com/view?r=eyJrIjoiOTExYmFjNTAtYzRkNy00YzU3LWJjOWUtY2M2YjM3YjI1MzkwIiwidCI6ImFjYzhhYWE1LWYxOTEtNDgyZi05MjFiLWNmNmMzM2E1ODgzMiIsImMiOjF9
// Generation date : 12/13/2021 5:38:02 PM

Collectives™ on Stack Overflow

Sparklines in Power BI with SVG

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related