July 15, 2020
Row-level security in Power BI
For Power BI version 2.76.5678.782 (December 2019).
When handling data we need a system for handling user’s rights. We usually don’t want users to access entire database tables. Row-level security controls access at a row level. We can restrict access to rows using roles like IT, Accounting, CEO etc.
Roles in Power BI
We set roles by following the steps below.
1. Select Modelling > Manage Roles. Manage roles window opens up.
2. Select Create.
3. Enter roles name to Roles, e.g. IT Team.
4. Select the table you want to filter, e.g. Sales.
5. Enter DAX filter formula to Table filter DAX expression. E.g., if we want our IT Team role to only see rows with IT in the Project Name field, we enter [Project Name] = “IT”.
6. Check the formula by selecting check mark.
7. Save with Save.
Testing the view for a role: View As
1. After setting roles, we can test how the report looks like for the selected role.
2. Select Modelling > View As.
3. Select the role, e.g. IT Team.
4. Select OK. The report updates and shows the view for the selected role.
Adding and removing users in Power BI Service
While we can edit roles in v Power BI Desktop application, we can add or remove users only in Power BI Service.
1. To add a user to a role follow the steps below.
2. Select Datasets and the Options icon (…).
3. Select Security. Only project owners can see Security tab.
4. Enter user’s email.
5. Select Add.
We delete a user by selecting Delete symbol.
Testing the view for a role in Power BI Service
We can also test the view for a role in Power BI service. We follow the steps below.
1. Select the Options icon (…).
2. Select Test as role.
3. We get the list of report pages, assigned to current roles. We can observe reports as the selected roles sees it. Dashboards are not included.
4. We can select Now viewing as to change the role.
5. To return to normal view, se select Back to Row-Level Security in upper left corner.