Implement row-level security roles

A common business requirement is to secure data so that different users who view the same report can see different subsets of data. In Power BI, this can be accomplished with the feature called row-level security (RLS).

Row-level security restricts data by filtering it at the row level, depending on the rules defined for each user. To configure RLS, you first need to create and define each role in Power BI Desktop, and then assign individual users or Active Directory security groups to the roles in the Power BI service.

Note Row-Level Security and Live Connections

Defining roles in Power BI only works for imported data and DirectQuery. When you connect live to a Power BI dataset or an Analysis Services data model, Power BI will rely on row-level security configured in the source, and you cannot override it by creating roles in Power BI Desktop.

In this section, we review the skills necessary to implement row-level security roles in Power BI Desktop. We examine assignment of roles in the Power BI service in Chapter 4, “Deploy and maintain assets.”

Creating roles in Power BI Desktop

To see the list of roles configured in a dataset in Power BI Desktop, select Manage roles from the Modeling ribbon in the Report view. To create a new role, select Create in the Roles ­section. You’ll then be prompted to specify table filters, as shown in Figure 2-9.

FIGURE 2-9 Manage roles

When you create a role, you have the option to change the default name to a new one. It’s important to give roles user-friendly names because you’ll see them in Power BI service, and you need to be able to assign users to the correct roles. All roles are listed in the Roles section of the Manage roles window.

If you right-click on a role or select the ellipsis next to a role, you’ll be presented with the following options:

• Create This option creates a new role and is an alternative to the Create button below the list of roles.
• Duplicate This option creates a copy of the currently selected role.
• Rename Use this option to rename the currently selected role; you can also rename a role by double-clicking on its name.
• Delete This option deletes the currently selected role; this action can also be performed by selecting Delete below the list of roles.

For each role, you can define a DAX expression to filter each table. When row-level security is configured, these expressions will be evaluated against each row of the relevant table, and only those rows for which the expressions are evaluated as true will be visible.

You can either enter a table filter DAX expression yourself or use the ellipsis menu next to each table to add an expression that you can then customize. You can also access the menu by right-clicking on a table and choosing from these options:

• Add filter This option lists all columns available in the table and lets you hide all rows.
• Copy table filter from This option copies a table filter DAX expression from another role that has a filter expression defined for the table.
• Clear table filter This option removes any table filter DAX expression from the table. It’s a shortcut to erasing all text from the Table filter DAX expression area manually.

For example, in the Wide World Importers data model that we previously created, you can select the ellipsis next to City > Add filter > [Sales Territory] to insert an expression, as shown in the Table filter DAX expression area:

[Sales Territory] = “Value”

The placeholder expression depends on the data type of the column, and it helps you to write the correct filter expression.

After you modify the expression, you can validate it by selecting the Verify DAX expression (check mark) button above the Table filter DAX expression area. If the expression is invalid, you’ll see a warning stating that the syntax is incorrect below the Table filter DAX expression area. Next to the check mark button is the Revert changes (cross) button, which reverts any changes that haven’t been applied yet.

To hide all rows in a table, right-click on the table and click Add filter > Hide all rows. This will add the following table filter DAX expression:

false

Because false is never going to be true for any row, no rows will be shown in this case.

You can configure row-level security in the Wide World Importers data model. First, create two roles as follows:

Create a new role and call it Southeast.

For the Southeast role, in the City table, enter the following table filter DAX expression:

[Sales Territory] = “Southeast”

Select the Verify DAX expression button above the Table filter DAX expression area.

Right-click the Southeast role and select Duplicate.

Rename the new role to Plains.

For the Plains role, update the table filter DAX expression in the City table as follows:

[Sales Territory] = “Plains”

Select Save.

Important Duplicating Roles

If you duplicate a role before you verify the last added table filter, the table filter will not be copied to the duplicate role.

We can now test the roles in Power BI Desktop.