Implementing row-level security

A common security need for customers building reporting solutions with Power BI and needing to simplify the data model is a way to build multitenant data models. This means that data can be stored in a single table but only be viewable by the designated users who should have access to it. This capability is called row-level security.

Row-level security allows filtering to happen at the table level in a data model based on the user accessing the data. For example, an entire table might contain all the sales data for the organization across different regions or countries and some users of the report might only need to access data from their region or country. Without row-level security, we’d have to manually set up different tables for each region or country as well as a combined table with data from all regions for senior leadership. With row-level security, we can define the roles needed and apply a DAX expression to the table that will handle the filtering for each role. This way, all data can be stored in the same table and the filtering criteria set up for row-level security ensure each user sees only the appropriate data.

Setting up row-level security

After connecting to data in Power BI Desktop and creating a report that shows sales by country, your report might look like this:

Figure 6.1 – Example Power BI report visual showing sum of TotalDue in sales per country, for the United Kingdom and the United States

In the following steps, we will set up row-level security so each team member can only see the data for their respective country:

1. On the Modeling tab, click Manage roles. In the Manage roles window, we create all the roles within our organization that will need to view data in the report.
2. Click Create to create a new role.
3. Type a name for the role that corresponds to one of the groups of users that will access the data. For example, there may be a group of sales leaders in the United States, so we’ll call this role US Sales. We’ll also create a role for UK Sales.
4. Select the address table. The address table is used as a single place in our data model where all addresses (shipping and billing) are stored. A relationship is established between our address table and our sales and customers table, which allows cross-filtering between the tables. This will be required for the data to be filtered properly by the DAX expression we set up in the row-level security.
5. Type in the [CountryRegion] = “United States” and [CountryRegion] = “United Kingdom” DAX expression for each role created.

We can verify that the roles and filtering are set up properly by using the View as button on the ribbon under Modeling.

Figure 6.2 – The same Power BI report visual showing sum of TotalDue in sales per country filtered to only show the United States

1. Click View as.
2. Select one of the roles we just created; try US Sales.
3. View the filtered data in the report.

Next, let’s look at managing this security.