Dynamic Row Level Security in Power BI

Using dynamic role-level security, you can restrict users’ access to reports and dashboards based on their login credentials. 

Row-level security is often abbreviated as RLS!

This is one of Power BI’s more advanced features that is covered in our Power BI classes.

What Is Dynamic RLS?

Row-level security allows users to see data from the same Power BI content differently.

You only assign one user to a single role using static role-level security. For more information on static row-level security, see this article: row-level security.

What if you need to assign multiple roles to a single user or multiple roles to a group of users? 

Then it’s time to get a little more dynamic and use dynamic RLS.

Benefits of Dynamic RLS

Using DAX functions is the best way to limit data views across your organisation because DRLS filters the dataset based on the Power BI Service user’s log-in credentials.

It saves you the trouble of creating multiple roles for a single user, as you have to with static role level security.

Dynamic Row Level Security – Guided Example

We’ll use data entered directly into Power BI to demonstrate Dynamic RLS.

Dynamic security works with any data source that Power BI accepts, for example a SQL server

Let’s start with Power BI desktop and create two basic tables: 

  1. Sales Rep
  2. Transactions

Sales Rep is a database that contains information about sales representatives and sales transaction data. It’s worth noting that a sales representative handled each transaction.

To create a new table, go to Power BI Desktop’s Home tab and click Enter Data.

creating a new table in Power BI Desktop's Home tab

Create a table with three columns with data in them, name it Sales Rep, and make sure the data in the username column is from real Power BI accounts that you want to secure. Load the table.

Screenshot of creating a table with three columns with data in them

Create a second table with data in it and name it: Transactions.

Load the table.

Screenshot of creating a second table with data in it

As you can see from the sample table, each sales transaction was handled by a sales rep. 

After loading both tables, double-check the relationship is between Sales Rep (ID) and Transaction (Sales Rep).

double-checking the relationship is between Sales Rep (ID) and Transaction (Sales Rep).

  • Creating a Report

Create a table visualisation to display the date, transaction amount, and name (from the sales rep).

The simple table report below shows this. 

When a user logs in, the goal is to ensure that they only see the portion of the report assigned to them based on the Sales Rep (Username).

Screenshot of portion of the report assigned to the users logs in

  • How to see who’s currently logged in?

USERPRINCIPALNAME() is a useful DAX function that determines who is currently logged in and using the report. 

It will help you filter the report and show reports based on who is currently logged in and viewing the report or dashboard.

Create a new measure called “User” (or whatever you want) with a value of “USERPRINCIPALNAME” by right-clicking on the column containing the users’ email addresses (as shown below).

Screenshot of creating a new measure called "User"

Screenshot of creating a new measure with a value of

Press Enter on your keyboard.

  • Assigning a Role.

Click on the Modeling tab on the Ribbon Bar and then click Manage roles.

Screenshot showing how to assign a role

  1. Click on Create
  2. Assign a name to the Role.
  3. Find the Table and click on it.
  4. Select the Ellipses beside the table with your mouse.
  5. Apply a Filter

Choose the name of the Column you want to use.

Screenshot showing how to choose the name of the Column

Replace [Username] = “value” with USERPRINCIPLENAME(), verify the DAX Expression for errors and finally click Save to apply the role, as shown below. 

Screenshot showing saving the assigned role

  • Validating and Verifying A Role

Click “View As” on the Modelling tab to see if the role is active based on who is currently logged in.

Validating and Verifying A Role - Screenshot showing how to find if the role is active

Check the box “Other users”, and to the right, enter the email address of a user you want to test. 

Below that, check the box for the newly created role “Permissions”.

Validating and Verifying A Role - View as roles

Before clicking OK, here’s a look at the report.

Validating and Verifying A Role - View as roles report

After you’ve clicked OK,

Validating and Verifying A Role - Screenshot showing report of verifying a role

  • Assigning Users to roles

Within Power BI Desktop, you cannot assign users to roles after you’ve published your report.

In Power BI service, you can. 

To do this, navigate to the workspace where the Dataset or Report is published.

Select Security by hovering over the dataset and clicking the Ellipses.

Assigning Users to roles - Select Security by hovering over the dataset

Add all users to the Security tab. 

One user, myself, has been added to the role in the screenshot.

Assigning Users to roles - One user, myself, has been added to the role in the screenshot.

Adding a user here does not guarantee that they will see the report’s data. 

Remember that this security is dynamic.  

They will only see data rows assigned to their username or email if the underlying dataset contains a record for them.

Testing DRLS 

Click “Test as role” to see if the role is active on the Ellipsis next to the role name in Power BI service. 

Testing DRLS  - Screenshot showing if the role is active on the Ellipsis

You’ll be able to see reports created specifically for this role based on the currently logged-in user.

Testing DRLS - reports created based on the currently logged-in user

Conclusion

Using the DAX UserPrincipalName() function, you’ve seen how simple it is to use Dynamic Row-Level Security in Power BI

When users log in with limited access to the underlying datasets, they will see their assigned view of the reports.

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.