Forum Discussion
Dax for SVGImages sparkline
Hey PowerBI expert!
I have followed this tutorial to create sparklines and display into a table visual.
In my scenario, the x-axis is Date and the y-axis is Sales Rank. The sparklines (SVGImages) works perfect when I only have Product and Category columns in the table visual. What I'd like to achieve is add 'Date' and 'Sales Rank' columns into the table visual too.
for example, I wish the sparklines won't affect by the single 'Date' and 'Sales Rank', ideally the table visual will looks like as following:
Date | ProductID | Category | Sales Rank | Sparkline |
16/05/2020 | ABC | Computer | 100 | x-axis: 14th, 15th, 16th May 2020 y-axis: 98, 87, 100 |
15/05/2020 | ABC | Computer | 87 | x-axis: 14th, 15th, 16th May 2020 y-axis: 98, 87, 100 |
14/05/2020 | ABC | Computer | 98 | x-axis: 14th, 15th, 16th May 2020 y-axis: 98, 87, 100 |
I am sorry i don't know how to draw a line here so i write into words.. in this example the line will consist of three dots
(14th, 98)
(15th, 87)
(16th, 100)
I hope it make sense:)
I was thinking add ALL or ALLExcept funtion into the current svg dax that whatever the date selection I will always see a range of date I'm not sure if this can be achieved.. could anyone advise me a solution for this?
Your reply would be much appreciated! Thank you very much for reading my question 😊
janet_05 wrote:
I am wondering would it be possible if I select one single date from the date picker (slicer) and i still can see the sparklines..
for example, filter the Date as Sat, 2 January 2010 from the slicer and the sparkline's x-axis is from Sat, 2 January 2010 to Mon, 30 December 2013 and y-axis is Sales on each day respectively.
Yes you should be able to do this, you'd just have to define the number of days to plot in the sparkline. In the following code I've just changed the XMinDate to be the XMaxDate - 4 and then I've changed from using ALLSELECTED on the dates to get just the dates that are in the current filter context to using DATESBETWEEN to get everything between the max selected date and 4 days before that.
Sparkline Line = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMaxDate = MAXX(ALLSELECTED('Table'[Date]),'Table'[Date]) VAR XMinDate = XMaxDate - 4 var XDates = DATESBETWEEN('Table'[Date],XMinDate,XMaxDate) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = MINX(ALLSELECTED('Table'[Date]),CALCULATE([Measure Value])) VAR YMaxValue = MAXX(ALLSELECTED('Table'[Date]),CALCULATE([Measure Value])) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Table',XDates),'Table'[Date]), "X",INT(100 * DIVIDE('Table'[Date] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", [Date]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = IF(HASONEVALUE('Table'[Category]), "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()) RETURN SVGImageURL
3 Replies
- d_gosbell
Super User
So I've updated the file from the tutorial you linked so that it ignores the dates on the axis of the table by using ALLSELECTED and I've used the Sales column instead of Rank (since that is what the sample file is using for it's sparkline)
The results are below, I've also attached the pbix to this post
The adjusted sparkline measure is as follows
Sparkline Line = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMinDate = MINX(ALLSELECTED('Table'[Date]),'Table'[Date]) VAR XMaxDate = MAXX(ALLSELECTED('Table'[Date]),'Table'[Date]) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = MINX(ALLSELECTED('Table'[Date]),CALCULATE([Measure Value])) VAR YMaxValue = MAXX(ALLSELECTED('Table'[Date]),CALCULATE([Measure Value])) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Table',ALLSELECTED('Table'[Date])),'Table'[Date]), "X",INT(100 * DIVIDE('Table'[Date] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", [Date]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = IF(HASONEVALUE('Table'[Category]), "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()) RETURN SVGImageURLHi,
Thank you very much for the solutions!
I am wondering would it be possible if I select one single date from the date picker (slicer) and i still can see the sparklines..
for example, filter the Date as Sat, 2 January 2010 from the slicer and the sparkline's x-axis is from Sat, 2 January 2010 to Mon, 30 December 2013 and y-axis is Sales on each day respectively.
Thanks again for your kindly help 😊
- d_gosbell
janet_05 wrote:
I am wondering would it be possible if I select one single date from the date picker (slicer) and i still can see the sparklines..
for example, filter the Date as Sat, 2 January 2010 from the slicer and the sparkline's x-axis is from Sat, 2 January 2010 to Mon, 30 December 2013 and y-axis is Sales on each day respectively.
Yes you should be able to do this, you'd just have to define the number of days to plot in the sparkline. In the following code I've just changed the XMinDate to be the XMaxDate - 4 and then I've changed from using ALLSELECTED on the dates to get just the dates that are in the current filter context to using DATESBETWEEN to get everything between the max selected date and 4 days before that.
Sparkline Line = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMaxDate = MAXX(ALLSELECTED('Table'[Date]),'Table'[Date]) VAR XMinDate = XMaxDate - 4 var XDates = DATESBETWEEN('Table'[Date],XMinDate,XMaxDate) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = MINX(ALLSELECTED('Table'[Date]),CALCULATE([Measure Value])) VAR YMaxValue = MAXX(ALLSELECTED('Table'[Date]),CALCULATE([Measure Value])) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Table',XDates),'Table'[Date]), "X",INT(100 * DIVIDE('Table'[Date] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", [Date]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = IF(HASONEVALUE('Table'[Category]), "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()) RETURN SVGImageURL
