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 =
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