Show or Hide a Power BI Visual Based on Selection
I have written a few articles in the past that toy with the ideas of changing visibility and text colour based on selection. I started to wonder if it was possible to make a visual appear (or not) based on a selection from the user. There is no out of the box way to do that today. It is possible to use bookmarks to show an hide an object, but the user must click a specific button to do this. I 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. Microsoft is already working on building expression based formatting across the breadth of Power BI however as of now the only item you can change is the header in a chart.
Solution Overview
My solution to solve this problem involves.
- Create a Treemap with 1 large and 1 small value.
- Write a measure that returns either the background colour or “transparent” based on the user selection, and set that to conditionally format the colours of the Treemap.
- Create a measure and card to display a user message.
Requirement
I have a sample report below. I want the matrix on the right to appear if the user selects an item in the category slicer on the left. 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.
Transparent Colours
The key to this solution is the expression based formatting of colours using DAX. As I described in my 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 next trick was to have a box that I could colour white or transparent – enter the Treemap
Create a Treemap
This was the obvious contender but I had to do some experimenting with it to work out how to do what I wanted. I started out writing a measure VALUE = 1 and added that to the Treemap, however it seems that you can’t conditionally format the colours of the treemap if there is only 1 value displayed. The solution I settled for was to create a simple table of data with 1 very large number and 1 small number. I created a new table using enter data as follows:
ID Value A 10000 B 1
I loaded the table and added the ID to Group and Value to “Values”. This gave me a Treemap with 1 very large box, 1 very small box (so small you can’t see it) and it would accept conditional formatting of the colours using rule based expressions.
Write the Transparent Measure
This was pretty easy. I wanted to show a matrix when there user had selected an item in the Product[Category] slicer and hide the matrix otherwise. The measure I wrote therefore was this
Make Transparent = IF(ISFILTERED(Products[Category]),"#FFFFFF00","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).
Format the Treemap
Then I applied the measure to the Treemap. To do this, click on the Treemap, then select format\data colors\advanced controls, then select format by “field value” and then select the measure.
While I was in the Treemap formatting pane, I turned off all the optional formatting such as borders, titles, data labels etc.
Create the User Instructions
The last thing I had to do was to create a measure that displayed a message to the user. This was also easy as follows.
Message = IF(ISFILTERED(Products[Category]), "","You must first select a Product Category from the slicer to see the results")
I added this measure to a card, turned off all the card category and placed it on top of the Treemap. Importantly, the order of the visuals (top to back) needs to be:
- Card
- Treemap
- Visual to show/hide.
Note 1: that the selection by the user is based on the Slicer that directly filters the treemap. It will not work with highlighting. You can edit the cross filter behaviour from “edit interactions”.
Note 2: as pointed out by a reader, you could also use the new custom title feature to display the message instead of the card.
The Final Result
And here is the final result.
