Categories: Power BI

Dynamically Changing Shapes in Power BI – Alternatives

NOTE: the DAX presented in this blog post is written in my locale (nl-BE). This means that semicolons are used in the functions and lists instead of commas. Also, we use the comma as the decimal seperator. 0.95 (in the US) is 0,95 in my locale. A couple of well placed search and replaces should fix those issues if you are in another locale.

Yesterday I published the blog post Dynamically Changing Shapes in Power BI, which seemed to be quite popular in social media. It showed a lot of people were struggling with this issue and that some people had also found (alternative) solutions for it. In the comments of that blog post, you can find a solution proposed by Jason Thomas (blog | twitter). Jason has quite the Power BI & SSRS knowledge and he has found a cool trick to solve our changing images problem. In this blog post, I’m going to explain the solution step-by-step.

First of all, we have to create a disconnected table that will store the URL of our images, along with a short description. You can do this in DAX by using a calculated table:

Images = DATATABLE (
    "ShapeURL"; STRING;
    "State" ; STRING;
    { 
        { "https://sqlkover.com/wp-content/uploads/2017/02/thumbsup.png";"ThumbsUp"};
        { "https://sqlkover.com/wp-content/uploads/2017/02/thumbsdown.png";"ThumbsDown"} 
    } 
)

The next step is to create a measure. This measure will calculate a filter value, which we are going to use later on to filter our visual.

FilterValue = SUMX(
                    VALUES(Images[State]);
                    SWITCH(Images[State];
                                "ThumbsUp";
                                    IF(CALCULATE(
                                                    AVERAGE(ServerAvailability[availability])
                                                ) > 0,95
                                            ;1
                                            ;0
                                      );
                                    IF(CALCULATE(
                                                    AVERAGE(ServerAvailability[availability])
                                                ) > 0,95 
                                            ;0
                                            ;1
                                        )
                        )
               )

We can visualize the result in a table:

Let’s take a closer look at this measure.

SUMX(VALUES(Images[State]);…). This is an iterator. It goes over the rows of a table and will calculate an expression for each row of that table. At the end, the results are aggregated using Sum. Here the table is the result of the VALUES function, which gives two rows: ThumbsDown and ThumbsUp.
The next step is evaluating the SWITCH function. If the current row is equal to ThumbsUp, it will evaluate an IF statement. If it’s not, it will evaluate another IF statement.
The IF statement checks if our server availability is higher than 95%. If it is, the result is 1 (for ThumbsUp) and if it’s not, the result is 0. If the current row is ThumbsDown, the logic is the other way around: if the availability is higher than 95%, the result is 0, otherwise 1.
We need to put CALCULATE around AVERAGE(ServerAvailability[availability]). This is needed to transfer from a row context into a filter context. If the images table had a relationship with the fact table, the current row would have an impact on the filter context. However, we are using a disconnected table, so the filter context is the same for both rows. In other words, the calculated measure returns the same result. It’s the IF statements that decide if a 0 or a 1 is returned.
When we take a look at the example above – availability = 94.90% – and we follow the logic, we can see that ThumbsDown has a filter value of 1 (meaning availability < 95%) and ThumbsUp has a filter value of 0. The total row repeats the same process and aggregates the result: 0 + 1 = 1.

In the following animation you can see how our table behaves when we change the availability:

The next step is to use the custom visual Image Viewer to show our image. In the filter area, we add the FilterValue measure and we configure it to be greater than or equal to 1.

Now our image behaves the same as our table:

The clue behind this is that the Image Viewer visual still internally behaves like the table: there are rows you can loop over and thus rows that you can filter out. I didn’t realize this at first (I just thought the visual took the first image it could find if multiple images are provided).

The advantage of this solution is that you don’t have the dependency on R code and this solution will certainly work in PowerBI.com. The disadvantage is that you can only use image URLs, you cannot load images from disk (and there’s no option to load images in the model, like you can in PowerPivot).

Someone else suggested to use a calculated column, but then you lose all flexibility. If you have a report where the condition of the shape only needs to be determined when the model refreshes, than calculated columns are certainly a good option.

Will Thomson of the Power BI team had the following suggestion:

@Ko_Ver I have a no-code version… Still a workaround (multiple Card visuals stacked on top of each other but hey, it works. Also, animals! pic.twitter.com/z51c3Kkheu
— Will Thompson (@Will_MI77) February 8, 2017

Unfortunately, you cannot add a filter to a card visualization, so again, we lose the flexibility we have when working with measures. A last alternative – if you don’t like custom visuals – is using a table. The principle is the very same as when you use the Image Viewer. However, in a table you have almost no control over the size of the image.

------------------------------------------------
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 Missing Checkbox for Security Filter in Power BI Desktop »

Previous « Dynamically Changing Shapes in Power BI

View Comments

Tina says:

February 10, 2017 at 7:21 pm

Hi Koen,

This is a great idea, we like it so much! Thank you!
The only question can it be used for a real-time streaming dashboard in Power BI? Would be great to show some KPI with dynamically changed images there.

Best regards,
Tina
- Koen Verbeeck says:
  
  February 13, 2017 at 10:39 am
  
  Hi Tina,
  
  I'm not sure you're able to define extra measures on top of a streaming dataset.
  (at least, I haven't found any example where they do this)
  Since the dataset only exists in the service, I can't seem to find a way to add extra DAX measures.
  
  Very good idea though, I hope it becomes possible in the future.
- Rajat says:
  
  September 25, 2020 at 9:37 am
  
  Hi Tina,
  
  I'm trying something similar. Just curious to know, did you able to achieve this in Power BI service.
  
  Regards,
  Rajat
Julian says:

February 17, 2017 at 7:22 am

Hi Koen,

What if I have more than 2 images, say 3 options "S", "M", and "L" instead? In addition to modifying the FilterValue measure, how can I configure the filter area of visual Image Viewer?

Best regards,

Julian
- Koen Verbeeck says:
  
  February 17, 2017 at 7:50 am
  
  Hi Julian,
  
  it actually doesn't matter how many images you have. You just have to construct your FilterValue measure in such a way that only one image results in 1 and all the others in 0.
  Example:
  instead of "> 95" and "<= 95", you can do "< 50" ,">= 50 and <= 95" and "> 95".
  - Julian says:
    
    February 17, 2017 at 11:25 pm
    
    Hi Koen,
    
    Thanks for your instructions. I'll try it accordingly.
    
    Regards,
    
    Julian
    - Koen Verbeeck says:
      
      February 20, 2017 at 9:23 am
      
      OK, let me know how it went :)
  - Hector Alvaro Rojas says:
    
    May 3, 2019 at 6:54 pm
    
    Hi KOEN:
    
    Would you mind writing down the codes for the" FilterValue" in this case here:
    Example:
    instead of “> 95” and “< = 95", you can do "= 50 and 95″.
    
    By the way, you have done terrific work with this -Dynamically Changing Shapes- articles. I have been trying both successfully. They are great!
    
    Congratulations!
Julian says:

February 20, 2017 at 1:55 pm

Hi Koen,

I've put R script and DAX formula in one PowerBI desktop file following your instructions and both versions run smoothly, but the image refreshing with R script seemed a little bit slower than that from image viewer. As to the third image, as I don't have my own website to store the image files, so I just tried the R version to retrieve my local images with the modified measure as shown below:

ImageURL = IF(AVERAGE(Table1[availability]) > 0.97,"C:/Users/Julian/Pictures/thumbsup.png",IF(AVERAGE(Table1[availability]) >= 0.95, AND(AVERAGE(Table1[availability]) <= 0.97,"C:/Users/Julian/Pictures/fair.png"),"C:/Users/Julian/Pictures/thumbsdown.png"))

However, when I sliced wk1 and wk4 with the availability values falling in the range between .95 and .97 I got the following error message:

MdxScript(Model) (1, 199) Calculation error in measure 'Table1'[ImageURL]: Cannot convert value 'C:/Users/Julian/Pictures/fair.png' of type Text to type True/False.

Does it mean the R script needs further modifying accordingly? Please comment. Thanks.

Regards,

Julian
- Koen Verbeeck says:
  
  February 20, 2017 at 2:31 pm
  
  There's a problem in your measure:
  
  AND(AVERAGE(Table1[availability]) --> there's no second parameter and the final ) is missing
  
  Remove the AND and it should work.
Julian says:

February 20, 2017 at 3:01 pm

Sorry, I'm a little slow to the nested if statement. Could you please give me the exact measure instead? Sorry for bothering you so much.
Julian says:

February 20, 2017 at 3:11 pm

I worked it out finally after trial and error. Please forget my previous reply. Thanks.
- James says:
  
  February 20, 2017 at 6:40 pm
  
  Julian, i have the same problem of you, can u help me plz.. How can I resolve the issue?
  - Koen Verbeeck says:
    
    February 21, 2017 at 9:04 am
    
    Hi James,
    can you give us a bit more info on your problem?
Julian says:

February 21, 2017 at 12:04 pm

James,

I guessed you were talking about the nested IF statement. If this is the case, please refer to the following fixed script:

ImageURL = IF(AVERAGE(Table1[availability]) > 0.97,"C:/Users/Julian/Pictures/img1.png", IF(AVERAGE(Table1[availability]) >= 0.95, "C:/Users/Julian/Pictures/img2.png","C:/Users/Julian/Pictures/img3.png"))

Regards,

Julian
Paolo says:

March 30, 2017 at 3:59 pm

Any suggest if I want use 3 variable up, down or middle. Thanks
- Koen Verbeeck says:
  
  March 30, 2017 at 4:11 pm
  
  Julian already asked the same question. It doesn't matter how many images you have. You just have to build the right case expression so that only one image has the value 1 at a given point in time, and all the others have 0.
Betzalel Fialkoff says:

September 4, 2018 at 10:08 am

Is there any way to do this based on simpler boolean statement? If row value is TRUE then 1 image, else display another image
- Koen Verbeeck says:
  
  September 4, 2018 at 10:38 am
  
  Hi Betzalel,
  
  you probably can. In the example here, you need a measure though, so you'll need to get the "row value" into a measure.
Marc R says:

November 7, 2018 at 10:55 am

Hello,
if instead to have a value, i.e. 0,97, we have a word? I would like to show an image based on a word. I have a table with Approved and Rejected words. How I can link the "ThumbsUp" to the word "Approved" and "Thumbsdown" to the word "Rejected"?
Thanks in advance.
- Koen Verbeeck says:
  
  November 7, 2018 at 1:42 pm
  
  Hi Marc,
  I would model this like you would in a regular dimension with pictures for certain members.
  For example, a table with movie titles that also contains the movie poster or a list of countries and their flags. The only difference is that you might store the images in a separate table, linked to the original table with a relationship.
Daniel Brooks says:

October 12, 2021 at 12:15 pm

Hi,

Im using this to create a visual dashboard (or trying to) i've successfully managed to get the table to work and when i only have a single image to load it works fine, however when i attempt to load a second image neither load.
I have attempted creating a second data table with the image URLs and linking the new image to that however it still leaves both blank.

Any thoughts?
- Alexander van Roij says:
  
  January 3, 2022 at 8:32 am
  
  I have the same issue. I
  used a workaround: add another column with the same url in the IMAGES table and use copy column in the second visual, then the 2 images are shown correctly.
  but this implies that you need a seperate column per visual, not very user friendly if you have multiple visuals in your dashboard.