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 =
SWITCH(
SELECTEDVALUE(‘Dynamic Fact'[FactKey]),
1,[Sales Amount],
2,[Sales Profit],
3,[Sales Units],
4,[Product Cost],
5,[Profit pct],
BLANK()
)

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