Row-level security (RLS) is one of the approaches used in Power BI to control access to data reports. Row-level security is applied to rows in a table and, thus, a horizontal limitation that controls data visibility. There are two types of RLS: static RLS and Dynamic RLS. In this article, we shall focus on dynamic RLS and how to implement it in Power BI.
Dynamic Row-level security
When using dynamic RLS, users’ access to different dashboards and reports depends on their login credentials. This method of Row-level security is more complex than static RLS but more secure. Using dynamic RLS, it is possible to assign one user multiple roles or multiple roles to multiple users.
How to implement dynamic row-level security in Power BI
Dynamic row-level security works with any data source with which Power Bi is compatible, such as an SQL server.
On the Power BI desktop, let’s start by creating two tables: transactions and sales reps, as shown below.
In our examples, the sales rep handles every transaction.
To create a table, navigate the desktop to the home tab and click on add data.
Create a table with several columns (3) and label it sales Rep; kindly ensure that the data in the username column is extracted from actual Power BI accounts that you wish to secure.
Create a second table and label it transactions and then Load it.
From the table above, it is evident that the sales rep is responsible for each transaction. After loading both tables, you can check the relationships between the sales rep and transactions.
Creating a Report
You can use a table visualization to create the report, displaying the date, amount, and name. Our aim is to ensure users will only have access to the section of the report assigned to them.
You can use the USERPRINICIPLENAME() DAX function and check the logged-in user. This allows you to view all users currently logged in and the part of the report each is accessing.
In Power BI, assigning users roles after a report is published can be done in Power Service.
Navigate where the report is published and select security. Hovering over the data selection and clicking on the ellipses to view the available options.
Added new users on the security tab the user will have access to the data sets assigned to their usernames.
Adding users in Power BI
In Power BI, all new users can only be added before the report is published, by accessing the Power BI desktop’s modeling tab and selecting managing roles.
- On managing roles, click
- Assign a unique name to the role.
- Find the table and select it.
- Select the ellipses on the table, apply a filter, and then select the column name you wish to utilize.
In the view above, replace the value in “username with USERPRINCIPLENAME() and then verify the DAX expression before saving to apply for the new role, as shown in the image below.
Verifying new roles
Verify whether the created role is active and check the user logged in and what part of the report they have access to. Selecting the “other users” box and entering the username or email of the user you are checking. The box below “other users” is the “permissions,” which shows this user’s permissions.
Leave A Comment