Dynamically Changing Shapes in Power BI

UPDATE: I also published a blog post using a DAX only approach. Check it out here.

Recently someone had a question if it was possible to change the color of a shape depending on the value of a measure. Imagine for example a dashboard with the picture of a database server. If up-time is good (availability >= 95%), the server would color green, if it was bad it would color red. This is not so hard to do in Visio (I saw it once in a SharePoint 2013 business intelligence course several years back), but the requirement was to do it in Power BI Desktop.

In Reporting Services, we would use expressions on the properties of the shape to get it done. However, Power BI currently doesn’t have expressions to set, so we have to think a bit out of the box here. My idea was to have a measure that calculates the up-time of the server and depending on the result, it would change the URL to a image. This means you need to have images of all the different possible states of your shape. In this blog post I’ll use the following two shapes:

thumbs up if availability >= 95%

thumbs up shape

thumbs down if availability is lower than 95%

thumbs down shape

I loaded the following test data into Power BI Desktop:

test data for changing shapes

I added some extra fields so I have some to slice and dice in the report. In Power Query, I also added the weekday number using an out-of-the-box transformation.add weekday nbr in power query

On my system this returns 0 for Mondays and 6 for Sundays (this might depend on your locale). I’m fine with this, because Mondays are the start of the week (otherwise they wouldn’t call Saturday and Sunday the weekend, gottit USA?). I will use this weekday number column to properly sort my dayweek column.

Unfortunately it’s currently not possible to load images with Power Query into the model, something that is possible in PowerPivot or in Tabular models. So we have to read them from disk, or from a URL.

The actual measure is straight forward:

ImageURL = IF(AVERAGE(ServerAvailability[availability]) >= 0.95,"D:/Tmp/Images/thumbsup.png","D:/Tmp/Images/thumbsdown.png")

You can insert images into Power BI Desktop, but these are static images. If you want them to dynamically change, you need the Image Viewer custom visual. Unfortunately, it doesn’t support measures, only columns. Since we want dynamic changes, fixed column values are not going to work. Someone proposed a work around on the Power BI forums, but this only works if you have a fixed set of attributes you want to slice on (for example, 4 categories). I want a totally flexible solution (e.g. each month we have a couple of new weeks to slice on), so again, not possible.

The only solution I could think of that would still work: using R visuals. It took me a while to find the right plotting method (one that doesn’t clutter the plot so there are too many white areas and one that preservers the original size). I tried rasterImage and the imager package, but both had too much white space:

too much white space in r image plot

Finally I settled with the ggplot2 package and the qplot function.

The full R script is as follows:

#load libraries
library(png)
library(grid)
library(ggplot2)

# get the url string from the imported data frame. Convert it to a character vector.
urlstring <- as.vector(dataset[1,1])
# if you use an image URL, you have to download the file first:
#z <- tempfile()
#download.file(url = urlstring,destfile = z,mode="wb")

#read the PNG image file
pic <- readPNG(urlstring)

# create a raster of the PNG file
g<-rasterGrob(pic,interpolate=TRUE)
# create a plot and add the image
p <- qplot(1:10,1:10,geom="blank") + annotation_custom(g,xmin=-Inf,xmax=Inf,ymin=-Inf,ymax=Inf)
# remove all clutter from the plot and display it
p + theme(axis.line=element_blank(),axis.text.x=element_blank(),
          axis.text.y=element_blank(),axis.ticks=element_blank(),
          axis.title.x=element_blank(),
          axis.title.y=element_blank(),legend.position="none",
          panel.background=element_blank(),panel.border=element_blank(),panel.grid.major=element_blank(),
          panel.grid.minor=element_blank(),plot.background=element_blank())

This gives the following result:

r script editor to show images

Now I can add other elements to the report and test my solution. When nothing is selected, the grand total average is 94.90%, which results in a thumb down image.

test shape change 1

If I change the week slicer to week 1, the measure rises above 95% and we get a thumbs up.

test shape change 2

If we select week 3, we get a thumbs down:

test shape change 3

You could also add a slicer on day of the week, and that would work as well. Note that there is a small delay once we change the slicer, since the image has to be loaded from disk first. You could make the solution work with image URLs as well, in that case you have to modify the measure of course but also you have to explicitly download the image file in the R script (which you can see in the comments). You might have also noticed I used forward slashes in the image file names; this is typical in the R language where the backslash is a reserved character.

You can do all sorts of cool stuff with this solution, but to be honest, it’s quite the work around. You need to use R scripts, which means having an R distribution installed on all the machines that are going to view this report in Power BI Desktop, and have all the necessary packages installed. I’d rather have the Image Viewer visual accept text measures (I’ve send a note to the developers) or that Power BI Desktop includes out-of-the-box functionality to change properties of shapes and images.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

18 thoughts to “Dynamically Changing Shapes in Power BI”

  1. Interesting technique, I wouldn’t have thought of this approach since I am not much of a “R person”. I don’t use the image viewer custom visual also, as I like the chiclet slicer better and I have done this kind of visuals using the chiclet slicer before. Eg – http://sqljason.com/2016/04/hex-tile-grid-maps-for-power-bi.html (check out the portion where the images of the president change).

    That said, I was interested to see if the same approach works using the image viewer custom visual and it does, just tested it. We can create a disconnected table in the Model (just 2 rows with ThumbsUp and ThumbsDown image url and description). Then create a measure which will return 1 for ThumbsUp if the condition is true, 1 for ThumbsDown if it’s condition is true, else 0
    Test = SUMX(VALUES(Img[State]), SWITCH(Img[State], “ThumbsUp”, IF(CALCULATE(SUM(Table1[Measure]))>95,1,0), IF(CALCULATE(SUM(Table1[Measure]))>95,0,1)))

    Now you can just add this measure to the visual filter of the ImageViewer and see that the image changes based on what you select. You can see the result below –
    https://jasonsql.files.wordpress.com/2017/02/temp.gif

    The same approach can also be followed for the Chiclet slicer. The beauty of Power BI is that the same thing is possible in multiple ways. They are just waiting there to be discovered.

  2. Thanks for your input Jason!
    I started out with a disconnect table as well, but I couldn’t figure out how to pass a visual level filter to the Image Viewer. Turns out that internally the visual “loops” over the images and filters out those where the test measure isn’t equal to 1, am I right?

    Very interesting.

    1. The image viewer works just like a table, it shows all the records (or images in this case). And for each record, the measure is evaluated (in simplistic terms, it is almost like if you had got the column used in visual level filter as the next column in the table and then applied the filter on top of it).

  3. Actually i do this all the time without R or custom visuals. For cimplicity, just create a table with 1 row and 1 column, something like:

    Column = IF([Measure]>0.95,”https://cdn.pixabay.com/photo/2013/07/13/10/27/hand-157251_960_720.png”,”http://www.clker.com/cliparts/5/2/5/8/13476359851958638477thumbs-down-icon-red-hi-hi.png”)

    And in Modeling, set the data category to “Image URL”. Then in the report just use the multi-row card, and select this column. So if the value of [Measure] > 0.95 it’ll show green thump up, and if below it’ll show red thump down.

    1. Hi Alaeddin,

      If I’m correct this means you are using a calculated column, right? This means the measure inside the column is evaluated at processing time. However, I want to evaluate my measure at runtime, meaning if I select something on a graph or on a slicer, the value of the measure will change and the visual will change as well.

    2. I am not quiet understanding how you do this – but it is exactly what I need !
      If I try and build the table in PowerBI using your code, i get an error for measure (as it does not yet exist), and for “http” (because the column has not yet been created and thus is not configured as an image URL).

      I am wondering if a table needs to be created in Excel and imported first, so the column can be set as an image URL. But then what would go in the single row, and where is the measure created.?

      To clarify how to do this could you please give a step-by-step explanation because this is a very valuable solution.

    1. Hi Lorenzo,

      you are absolutely right. However, the original question was about changing the color of a shape, not just changing colors. In that case KPIs would indeed be preferred.

  4. Hi! I was checking your solution, and it was to interesting, but I have a question, Don’t You have problems when export it to Power Bi Service?

  5. Hi Lorenzo,

    Thank you for your share, but I have a Problem,why I can not find the Image Viewer custom visual on Your share URL,could you sent me the download url ? thank you very much.

    My email is liangcai0424@163.com

    1. Hello – could you share out the image viewer you are using? I also cannot find one on the Power BI store. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.