Row Level Security has been a popular topic in our last Power BI training in Sydney and Melbourne. So we decided to dedicate this week’s blog to this topic. In this blog, Row Level Security in Power BI has been introduced and Static Row Level Security has been discussed in detail.
What is Row Level Security?
Row Level Security (RLS) is a feature that restricts the access of 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 types of Row Level Security options 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.
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 exercise 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 is a sample report on which you will practice Row Level Filtering. This report page shows the Employee Performance for AMZ Consulting.
3.A pop-up menu appears, click on Create.
The pop-up menu contains three sections:
- Roles: It contains a list of roles that are created.
- Tables: This sections provides you with the table options to filter data.
- Table filter DAX expression: This section defines the functionality of a role.
4.Under the Roles section, a tab appears as New Role. Rename this to “Ali”.
5.Now, Under the Tables section select the “Employee” by right clicking on it.
6.Hover over to Add filters and select the field ”[Names]”.
To create a role for a single field:
1.Replace the word “Value” with “Ali Noorani” and click Save.
Our first role “Ali” is created.
Now you will create another role with the name “Sydney”. This Role will filter the data for all the employees working in Sydney.
To create a Role “Sydney”:
2.Click on create and rename the new role to “Sydney”
3.From the tables section, select the table Office.
4.Click on the three dots next to the office and select the field City.
5.Replace the “values” word with “Sydney”.
Our second role “Sydney” is successfully created.
Writing Table Filter Expressions for Multiple Fields
Power BI allows you to filter data based on more than one fields from the same table.
OR Operator
OR operator is a logical operator that returns a value when either of the two values are True.
As an example, assume that “Muneeba Sirshar” and “Adam Smith” are two employees of AMZ consulting. Both these name values belong to the same field, Names.
A role created using OR operator will display the same report to Muneeba and Adam. Let us see how this is done.
1.Create a role and rename it to “Muneeba or Adam”.
2.From the Employees table, select the field “Names”.
3.Replace the word “values” with “Muneeba Sirshar”.
4.Now click on the three dots next to the Employees tables and add the field Names.
As a default value “&&” appears between the two field names.
5.Replace the word “values” with “Adam Smith”.
6.Finally replace “&&” with the “||”.
7.Click Save.
AND Operator
AND operator is a logical operator that returns a value when both of the values are True.
AMZ Consulting holds two office sites in Sydney city located at different Suburbs.
Now, you will create a role “Sydney – sydney city” that filters the data for the office site located in “Sydney City”.
Using AND, you can filter the data from different columns of the same table.
To create a role Sydney- sydney city:
8.Create a new role and rename it to Sydney – sydney city.
9.In the tables section, select the field City from the Office table.
10.Replace the word “Values” with “Sydney”.
11.Now click on the three dots next to the Office table again and add a field Suburb.
12.Replace the word “Values” with “Sydney City”. Click Save.
A pop-up menu appears under the header View as Roles.
Now we view the Role “Ali”:
3.Select Ali by clicking on the checkbox beside the role, click OK.
The image above shows that Ali has been provided with the access to the data that is only related to him.
4.Click on Stop Viewing to view other roles.
Now let us view the role “Muneeba or Adam”
5.Click on Modeling > View as Roles > Muneeba or Adam > OK.
Now we view the role Sydney.
Click on Stop Viewing.
6.Click on Modeling > View as Roles > Sydney > OK.
You can view the role created with the name “Sydney” in the image below.
Sydney has 2 office sites located at different suburbs. Total number of employees in Sydney can be visualized from the role Sydney.
There are total 5 employees working at two different office locations in the city Sydney.
Now let us view the data related to a particular office site located inside the Sydney City.
7.Stop viewing Sydney by clicking Stop Viewing on the top.
8.Click Modeling > View as Roles > Sydney – Sydney City> OK.
You can see that only 2 of the total 5 employees work at the office located at Sydney City.
9.Click on Stop Viewing.
Viewing Multiple Roles
Power BI allows you to view as multiple roles at the same time.
To view as two or more roles together:
10.Click on the Modeling tab in the ribbon.
11.From the security section, select View as Roles.
A pop up menu appears under the header View as Roles
12.Select Ali by selecting the check box next to Ali.
13.Select Muneeba or Adam by checking the check box next to Muneeba or Adam.
14.Click ok.
Both views can be viewed simultaneously in the above image.
Conclusion:
Row Level Security in Power BI is used to limit the access of data to certain users. There are three types of Row Level Security in Power BI: Static Row Level Security, Dynamic Row Level Security and Row Level Security using URLs. Static Row Level Security restricts the access of report to the audience by defining a logic for the roles inside the Power BI Model. In this blog post, Static Row Level Security has been discussed in detail.
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:
