Hide measures using Row Level Security – #PowerBI

In some cases, you might not want to give all users access to all measures in your model – you might not want to show the profit to certain users.

In the Power BI Desktop designer/Service we can’t hide measures depending on the active user but by combining dynamic measures and row level security we can make our way around this.

In my example I use data from AdventureWorksDW2014 and created a datamodel around FactResellersales.

So, we have 5 measures but Sales Profit, Product Cost and Profit pct should be hidden for some users.

First up creating a dynamically fact

I created a table by entering data in a table

The column Secret should be used to use to filter by user and FactKey we will use the FactKey in a SWITCH statement to create a dynamic fact.

The dynamic fact

The fact will be created like this

Selected Fact =
SELECTEDVALUE(‘Dynamic Fact'[FactKey]),
1,[Sales Amount],
2,[Sales Profit],
3,[Sales Units],
4,[Product Cost],
5,[Profit pct],

In the model I hide all the columns and only show the fact

Hide the table FactResellerSales

To disable the user to be able to select any of the measures created in the FactResellerSales.

Use the dynamic fact

So, in order to use the fact we have to tell the visual which fact to use.

In this case a card visual I have selected the fact.

You can also use the matrix to show more facts at once

Create the Row Level Security

Now we need to add Row Level Security

NonSecretMeasures is now set to filter out the Facts where [Secret] is set to True.

Test the RLS

We can now test the Row Level Security in Power BI Desktop designer

So when viewing as NonSecretMeasures the user sees this

But when viewing as AllMeasures we see

Scaling it – consider moving it to Azure Analysis Services/Tabular model

This method doesn’t really scale very well but can be used in small models.

If your model is bigger and more complicated, you should look at building the model using Azure Analysis Services or a On Prem tabular model where you can implement object level security.

Q & A can help

Using Q & A in the report – it makes it a bit easier to create the visuals

Let me know what you think

Link to demo file – here


5 thoughts on “Hide measures using Row Level Security – #PowerBI

  1. Pingback: Escondendo campos com RLS – BI – Do Conceito à Prática

  2. Very helpful, this is the only solution I have found to achieve ‘CLS’ in Power BI. I’m implementing this for a client now (hiding cost price for some users).

    Do you also know how I can alter the ordering of the measures (as shown in the visual)?

    Thank you!

  3. It is useful!!!But the problem is that my measures should use different format, such of them with %, and currency. Do you think we can do something on it?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s