#PowerBI – Create a sparkline in the new card visual and some other nice formatting options

In the June 23 release of Power BI desktop we has been blessed with a new card visual that not only will give a better performance compared to adding multiple cards in the past but it also gives us some formatting options we have been dreaming of for a long time.

Here is a link to the official blogpost – New card visual | Public preview | Microsoft Power BI Blog | Microsoft Power BI

On social media you have probably already seen a lot of examples of usages, and I wanted to share how you can add a sparkline to the card to make it look something like this.

The demo dataset is based on hourly temperature data from a weather station in central Copenhagen Denmark – shared via Danish Meteorological Institute – Open Data – DMI Open Data – Confluence (govcloud.dk) and the model imports data from their API for the period of 2022-01-01 until yesterday of the writing of this blog post.

Let’s try out the new and the old card

When first added you might think … hmmm that is not really a big difference – BUT the BIG difference lies in your formatting options and that you can add more than one measure to the visual.

With the old card you would have to add a second Card to your report and sending two queries to your dataset instead of 1 as the new visual will do. Better performance – YES thank you.

In this post I will focus on how we can use the formatting options to make it really stand out.

Formatting options

Each of the measures you add to the visual can be formatted individually via the Cards properties

Let’s start by adding an accent bar to the left

Tip – Notice that the color can be controlled by fx meaning that you could make the accent color change depending whether the temperature was higher or lower compared to last year.

Another way of adding an accent bar is to use an image via the Fill properties

And then via the image control the location of your accent bar – the image is however static and can’t be controlled by a formula.

The values and label is formatted via the Callout section of the visual

Here you should especially notice that we now can control how blank values should be shown ️

Here is the default way

And here telling the user that there is no data

The label can also be controlled by an expression –

Giving us the possibility to give feed back to the reader whether the temp is higher or lower than last year for instance.

Here is the DAX measure linked to the label

Feedback on temp = IF([Avg Temperature] > [Avg Temperature LY] , "Avg temp - higher than last year", "Avg temp - lower than last year")

But now it’s time to look at how to add a sparkline

The property we have to use to do this is the Image property in the Cards section.

The Image type can be two different types – either an image or an image URL

When you pick the image URL

Notice that it can be a static URL or based on an expression

So I connect the Image URL to my Field called Temperature Sparkline

OBS – Remember to set the Category of your expression to Image URL

OBS – One thing I have noticed is that I have had to change the Size from Auto to a particular px size for it to work.

And now the card has a sparkline

In my formula I have also added the change value compared to last year average temperature.

So how did I make the formula to create the sparkline

I took a look at Kerry Kolosko amazing templates for SVG’s

Chart Templates – EXPLORATIONS IN DATA STORYTELLING WITH POWER BI (kerrykolosko.com)

And modified a bit to fit the column names in my model and then added a text element to show the change.

Here is the DAX measure

 

Temperature Sparkline =

// Line and area colours – use %23 instead of # for Firefox compatibility (Measure Derived from Eldersveld Modified by Kolosko)

// “Date” field used in this example along the X axis

VAR
XMinDate = MIN(Observations[Date])

VAR
XMaxDate = MAX(Observations[Date])

// Obtain overall min and overall max measure values when evaluated for each date

VAR
YMinValue = MINX(Values(Calendar[Date]),CALCULATE([Avg Temperature]))

VAR
YMaxValue = MAXX(Values(Calendar[Date]),CALCULATE([Avg Temperature]))

// Build table of X & Y coordinates and fit to 50 x 150 viewbox

VAR
SparklineTable = ADDCOLUMNS(

    SUMMARIZE(‘Observations’,Calendar[Date]),

        “X”,INT(150 * DIVIDE(Calendar[Date] – XMinDate, XMaxDate – XMinDate)),

        “Y”,INT(50 * DIVIDE([Avg Temperature] – YMinValue,YMaxValue – YMinValue)))

// Concatenate X & Y coordinates to build the sparkline

VAR
Lines = CONCATENATEX(SparklineTable,[X] & “,” & 100-[Y],” “, Calendar[Date])

// Add to SVG, and verify Data Category is set to Image URL for this measure

VAR
SVGImageURL =

    “data:image/svg+xml;utf8,” &

    “<svg xmlns=’http://www.w3.org/2000/svg&#8217; x=’0px’ y=’0px’ viewBox=’0 0 150 100′>” &

     “<polyline fill=’%232e4e6a’ fill-opacity=’0.3′ stroke=’%232e4e6a’

      stroke-width=’3′ points=’ 0 150 ” & Lines & ” 150 100 Z ‘/>

      <text font-family=’Segoe UI’ font-size=’24’ x=’90’ y=’20’> ” & FORMAT([Avg Temperature Chg], “#0.0” ) & “</text></svg>”

RETURN
SVGImageURL

The measures can/should be modified to fit your column names and measures and could be made even more dynamic using field parameters – but that might come in a later blogpost.

Link to the demo file

You can get a copy of the demo file here – Link

Do this as well

Give feedback to (2) PBI Core Visuals: Overview | LinkedIn on LinkedIn or directly to (2) Miguel Angel (@myersmiguel) / Twitter – who is doing an amazing job improving the core visuals in Power BI.