We discussed the importance of learning Row Level Security in Power BI Trainings in Sydney and Melbourne in our last blog. This blog is in continuation to Static Row Level Security discussed last week.
Row Level Security (RLS) is a feature that restricts the access to data to a specific group of users. Row level Security allows you to give different levels of access to different users for the same report.
There are three ways of implementing Row Level Security in Power BI:
Static Row Level Security
Static Row Level Security restricts the access of report to the audience by defining a logic for the roles inside the Power BI Model.
Here’s a complete guide to Static Row Level Security: https://powerbitraining.com.au/static-row-level-security-in-power-bi-power-bi-training-in-sydney-and-melbourne/
Dynamic Row Level Security
Dynamic Row Level security refers to the use of DAX functions in order to restrict the access of your audience for the same report. Dynamic security changes as per the login credentials.
Row Level Security using URL
Row Level Security appends parameters to the report URL in order to limit the access to underlying data.
Getting Started
The data model used for this blog consists of three tables:
- Employee: This table contains details about the Employees of AMZ Consulting.
- Office: This table contains the details about the office sites of AMZ consulting.
- Sales: This table is a fact table and contains data about sales contributions made by each employee.
The Report on which you will practice Row Level Filtering shows the Employee Performance for AMZ Consulting. This is shown in the image below.
Creating Dynamic Row Level Security
Assigning roles to every person of the team is cumbersome and time consuming. Dynamic row level security solves this issue by filtering the data based on login credentials of users.
To create Dynamic Row level Secuirty:
1.Create a Measure in the Employees table using the DAX function USERPRINCIPALNAME().
This function provides row level security dynamically based on the login email of the user in Power BI Service.
2.Create a new role Dynamic.
For getting started on creating Roles: https://powerbitraining.com.au/static-row-level-security-in-power-bi-power-bi-training-in-sydney-and-melbourne/
3.From the tables section select Employees.
4.Click on the three dots at the right of Employees and add a field Email_Ids.
5.In the right Table filter DAX expression section, replace values with “USERPRINCIPALNAME()”
6.Click on Save and Publish the report to Power BI Service.
Dynamic row level security cannot be tested in desktop. We will test this in Power BI Service later in this chapter.
Creating Dynamic Row Level Security based on Reporting Lines
In this section, you will develop a Reporting Line and dynamically filter data to view the performance of your subordinates.
To create a dynamic Row Level Security:
- In the Employees table, create a new calculated column and rename it to “Manager Id”.
2.Create another calculated column called ‘Reporting Line.’
3.Create a role Reporting Line.
4.From the tables section, select Employees.
5.Write the DAX expression shown in the image below.
6.Publish the file to Power BI S
Assigning Members to Roles
In this section, we will learn how to assign members to appropriate roles.
To assign members to a role:
1.Login to your Power BI Pro account.
2.Select the workspace where you have published your exercise files.
3.Select your report under data sets and click on the three dots next to it.
4.A menu appears, click on Security. This directs you to a new page.
5.Select the Role Dynamic.
6.In the Members’ section, select members.
7.Click Save.
8.Now select Reporting Line and assign members. Using their emails, click Save.
Testing Scenarios in Power BI service
The roles assigned to the user limits their access to data. It is important that you can act as a member assigned to the role.
To test scenarios in Power BI Pro:
1.Select the Role Dynamic and click on the three dots next to it.
2.Click on Test as role.
Dynamic filters the data set for the user whose email has been used to login to Power BI service.
Now, test the role of Reporting Line by following the same steps.
In this role, the employee “Muneeba Sirshar” has access to the perforamance details of employees reporting to her.
Conclusion:
Row Level Security in Power BI is used to limit the access of data to specific users. Row level security in Power BI works on the principle of creating roles and allowing the access accordig to the roles. There are three types of Row Level Secuirty options in Power BI. In this blog post, dynamic row level secuirty has been discussed.
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
