Data Bear’s Monday Motivation Blog Post

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

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.

Show Hide DAX

 

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

Conditional Formatting on 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.

Show Hide DAX for title

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.

Conditional Formatting Field Value

This is the effect you will get.

Show Hide GIF

 

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.

Button format

 

The effect is as below.

Hide show button GIF

 

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.

RLS table

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

Message DAX Hasonefilter

Add this to the field of your card measure.

 

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

Make transparent DAX

 

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

Conditional Formatting Field Value

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

Add RLS.

RLS Manage roles

RLS set up

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

View As Role

View as Role screen

 

The report page before.

Not viewing as role

The report page when viewing as role.

Viewing as role

 

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

Our training options here.