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

Show or Hide a Power BI Visual Based on Selection

Updated: 17 Jan 2020

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.

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

Requirement

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.

Hidden Visual Live Connection

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

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.

Check Filtered

Blank Matrix

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.

Power BI Online Training

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.

Hidden Visual Live Connection

You can download my sample workbook here.

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

  1. Tridib kumar Dey

    I want hide a report page. If there is no data. I mean in my case it may happen some time data will come some time it will not. So whenever there will be no data how to hide a the visuals?

  2. Slicer values: Week, Month, Quarter

    3 graphs are present in same page
    1.Bar chart 2.Line Chart 3.Tree Map

    Requirement is:
    when user select Week in SLICER then display only1.Bar chart and 2&3 graph should hide
    when user select Month in SLICER then display only1 2.Line Chart and 1&3 graphs should hide
    when user select Quarter then display only1 3.Tree Map and 1&2 should hide

    1. Jason Cockington

      Hi Raju,
      If I understand your scenario correctly, then you will need to make separate measures for the separate show/hide conditions.

  3. does the same work for a table visualisation ?I am using the text filter from the app store -and the check fiklter does not seem to work for this …

    1. I couldn’t get the nice trick to work with the table visualization either. I had a master table and a child table and I wanted to show no data on the child table unless I click on a row in the master table. In the Check Filtered measure, I had to test different columns of the master table ISFILTERED(MasterTable[FindTheRightColumnHere]) and all of a sudden it worked. I hate to not understand the reason but it is working now.

  4. Hi ,
    I have almost a similar requirement.
    Requirement : When I select a button only the specific visual has to change (this one I can do with bookmarks)
    I have 2 visual independently (and 2 on exactly on top of them)
    I have four buttons , 2 on top of each visual
    button1chart button2table
    ——-chart visual—-
    ——-table visual———
    ————————–
    button3chart button4table
    —–chart visual—————–
    ——table visual———————-
    —————————
    Now when I select button2table , the chart visual should change to table
    After this when I select button4table , the chart visual should change to table (the first visual table should remain table)
    after this , when I select button3chart , the visual should come back to chart (but still the first visual should remain as table).

    It’s like the visual should change independently.
    how can I achieve this ?
    please help
    Note : I have tried bookmarks and selection panes
    But the problem is when I bookmark button4table , I don’t know the above visual is in which state and that state should not change

    Thank you Much Appreciated