Categories: Power BI

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 down if availability is lower than 95%

I loaded the following test data into Power BI Desktop:

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.

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:

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:

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.

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

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

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.

Next Dynamically Changing Shapes in Power BI - Alternatives »

Previous « Microsoft Data Platform MVP Award

View Comments

Jason Thomas says:

February 7, 2017 at 3:22 pm

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.
Koen Verbeeck says:

February 7, 2017 at 3:50 pm

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.
- Jason Thomas says:
  
  February 7, 2017 at 4:10 pm
  
  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).
Maxim Zelensky says:

February 7, 2017 at 11:48 pm

Nice solution and comments, thanks!
Alaeddin Badran says:

February 8, 2017 at 6:45 am

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.
- Koen Verbeeck says:
  
  February 8, 2017 at 8:18 am
  
  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.
- Paul Shuster says:
  
  February 16, 2018 at 8:18 pm
  
  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.
  - Koen Verbeeck says:
    
    February 19, 2018 at 8:51 am
    
    Hi Paul,
    in this example I use some R code to visualize the images. A better option might be to use the DAX solution instead: https://sqlkover.com/dynamically-changing-shapes-in-power-bi-alternatives/
    
    I did use a custom visual though to display the image, because you can't import the images into Power BI Desktop like you can in Power Pivot.
Lorenzo Vercellati says:

February 8, 2017 at 10:28 am

Very Interesting solution, but a little too complicated to change the color of an image.
If image is not mandatory, you could use custom visual card with sates by okViz: https://app.powerbi.com/visuals/show/PBI_CV_7B952816_A48F_49B4_9E13_15E3BB2C0337
This visual "allows you to bind a performance measure and to define multiple states that determine the color of the visual itself".
- Koen Verbeeck says:
  
  February 8, 2017 at 10:43 am
  
  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.
Koen Verbeeck says:

February 8, 2017 at 3:07 pm

To the subscribers of the comments, I posted a follow-up blog post about changing images in Power BI, now with a full DAX solution.

Dynamically Changing Shapes in Power BI – Alternatives
Johanan says:

August 4, 2017 at 12:38 am

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?
- Koen Verbeeck says:
  
  August 4, 2017 at 9:45 am
  
  Hi Johanan,
  
  I can't remember if I tested it at the service at the time I wrote this post.
  I'm just on a new laptop and I haven't installed R and Power BI yet. However, should it fail in the service, you can take a look at the "DAX-only" solution: https://sqlkover.com/dynamically-changing-shapes-in-power-bi-alternatives/.
Cai Liang says:

August 6, 2017 at 12:49 pm

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
- Kate says:
  
  November 28, 2017 at 5:37 pm
  
  Hello - could you share out the image viewer you are using? I also cannot find one on the Power BI store. Thank you!
  - Koen Verbeeck says:
    
    November 29, 2017 at 8:17 am
    
    Someone has upload it here in the community forum.
Khushboo Dixit says:

October 25, 2017 at 7:15 am

Great solution, the only problem being it doesn't work on Power BI Service as networking (accessing web URLs etc.) via R isn't allowed in Power BI service.
- Koen Verbeeck says:
  
  October 25, 2017 at 7:52 am
  
  Make sure you read the follow-up, which has alternatives that should work in the service:
  https://sqlkover.com/dynamically-changing-shapes-in-power-bi-alternatives/