Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles.
You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. For Analysis Services or Azure Analysis Services lives connections, you configure Row-level security in the model, not in Power BI Desktop. The security option will not show up for live connection datasets.
There are 2 main types of RLS:
- Dynamic RLS
- Using the variable "userprincipalname()" and a mapping of which for example dimensions a user has access to in a separate table.
- We strongly prefer Dynamic RLS
- Static RLS
- Hard coding RLS to "Roles" and then in Power BI Service assigning users to roles.
Before you set up RLS:
- Make sure you have a BI Book Premium Analytics Licence (Grants access to Power BI Service)
- Open your Power BI model in Power BI Desktop and import a table containing the users and the roles you want the users to have. The table can come from BI Book Dataroom, MS Teams or any other source.
- Role is not necessary if user rights are contained in the underlying data (for example time entries)
Setting up RLS:
- Open your Power BI model in Power BI Desktop and add the role to the user table you want.
- Manage Roles
- Create a new role
- Select the table you want to apply the filtering on
- Write the dax expression like this: [YOUR_FILTER_COLUMN_NAME]=userprincipalname()
- Validate the Relationships that you applied the security filters on. We do not assume responsibility for wrongly configured RLS.
- Validate desired result using a username:
Example models:
- Example model:
- One way relation + example function:
- RLS without a relationship in relationship view:
More instructions can be found on Microsoft's page here: : https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls