Edit: 17 Jan 2020 – New Content by Jason Cockington
Interact with the Hidden Visual
– Check Filtered Measure
– Filter Matrix to Render Blank
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 and 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 and more recently the background in a card.
My solution to solve this problem involves.
- Create a card to work as a mask for the visual.
- Create a measure to display a user message on the card based on the user selection.
- Create a measure to render the visual blank when nothing is selected
- Write a measure that returns either the background colour or “transparent” based on the user selection, and set that to conditionally format the background colour of the card.
I have a 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.
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 Card.
Create the User Instructions
The first thing I had to do was to create a measure that displayed a message to the user on the card. I wanted to show the matrix when the user had selected an item in the Product[Category] slicer and display the message otherwise. This was easy as follows.
Message = IF( ISFILTERED(Products[Category]), "", "You must first select a Product Category from the slicer to see the results" )
Write the Check Filtered Measure
The next thing I needed to do was write a measure that would make the matrix appear blank when no selection in the slicer had been made.
Check Filtered = IF( ISFILTERED(Products[Category]), 1, 0 )
Filter the Matrix to Render Blank
Select the matrix, and place the [Check Filtered] measure onto the “Filters on this visual”, and set the “Show items when the value: is 1”. This renders the matrix 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 cells for further cross-filtering the report.
Create a Card to Work as a Mask
Next, I placed a card visual with the measures [Message] placed in the “Values” section. I made the card just big enough to cover the blank visual (top 2 rows). I turned off the card category label. Now the trick is to use conditional formatting for the card background colour using rule based expressions.
Write the Transparent Measure
This was also pretty easy. I wanted to show the matrix when the 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) so that it hides the matrix.
Format the Card
Then I applied the measure to format the card background colour. To do this, click on the Card, then select format\Background and switch it on. Then click on the ellipses on the right-side of Color as shown below.
Click on Conditional formatting, Select Format by as “field value” and then select the 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.
You can download my sample workbook here.