Updated: 17 Jan 2020.
Updated: 22 Sep 2021.
Updated: 27 Apr 2022 – Walkthrough Video Added
This is the 3rd iteration of this Power BI Visual Based on Selection article, attempting to address some of the confusion that has emerged since I added the [Check Filtered] measure into the mix in January 2020.
Matt has written a few articles in the past that toy with the ideas of changing visibility and text colour based on selection, and this article was first penned by his hand in April 2019, as he started to wonder if it was possible to make a power bi visual appear (or not) based on a selection from the user. Since that time, the article has become the most in-demand solution encountered on our website, and hence deserved a redeux. The challenge of selectively showing report elements has plagued Power BI report developers for many years now, but there is no out of the box solution for this problem at this point in time. It is possible to use bookmarks to show and hide an object, but the user must click a specific button to do this. If you want the user to be able to interact with a report and see (or not see) a chart based on some valid selection across the report, for the moment, this article is the best solution available. Microsoft is already working on further establishing expression based formatting across the breadth of Power BI, and currently allows one to change a number of settings ranging from the header in a chart to font colours, backgrounds and data colours.
If you would like the Power BI development team to include this feature in their plans, be sure to vote for the idea.
The solution to solve this problem (when trying to Show or Hide a Power BI Visual Based on Selection) involves:
- Create a card to work as a mask for the visual.
- Write a measure to check if there is a filter on the desired column (type Boolean)
- Write a measure to display a user message on the card based on the user selection (type Text).
- Write a measure to render the visual blank when nothing is selected (type Whole Number).
- Write a measure that returns either the background colour or “transparent” based on the user selection (type Text), and set that to conditionally format the background colour of the card.
Please note, this solution works well for most standard visuals, but the Waterfall chart, Scatter plot, and Map does not deliver the same effect due to the way these visuals are rendered.
In the sample report below, I want the matrix on the left to appear if the user selects an item in the category slicer on the right. If there is nothing selected in the slicer, I don’t want to see the matrix at all, but instead see some instructions on what to do.
Since adding the additional measures to the method, that allow the hidden visual to still be interactive after being revealed, significantly more questions surrounding implementation challenges have emerged. As a result, I decided to create a Walkthrough video to assist with the implementation of this strategy.
The key to this solution is the expression based formatting of colours using DAX. As described in the article about conditional based text colours linked above, you can set the result of a measure as being any HEX colour you want using conditional colour formatting. But as it turns out, you can change the transparency of a HEX colour like #FFFFFF by adding an optional additional 2 characters to the end (00 being 100% transparent). So while #FFFFFF is white, #FFFFFF00 is 100% transparent (white).
The trick then lies in having a box that can switch its background between a colour white or transparent. The Card is perfect for this.
Before we can proceed with building our background switching card we need some measures.
Write the Check Filtered Measure
The first measure that is needed is one that can return a True or False response based on the selection in our slicer. We’ll start with a simple measure that will return True whenever any selection is made in the slicer.
Check Filtered = ISFILTERED(Products[Category]) -- Returns True when Any selection is made on the Category slicer
Note: We’ll return to this measure at the end of the article to see what other options we have here.
Create the User Instructions
Next we will create a measure that will display or hide a message to the user on the card. The message will be displayed by default, but when the user selects an item in the Product[Category] slicer, the message will disappear and will reveal the hidden visual. To achieve this, we need a measure that will return nothing if the selection condition has been met, and display the message when it hasn’t.
Message = IF( [Check Filtered], -- Check Filtered returns True or False based on selection in slicer "", -- Give me nothing when Check Filtered returns True "You must first select a Product Category from the slicer to see the results" )
Create a Card to Work as a Mask
Next, place a Card visual onto the canvas with the [Message] measure placed into the “Values” section. The Card should be displaying the message. Turn off the Category Label in the format settings, and then test to see the message disappears when you make a selection in the slicer.
Write the Hide Visual Measure
The next thing needed is a measure that can make the visual you wish to Show/Hide (in this case a matrix) appear blank when there has been no selection made in the slicer. Again, we follow the [Message] measure’s logic, but this time the measure will return integers (whole number format) rather than text.
Note: it is critical this measure returns an integer, a boolean result (true/false) will not work, nor will a text result.
Hide Visual = IF( [Check Filtered], 1, 0 )
Filter the Visual to Render Blank
Select the visual to be hidden, open the Filters pane and place the [Hide Visual] measure onto the “Filters on this visual”, and set the “Show items when the value: is 1”. In the case of the matrix being used in this example, this results in the matrix being rendered with just 2 rows visible. This means there is no need to cover the entire matrix with the card layer, we can just cover over the top two rows, to achieve the same visual effect. This is important as it means that the visual will render larger than the card when displaying information, which will enable click interactivity with the displayed data for further cross-filtering of the report.
Cover the Empty Matrix
Select the masking Card made earlier. Click the Format tab in the ribbon, and adjust the layer order of the Card so that it is brought to the front. Resize the card so it is just big enough to cover the visible elements of the blank visual (top 2 rows of the Matrix).
Now the trick is to use conditional formatting for the Card’s background colour using rule based expressions.
Write the Transparent Measure
This measure is also pretty easy. The desired outcome is to show the matrix when the user has selected an item in the Product[Category] slicer and hide the matrix otherwise. The measure again follows the same pattern as the previous measures. It first needs to test whether or not a selection has been made in the Product[Category] slicer, and alter the result based on this test.
Note: It is essential that the data type of this measure is text. If it is not text, it will not work.
Make Transparent = IF( [Check Filtered], "#FFFFFF00", -- returns transparent if Check Filtered is True "White" )
In other words, if there is a value selected in the slicer, make the “colour” transparent, otherwise make it white (the colour of the report background) so that it hides the matrix.
Format the Card
The final requirement is to apply the [Make Transparent] measure to format the background colour of the masking Card. To do this, click on the Card, then select format\Background and switch it on. Then click on the fχ button on the right-side of Color as shown below.
Click on Conditional formatting, Select Format by as “field value” and then “Based on field” and select the [Make Transparent] measure.
Importantly, the order of the visuals (front to back) needs to be:
- The card
- The visual to show/hide.
The Final Result
And here is the final result of this tutorial about showing or hiding a power bi visual based on selection.
A Few Final Thoughts
When working with Maps, the world map will always render even when there is no data to display. As a result the [Hide Visual] measure doesn’t have the same effect on this visual type as that demonstrated with the Matrix above. In the case of Maps, there will be a trade off. To hide the Map, it will need to be completely covered by the masking Card, but this means that interactivity with the map will be lost. Alternatively, leave the world map fully visible, but alter the message, to explain that data will only be revealed once a selection is made.
Waterfall charts also yield a less than perfect result with this technique. Either follow a similar approach as that suggested with the Map, or it will be necessary to turn off the title from the X axis and turn off the Y axis entirely in order to have the visual appear blank when empty.
In the demonstration the Show/Hide is responding to a simple selection. If anything on the slicer has been selected, then Show the visual. Sometimes however, it will be necessary to reveal the visual only if a specific selection has been made. In this case, rather than using ISFILTERED(), the [Check Filtered] measure should be using a SELECTEDVALUE() test. However, SELECTEDVALUE() is only appropriate if the specific selection is a single valid value. For a scenario where it is necessary to have a specific combination of values from the one slicer selected before the visual can be revealed, ISFILTERED() is to broad and SELECTEDVALUE() is to narrow. In this situation we need to take advantage of CONCATENATEX() to return the selected values as a text string, and then test that string for a specific combination.
In the modified [Check Filtered] measure below, each of these scenarios is available to experiment with, simply uncomment the logic to test.
Check Filtered = ISFILTERED(Products[Category]) -- Returns True when any selection is made on the slicer // SELECTEDVALUE(Products[Category]) = "Bikes" -- Returns True only when Bikes are selected // SELECTEDVALUE(Products[Category]) = "Bikes" || SELECTEDVALUE(Products[Category]) = "Clothing" -- Returns True when either Bikes or Clothing is selected // CONCATENATEX(VALUES(Products[Category]),Products[Category], " ") = "Clothing Bikes" -- Returns True only when both Bikes and Clothing are selected
If you want to save this guide for the next time you need to Show or Hide a Power BI visual based on selection, you can download the sample workbook here.