Data Bear’s Monday Motivation Blog Post

Welcome, all.  Today we’ll be looking at different ways to hide/show items in Power BI.

1. Using conditional formatting

2. Using buttons

3. Using row-level security

1. Using conditional formatting to hide show items in Power BI

Conditional formatting is one of the most powerful tools in the Power BI box, in my opinion.  Today I will only show a very small part of what it’s capable of, but this will give you a great idea, and you can build on it.  Pop me a message if you have a specific request regarding this, I’ll be happy to help.

My scenario today is pretty simple, I want a card visual to show values in different colours depending on the product category chosen in the slicer, also I want it to be blank when nothing is chosen.  I also added conditional formatting to update the title, depending on the chosen category.

There are different ways to achieve this, but I will be using DAX to determine what criteria were being met, and then apply a rule in the conditional formatting of the card to implement the condition on the title and the colour of value.

Writing the DAX to hide/show items in Power BI

I’m addressing the value on the card first.  I used the IF function to determine what criteria were met.

Then you apply the rule to the conditional formatting of the colour of the Data label.

Next, I used the same DAX measure, to apply some formatting on the border of the card.

Now I am addressing the DAX for the card title to show the category it is filtered on.

This time we use the field value option in the conditional formatting section of the title.  You can go one step further and apply the same DAX as above to give the title the sale colour as the value, I did not.

This is the effect you will get.

2. Using buttons to hide/show items in Power BI

Using buttons is another clever way to hide/show items in Power BI.  In my example, I will be hiding a text message using a blank button.  It’s important to know you can set different values and interactions on a button depending on your interaction with them.  I’m referring to the button statuses like default, on hover and on press.

I wanted to hide a message and reveal it on hover.  This is a nice way to communicate to your audience without taking up too much space and also the message doesn’t necessarily need to be visible at all times.

I used button text however there is conditional formatting available that would support DAX.

The effect is as below.

I cannot take full credit for this, see here for another blog that uses this technique to hide/show items in Power BI using a slicer.

3. Using Row Level security to hide/show items in Power BI

The next option is not the most glamourous of ways, but I have had to use it before, hence I’m sharing it.  The need came up for an entire page in a report to only be visible to certain managers.  As you know that function is not available. So I found a workaround.  The page I needed to “hide” was not data that could be restricted, but rather calculations on the same data.  Therefore if I were to use normal Row Level security, it would restrict what they could see, that was not the desired effect.  I could have made a summary table and simply set row-level security on that, but I did not want to complicate my model.

Build your page as normal, then add a card and make it big, so that it overlays all the things you want to hide.

First, you need to create a table on which to base the RLS.  It is a very simple table, you can use the Enter Data option. Just 1 column and 2 lines will suffice.

Next, create the message you want to display by writing some DAX.

Add this to the field of your card measure.

Next, you need to control the background colour by writing more DAX.

Add this to the background settings of your card, and setting it to Field Value

Next, you have to set up your Row-level security.

Add RLS.

To test if it works you can use the View As role option.

The report page before.

The report page when viewing as role.

To view more information about Row-level security see here for our blog page.

Our training options here.