One of the most efficient and practical methods to restrict data views throughout an organization is to use Dynamic Row-Level Security (DRLS). DRLS uses DAX methods to filter the dataset depending on the Power BI service user’s log-in information. This enables Power BI report authors to quickly construct filtered data views without having to deal with the headaches of setting up various security roles in the model and maintaining user assignments to these roles.

This article will tell you all about benefits of dynamic row level security in power bi. First we need  to understand the basic difference between Static RLS & Dynamic RLS.

Static RLS implies that you define security logic inside a Power BI file (PBIX), and you must open the PBIX file, make the modification, save the file, and publish it again for each update.

The term “dynamic RLS” refers to the fact that the security logic is defined within the data model (tables, their relationships, etc.). All you have to do to update the logic is add/edit/delete records in the tables.

The below table will help you to understand the concept better.

Static Row Level Security	Dynamic Row Level Security
Static RLS signifies that you define security logic inside a Power  BI file (PBIX), and you must open the PBIX file, make the  modification, save the file, and publish it again for each update.	The term “dynamic RLS” refers to the fact that the security logic is  defined within the data model (tables, their relationships, etc). To  update the logic, simply add, amend, or delete records in the tables.
Static row-level security is simple to build, but it would be a   nightmare to manage if you have thousands of jobs.	In such cases, Dynamic Row-Level Security is the solution.

 

In addition to above, check this video to understand more about Row level security in Power BI.

 

How to implement DRLS?

• Begin by retrieving the USER ID from the Users table.
• Select Columns of Region ID from Table Returned and then filter the user region table based on User ID.
• Create a role that filters the region table using all of the identified Region IDs.

1.Users Table

You’ll need a database of all users in order to get Dynamic Row-Level Security to function. This table must contain all users, as well as a field that contains their Power BI report login id.

If your report is housed on the Power BI service, their login id is the email address they are using to log in. If the report is stored on the Power BI report server, the login id is the network account used to access the server.

2. Roles Table

A table for roles is also required. Keep in mind that roles are dynamically defined within the data model. Each different access level has its own row in the roles table.

It’s worth noting that a separate table for Roles isn’t always necessary. Your user table might also be used as a Roles table. In what instances might you inquire? There is no need for a separate Roles table if each user has only one role. Your Roles table will be a subset of your user table.

3. The Users Table to Filter the Roles Table

If you expect each role to have numerous users assigned to it, you could use a one-to-many connection to filter the roles table from the user table. Users and Roles table on the other hand, frequently form a many-to-many relationship.

4. Role Table Should Filter Other Tables

It’s critical that the role table filters the data model’s other tables. A relationship is used to filter data between Power BI tables. From the roles table, you’ll need a relationship with an appropriate direction to the other tables in the data model.

5. DAX Filters

The DAX expression you create in the role of a filter is another crucial component of dynamic RLS. This DAX expression is used to retrieve the user’s login id and filter the USERS table in your model. The UserName(), UserPrincipalName(), and CustomData() procedures are frequently used in DAX expressions to retrieve user information.

Using Dynamic Row-Level Security with Organizational Hierarchies

Every company has an organizational hierarchy, in many scenarios employees need to be authorized to their data records only, and to the data of people whom they are managing. This is another one of benefits of dynamic row level security. Here is how to use:

• User Table

The following is a sample of the data in the User table.

As you can see, there are two key columns: the employee’s ID and the Manager’s ID, which refers to the manager’s record.

• Sales Transaction Table

The transactions table may contain one or more sales transactions for each employee.

• Sample Report

It contains all employee records as well as the organizational hierarchy.

• Path Functions in DAX

One of the most typical approaches to provide row-level security in this circumstance is to use Path functions in DAX. Path functions are sophisticated functions that use an ID and Parent ID structure to go across an unknown degree of hierarchy. The structure of your data table is normally built around two columns: ID and Manager ID, as shown below.

1. Path() – This function traverses an ID and parent ID structure, revealing the entire hierarchical route in a string type delimited format. To utilize this function, simply add the following expression to a calculated column in the user table; this function will return the entire hierarchy path as a delimited text value. A vertical line (|) separates the ids of each employee in the path in this text.
2. PathItem() – The function returns the path’s individual item. You can utilize PathItem to find out who the level 1 manager, level 2 manager, or level 3 manager is. This means that calculated columns can be created at any level of the organization’s hierarchy.
3. PathContains() – PathContains is now the most significant function in this post. PathContains will determine whether or not an ID exists in the path. To provide row-level security, you’ll need to use this function. All you need to know is the person’s ID who is logged in.

Find out the ID of person Logged in

In DAX, you may use a Filter function and an Iterator function to determine who logged into the system. The filter function was used to filter the user using the PrincipalUserName() function, and the Iterator function was used to retrieve the ID of the remaining record.

The following is an example of how the Filter Function was used to retrieve the logged-in user’s record. After you’ve located the current user’s record, you can use MaxX or MinX to determine that user’s ID.

Furthermore, you can use this ID in PathContains functions to see if the user’s ID is present in a path. This logic must be added to the User database as a role. This will examine the entire organization hierarchy to determine whether any records in the user table have USER ID in their Path field.

As a result, if you move to that account, you’ll only view logged-in users and their information. You can use different functions and expressions to determine the ID of the current record if you want to utilize another approach of implementing logic.

A Simple Pattern for Dynamic Row-Level Security in Power BI

Let’s put together an RLS using two steps: one calculated column and one measure that describes each person’s function.

1) Employees table query

Keep it simple: if applicable, assign a supervisor to each employee. The data is given as follows in a table:

2) Get dynamic RLS in two steps

The first step is to obtain the table hierarchy by using the path function to a DAX computed column in the employee’s table. The second step entails a little more fiddling. Essentially, we want the Power BI Service to identify

1. who is connected
2. return their employee ID
3. show only the rows where their ID appears in the Hierarchy field.

The answer is to use the following DAX expression in Power BI Desktop’s Manage roles editor.

With:

1. The function USERPRINCIPALNAME(). 
2. The LOOKUPVALUE () method will provide his employee ID “A002” once we match it to his email address, and
3. The PATHCONTAINS () function will return TRUE for every row where his “A002” ID is contained in the Hierarchy field.

As a result, the employee table is filtered to only include persons who are supervised by Ankit, and he will only see reports for the employees he supervises. 

Configuration of the Security for DRLS in the Power BI Service

In the Power BI Service, set the security for DRLS.

• Go to the App Workspace where you uploaded the PBIX file and log into the Power BI Service.
• Go to Datasets, then click on the three dots and select Security.
• After that, save.
• Now, when a user logs in, they will only see their own data view.
• Ideally, the data should be provided through an App from the App Workspace. 

Validating & Publishing in DRLS in Power BI

After you’ve developed your roles, run them using Power BI Desktop to see how they perform.

1. Select View as from the Modeling tab. You’ll see the roles you’ve established in the View as roles pane.

2. Choose a position you’ve created, then click OK to apply it. The data is rendered relevant for that role in the report.

3. You can alternatively choose another user and provide a specific user.

Because the Power BI service and Power BI Report Server use the User Principal Name (UPN), it’s best to provide it.

1. Other user only displays different results in Power BI Desktop if you’re utilizing dynamic security depending on your DAX expressions.
2. Click OK.

The report is generated based on what the user is able to see.

Conclusion

To conclude, the dynamic RLS technique is based on the user’s email address, and it restricts the dataset to that user/group of users. Dynamic row level security benefits are countless, and this technique significantly minimizes the amount of Power BI reports and security roles that we must generate, as well as the management of the groups allocated to security requirements once the report is published to Power BI.