Enforce Row-level security in Power BI

Add a security table to the model

  1. In Power BI Desktop, open the Power Query Editor window.

  2. Add a new query based on the D:\Demo\Data\**ManagerCategory**.xlsx file.

  3. Use the ManagerCategory table in the file.

  4. Remove the Manager column.

  5. Split the Category column by the semicolon delimiter and split into rows (advanced options).

  6. In the Email column, replace the value ty-johnston@tailspintoys.com with the recipient account (from the MySettings.txt file).

  7. Point out that this user is able to see three product categories: Collective pitch, Trainer, and Warbird.

  8. Close and apply the queries.

  9. In Model view, create a relationship between the ManagerCategory and Product tables relating the Category column.

  10. Set the cross filter direction to Single (ManagerCategory filters Product).

  11. Hide the ManagerCategory table.

Add a role

  1. In Report view, open Manage Roles, and then create a role named Manager.

  2. In the role, filter the ManagerCategory table Email address column as follows:

   [Email] = USERPRINCIPALNAME()
  1. Save.

Validate the role

  1. Open View As, and then configure the following settings:

    • Other User: Check, then enter the recipient account.

    • Manager role: Check

  2. Point out that the filter visual shows just three product categories.

  3. Stop viewing the report using the view-as options.

  4. Save the Power BI Desktop file.

  5. Publish the Power BI Desktop file to the workspace, overwriting the dataset and report in the service.

  6. Close Power BI Desktop.

Configure dataset security

  1. In the Power BI service for the instructor, from the Navigation pane, open the security page for the Sales Analysis dataset.

  2. In the Members section, enter the recipient account (representing Ty Johnston).

  3. Add and Save.

Test row-level security in the app

  1. In the Power BI service for the recipient, refresh the dashboard (left open from the previous demo).

  2. In the Profit Margin dashboard tile, verify that only three product categories can be seen.