Enforce Row-level security in Power BI
Add a security table to the model
-
In Power BI Desktop, open the Power Query Editor window.
-
Add a new query based on the
D:\Demo\Data\**ManagerCategory**.xlsx
file. -
Use the ManagerCategory table in the file.
-
Remove the Manager column.
-
Split the Category column by the semicolon delimiter and split into rows (advanced options).
-
In the Email column, replace the value ty-johnston@tailspintoys.com with the recipient account (from the MySettings.txt file).
-
Point out that this user is able to see three product categories: Collective pitch, Trainer, and Warbird.
-
Close and apply the queries.
-
In Model view, create a relationship between the ManagerCategory and Product tables relating the Category column.
-
Set the cross filter direction to Single (ManagerCategory filters Product).
-
Hide the ManagerCategory table.
Add a role
-
In Report view, open Manage Roles, and then create a role named Manager.
-
In the role, filter the ManagerCategory table Email address column as follows:
[Email] = USERPRINCIPALNAME()
- Save.
Validate the role
-
Open View As, and then configure the following settings:
-
Other User: Check, then enter the recipient account.
-
Manager role: Check
-
-
Point out that the filter visual shows just three product categories.
-
Stop viewing the report using the view-as options.
-
Save the Power BI Desktop file.
-
Publish the Power BI Desktop file to the workspace, overwriting the semantic model and report in the service.
-
Close Power BI Desktop.
Configure semantic model security
-
In the Power BI service for the instructor, from the Navigation pane, open the security page for the Sales Analysis semantic model.
-
In the Members section, enter the recipient account (representing Ty Johnston).
-
Add and Save.
Test row-level security in the app
-
In the Power BI service for the recipient, refresh the dashboard (left open from the previous demo).
-
In the Profit Margin dashboard tile, verify that only three product categories can be seen.