Show or Hide a Power BI Visual Based on Selection - Excelerator BI

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 and more recently the background in a card.

Solution Overview

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.
  • 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.

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 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")

Create a Card to Work as a Mask

I placed a card visual with the measures [Message] placed in the  “Values” section.  I made the card big enough to cover the visual. 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.

Cond Formatting Card Background

Click on Conditional formatting, Select Format by as “field value” and then select the measure.

Cond Formatting Card Background 2

Importantly, the order of the visuals (front to back) needs to be:

  1. The card
  2. The visual to show/hide.

The Final Result

And here is the final result.

transparent measure

You can download my sample workbook here.

41 thoughts on “Show or Hide a Power BI Visual Based on Selection”

  1. Hi Matt,

    Here is my solution:
    Check Filtered = IF(ISFILTERED(Products[Category]),1,0).

    Place the measure to Filters on this visual tab and in Show items when the value: is 1

    Hope it helps anyone.

  2. Hello Matt,

    I experimented with your hack to show or hide some menu buttons based on certain values in a table, to restrict access to certain pages in my report
    But one problem: if the button is visible and the transparancy is on of the card or shape (rectangle) that’s used to hide the menu-button, it’s not possible to use the click to another page , because you’re still clicking on the card that’s used to hide the button, and not the button itself.
    It’s the same if you hide for example a chart or table, it’s not possible to click in it to filter another visual

    Have you experienced the same problem and have maybe (hopefully) a solution?

    1. Yes, you are right – I found this too. In my example you can’t select the visual hidden then shown for the same reason. I realise my concept is a hack and it has some limitations. Microsoft has announced that it will continue to build out expression based formatting across the product. So I hope and expect that at some stage we will be able to use a measure to make any visual visible or not

    2. Hi, in my case I wanted to hide a table. The scroll wheel didnt work when it was fully placed over the table but when I leave a bit of space on the right side it does work when the white space is gone. Might help with your problem aswell if you tried to scroll on your visual.

  3. Hi Matt,

    Great article, I am uunable to locate ellipses on the right-side of Color on card visual. I am using May 2019 release of PowerBI

  4. Simon Schofield

    Hi there, is there a way to amend the ‘Make Transparent’ measure so that when a certain value is selected from the slicer the transparent treemap is activated, rather than no value being selected from the slicer. Cheers

  5. Hello! A great tip, thanks for it!! Do you think the same would be possible if one bar in a bar chart is selected. With that I mean if a user would click on a bar, more details would appear (the treemap would become transparent) depending on the selection of the bar. (each bar has a unique ID).
    Thanks million times!

    1. You would have to test it. I am expecting more expression based formatting to come to Power BI in the future, so hopefully at some stage this will be a feature rather than a hack.

  6. My guess is that you have cross filtering set to “highlight”. If that is the issue, change “edit interactions” to “filter”. It is on one of the menus visible when you select the matrix.

  7. Hi, Instead of Slicer selection, I tired with Matrix row selection. With the Matrix row selection, I am not able to achieve the same thing. Could you please let me know how to use with Matrix row selection (SelectedValue) instead of a Slicer? Thank you in Advance.

  8. Pete Watridge

    Hi Matt — really enjoy your articles — have your website on my regular “to read” list. Your insight of using Treemap as a controllable color shape was brilliant and an eye opener for me. I had a project where I needed the background color of a matrix to be controlled by filters. So I adapted your approach: I made the background of the grid 100% transparent — and then put the treemap “background” behind it that responded to the filters via DAX formula. It worked perfectly. Never would have thought of using treemap as a controllable background without this article. Many thanks!

  9. Dang: This doesn’t work for me when saved to the PBI service and rendered in IE (version 11.0.120).
    Works fine in chrome. But our users have IE as the default.

    Anyone manage to get it working in IE?

  10. Hey Matt…I just spent ages trying to replicate your trick to no avail, until I finally realised that I was trying to trigger it from a Table (not a Slicer) and the default behaviour was to crossfilter the Treemap rather than filter it. Meaning it didn’t work. Perhaps you could include another step in your instructions above showing how to change the “Edit Interactions” behaviour of the TreeMap from Crossfilter to Filter, so this cool trick can be used for other types of trigger visuals.

    But I love it, man.

    1. Another way to accomplish this is to use visual level filters combined with dynamic titles. Create a measure that is 1 if filtered, 0 if not. Drag that measure to filter pane and set to only show when measure=1.

      You can also use that measure within the dynamic title to have it display a message. I use this for data tables when I don’t want all of the information showing until a user has filtered it.

      1. @Stu I use that trick too. But this method of obscuring both the data and the headers means you can do additional stuff like overlay different types of visuals on top of each other, and trigger a bookmark-like experience from a Slicer, which is very cool (and should be allowed natively).

  11. Is it possible to do the trick on two slicers with an OR function or similar? So the message would disappear if one selected the first slicer or if they just selected the second slicer?

  12. Hi Matt – Great Article, when i was experimenting with the file i realized that instead of writing the hex code for transparent as #FFFFFF00, one could just write “transparent” and it works.

  13. Could this work to highlight a sub-group in a scattergram? i.e. make the unselected sub-groups more transparent than the selected one.

    1. The principle of a measure that formats a data point is the same, I think. So as long as you can write the DAX, you will be able to do what you suggest. But what you will need to do is have your slicer either with an inactive relationship, or no relationship at all, otherwise the slicer will filter the visual. I assume that changing the cross filter behaviour between slicer and visual will not work in this case, but I can’t be sure.

      1. Great thanks, I solved it as you say by overlaying two scattergrams with the same set axes and one which has an inactive relationship to the slicer

  14. This is awesome Matt! I’ve been trying to find a way to do this as well, the treemap approach is pretty brilliant.

    One thing to note regarding optimization: the matrix is still calculating even though you can’t see it. That means a potentially large DAX query is still running in the background…possibly slowing down other visuals.

    You may want to adjust your matrix measure to the following:
    [Matrix Measure with Check] :=
    IF(
    ISFILTERED( Products[Category] )
    ,[Matrix Measure]
    ,BLANK()
    )

    This will return BLANK() if there’s no selection, saving the other measure from being evaluated.

    1. Yes, this would be another approach. I was really trying to find a problem to match my solution – the idea of using transparency to hide or show an object on the canvas. I am sure my solution has other applications too. For example, you could create an instructions section on the page, and overlay multiple text boxes with multiple instructions, all set to transparent text and transparent background. When certain conditions are met, you could display the appropriate instruction by changing the transparency.

  15. Nice workaround. Coming from the Qlikview world and having used that for the past 8 years, it’s amazing to me that simple SHOW/HIDE conditions are not available yet in PBI. This conditional formatting they are starting to roll out makes me feel like they are starting to get it and PBI is getting more and more Qlik like with every release.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top