Keeping data secure is something that’s of utmost concern for data analysts and organizations alike. This paramount need stems from a variety of reasons, including adherence to regulations, prevention of financial loss, protection of intellectual property, and ensuring business continuity.

One frequently employed method for ensuring data safety involves managing access through a system of requirements and roles. In Power BI, this level of control is possible by using the Row Level Security feature.

This article will explore Power BI’s Row-level security feature and how you can use it to effectively bolster data protection and control access to information.

What is Row Level Security?

Row-level security in Power BI offers a security solution that enables you to control and limit what users of a report or dashboard can view based on their designated roles.

Row-level security makes it possible for you to maintain the confidentiality of sensitive information by making sure users have access to data that specifically pertains to their responsibilities.

Consider this scenario where you have data collected from sales managers operating in different regions. Your objective is to grant managers in each region access only to data relevant to their respective regions. For instance, the sales manager based in the United States should exclusively see the United States sales data.

At the same time, you want to provide members of top management with unrestricted access to all sales data across regions. Achieving this precise control is attainable through the implementation of Row-level Security within Power BI.

How to Implement Row Level Security in Power BI

Row-level security functions by implementing filters that limit data access at the granularity of individual rows. These filters are set up within predefined roles, effectively determining the data accessibility for anyone assigned to a particular role.

Power BI desktop empowers you to configure row-level security whether you’re working with imported data models or accessing them through DirectQuery.

However, for datasets connected to Analysis Services or Azure Analysis Services through live connections, row-level security configuration is done within the model and not in Power BI Desktop.

Power BI offers two distinct types of Row-level security features – Static and Dynamic row-level security. The key distinction lies in their approach. While Static row-level security employs a fixed value to filter user access, Dynamic row-level security leverages a DAX function for this purpose.

This model is used to illustrate how you can apply row-level security to your data.

The data in this model consists of the FactInternetSales and DimSalesTerritory tables imported from the AdventureWorks2019 database. Both tables are connected based on the EmployeeKey column. These tables would be used to create simple visualizations.

In the above image, there are two visualizations. The bar chart illustrates the sales amounts generated in each country, while the card visual presents the total sales value.

Building on this, you can implement row-level security to restrict data access, ensuring that employees view only the sales value specific to their country.

Once row-level security has been implemented and is undergoing testing, you’ll observe a shift in the values displayed within this visual, corresponding to the specific role through which it is being viewed.

To apply row-level security, these are the steps involved.

Step 1: Open the Manage Roles Window

Go to the Modeling tab and click on Manage roles.

In the Manage roles window, you will see three tabs:

Roles – this is where you give a name that describes the role you want to create.

Tables – this contains all the tables in your data model. From here, you can select the column that the DAX expression will filter.

Table filter DAX expression – this is where you write the DAX expression that will determine the section of data that is accessible to a specific role.

Step 2: Create Roles

To do this, click on the Create button. Then double click on the text box above the button and name the role. You can also click on the ellipse to rename the role.

Step 3: Select the Filter Column

The next step involves choosing the table and the column that will determine the data accessed by users under this role.

You will do this in the Tables tab. Here, you click the table (DimSalesTerritory), the Add filter, then the column in the table that you want to add the filter ([SalesTerritoryRegion]).

Since the goal is to create roles based on the country of the user viewing the data, it’s appropriate to apply a filter on the column containing the country information. Hence, the [SalesTerritoryRegion] column.

Step 4: Define the filter Condition using a DAX Expression

This is done in the Table filter DAX expression tab. The DAX expression is supposed to be straightforward. That’s because it’s expected that the bulk of the work is already done at the data modelling stage.

Once your relationships are in the right order, applying a row-level security filter expression would be an easy task.

When you complete the third step, you get a default structure of the DAX expression is meant to be. At the bottom of this section, you can also get a hint of what the DAX expression should evaluate.

You can simply delete the Value in the expression and replace it with the appropriate value.

To create multiple roles, you can repeat the same steps. When you’re done, click on the Save button.

When you use a static value such as this to apply a filter to a role, then you’ve created Static row-level security. As in this illustration, the DAX expression here isn’t dynamic and will only work when the specified column in the table equals the United States.

If you want to filter for other countries, you will have to create different roles and adapt the expression to the appropriate country. In Dynamic row-level security, this is not usually the case as you will see in the following section.

Dynamic Row Level Security

The process for implementing dynamic row-level security mirrors that of static row-level security. As mentioned previously, the distinction between the two lies in the use of DAX functions in the DAX expression.

To illustrate the application of dynamic row-level security, this sample dataset is created in Power BI using the Enter Data feature.

This visual is created from the sample data.

The cards visual to the right displays the username which is achieved by creating a measure using the USERPRINCIPALNAME function. This function returns the name of the user as their email address, for example, user@domain.com.

The other function that works similar to this is the USERNAME function. It returns the domain name and username of the current connection in the format domain-name\user-name.

Both of these functions are what make the row-level security dynamic. That is because they make it possible to access user information which can then be used to control data access.

As seen in this image, the USERPPRINCIPALNAME function is used in the DAX expression in place of a fixed value. With this solution, you wouldn’t have to create multiple roles to control the data access of multiple users.

For this to work seamlessly though, you would have to make sure your data model is properly structured and each user’s user-name/domain email address is captured in your dataset.

How to See Row Level Security in Power BI

After creating roles, you want to be sure they’re working as intended. In Power BI, you can do this in a few clicks.

To be sure the roles you’ve created are working, you can test them by clicking on the View as button in the same Modeling tab.

In the next window, select the role you want to view and click on the OK button. You can also select multiple roles if you so wish.

Now, you will see the data just a user assigned to the United States role will see it.

At this point, you’ve only created the role. The next step is to assign users to the role and to do this, you’ll need to publish the data to the Power BI service.

To publish, go to the Home ribbon, click on the Publish button, select the workspace where you want to store the file and click on the Select button.

After publishing to the Power BI service, locate the dataset copy of the file.

Select the Security option from the contextual menu. The Security option is only available on the dataset file, that’s why it’s important that you locate the dataset and not the report copy of the published file. If you don’t see the Security option, you’re probably clicking on the Report copy.

In the next window, you will see the roles already created in the Power BI desktop. What you now do is add members to each role. Once done, click on the Save button. The number of members added to a role will be displayed inside the parenthesis beside the role name.

When adding members to a role, you should use the email address used to sign up with the Power BI service.

With this setup, you can easily control who sees what in a report.

You can also check how the role works by selecting the Test as role option. This option comes up when you click on the ellipses on the role name.

When you click on the Test as role button, the report will show data only available for the selected role.

How to Change Row Level Security in Power BI

Let’s say you want to change a few things around, perhaps change the column that’s been filtered or reassign a user from one role to another. These are changes that you can easily apply following the steps outlined earlier.

To change the columns filtered by the DAX statement, select the column and click on the Clear table filter option. Then you can proceed to add a filter on a column in another table.

If you’re creating a new role that uses the same logic as a previously created role, you can simply duplicate an existing role. This way, you wouldn’t need to create from scratch as all you have to do is change the role name and update the DAX statement appropriately.

Additionally, you can apply filters to more than one column in a role. The process involved in doing this is the same as when applying a filter to a single column.

When these changes are made on the Power BI desktop, you can simply republish the same document and select the replace option. This way, the new document is updated to reflect the changes and you can move users around different roles if necessary.

How to Disable Row Level Security in Power BI

Disabling row-level security is also a straightforward process. To disable static or dynamic row-level security, select all the roles (you can do this by holding down the Ctrl key while selecting the roles) and click on the Delete button.

Click on the Yes, delete prompt that shows up in the next window. At this point, the roles will be cleared, and you can hit the Save button to lock in the changes and exit the window.

Conclusions

Row-level security in Power BI, whether it’s static or dynamic, empowers you to finely control data access and ensure the confidentiality and relevance of information based on roles.

While the implementation steps may be similar, the choice between static and dynamic row-level security depends on the specific requirements of your data model.

This feature not only enhances data security but also enables the creation of tailored, insightful visualizations, ultimately contributing to more informed decision-making for users of the data.