By: Matt Wollner
 
 

What is Row Level Security (RLS) in Power BI?

Row Level Security (RLS) can be setup in Power BI to restrict data access based on the logged in. For example, perhaps you have multiple sales teams and you want each team to see sales data only for their respective region. Row Level Security will enable you to do so.

In this post, I’m going to walk through two ways to setup RLS in Power BI. The first way is a simple method, where I’m going to setup a Role per sales territory and then assign each user to a Role. The user will be able to see only the data for the roles assigned to them. This method is easy to setup but can become cumbersome to manage.

The second way involves me defining the users and territories in a bridge table. This table will be added to the data model and user data will be restricted based on the values assigned in the table.

I have pulled in “Fact InternetSales” from the AdventureWorksDW database along with a few assisted dimensions to provide content for this walk through. My main goal is to look at “Internet sales by Sales Territory” and limit the report to the end users Territory.

 

Method 1 – Create a Role per Territory

There is a “Manage Roles” button on the ribbon under the “Modeling Menu”. Open the “Manage Roles” and create a new Role.

My first Role will be for the USA. I want to limit the US Territory to only data where the “Sales Territory Country” is United States. A DAX expression can be used to filter any of the available tables. Select “DimSalesTerritory” and set the [SalesTerritoryCountry] = “United States”. I have also setup the “Territory World”, all territories where the [SalesTerritoryCountry] <> “United States”.

Power BI makes it easy to test the Roles you have setup. Select “View As Role” and pick the “TerritoryUS” that we just setup. You’ll notice that all the data in the report will be filtered to US data only.

You currently cannot assign users to a Role in Power BI Desktop. You will need to publish your report to the Power BI service. Once it has been deployed, you will assign users under the Dataset for the report. Click on the ellipsis next to the dataset and then click on “Security”.

Add each user to the appropriate Role by entering their email.

You can also test the online report with each given Role by clicking on the ellipsis next to each Role.

This is simple yet powerful, but it does require you to set up every user for each Role on the Power BI Portal. Now, on to Method 2…
 

Method 2 – Manage the Roles in a Table

A more advanced option will be to create a table that stores the users and Roles in a table.

First, I created a tabled named “BridgeSalesReps”. Inside the table, I have a row for every “SalesTerritoryKey” and user account (email address) that has access to view the data associated with that Sales Territory. I am giving Brad access to every “SalesTerritory”, Jason access to the US, and Me access to everything non US.

After I add the new table to the data model, I have to configure the join so that it will filter all associated data. Set the Cross Filter Direction to Both and Check the Apply Security Filter in Both Directions. To learn more, visit Microsoft’s documentation here.

To setup the Role, we go back to the “Manage Roles” section. We are going to create a single Role that controls all the Territory security. Set the user column, “UserName”, to the USERPRINCIPALNAME()

To Test, use the “View as Role” feature. You will need to enter the user to be tested under Other User, and select the Role that was just created.

Again, we cannot assign users access in Power BI Desktop; we need to publish the report to the Power BI Service. Just like before, we need to update the security for the dataset. But this time, we need to add every user to a single Role. After the user has been given permissions to the dataset, the data in the bridge table will be in charge of filtering the data. After you have added each user, you can click the ellipsis and Test as Role.

The initial view will be for your user. You can click on the down arrow and view the report as any user in your organization.

Warning: Row Level Security does not work when viewing the report in the Workspace. Anyone that has permission to the Workspace will be able to see all the data. Row Level Security will only restrict data once the report is deployed, so be mindful of the Workspace that the report is deployed to!

More info: https://docs.microsoft.com/en-us/power-bi/service-admin-rls

 

Questions?

Thanks for reading! We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
 

Keep your data analytics sharp by subscribing to our mailing list

Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!

Grow your analytics knowledge.

Leave this field empty if you're human:

 

---

 

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.