IMPLEMENT ROW-LEVEL SECURITY
Row-level security (RLS) allows you to create a single or a set of reports that targets data for a
specific user. In this module, you'll learn how to implement RLS by using either a static or dynamic
method and how Microsoft Power BI simplifies testing RLS in Power BI Desktop and Power BI service.
Learning objectives
In this module, you will:
• Configure row-level security by using a static method.
• Configure row-level security by using a dynamic method.
INTRODUCTION
Microsoft Power BI can help you secure reports and workspaces by allowing you to share them to
active directory users and groups. You can also share a single report but have users see different data
according to their job role.
For example, consider a scenario where you work for Tailspin Traders. You use the following table to
track sales.
You can also use the following table for employee information.
The following table shows the list of products.
You want to make one report where employees in a specific department can only see the sales for
that department. For instance, Maria Cameron works in the Game department and should only see
the sales from that department, not sales from the Sports, Clothing, or Automotive departments.
This data is organized in a star schema. The Sales table contains all attributes of a fact table, while
employees and products are dimension tables. The semantic model is shown in the following
screenshot.
Two ways of implementing row-level security in Power BI are the static method and the dynamic
method.
Row-level security (RLS) uses a DAX filter as the core logic mechanism. This module will demonstrate
how you can implement row-level security in Power BI by using DAX to ensure that only the
appropriate person can view the appropriate records.
CONFIGURE ROW-LEVEL SECURITY WITH THE STATIC METHOD
The static method in row-level security (RLS) uses a fixed value in the DAX filter, while the dynamic
method uses a DAX function.
RLS involves several configuration steps, which should be completed in the following order:
1. Create a report in Microsoft Power BI Desktop.
1. Import the data.
2. Confirm the semantic model between both tables.
3. Create the report visuals.
2. Create RLS roles in Power BI Desktop by using DAX.
3. Test the roles in Power BI Desktop.
4. Deploy the report to Microsoft Power BI service.
5. Add members to the role in Power BI service.
6. Test the roles in Power BI service.
Create a report in Power BI Desktop
Follow the typical steps to create a report in Power BI Desktop. Use Microsoft Power Query to
retrieve and clean the data. Then, confirm that the relationship exists between the two tables by
using the Modeling tab; it should be a one-to-many relationship on the empID column.
Your next step is to create a Power BI report.
Notice how the preceding table has rows for all sales, including all departments. You will be limiting
visibility so that only employees of a specific department can see their own sales.
Create RLS roles in Power BI Desktop
To create RLS roles in Power BI Desktop, select the Modeling tab, and then select Manage Roles.
On the Manage roles page, select Create.
Power BI row-level security (RLS) uses DAX to control who can see which data. Consider it as always
adding another filter to the appropriate users, regardless of the filters, slicers, or interactions that the
users choose on a Power BI report.
On the Manage roles page, create a role for each department and then add a DAX expression to it.
For instance, you can create a role called Game and then add the DAX expression [department] =
"Game". Then, whenever a member of that role interacts with the report, Power BI will add that
filter to their interactions, thus limiting what they see.
A fixed value is used in the filter on the right side of the equal sign (in this case, "Game"). The
intention is that, if you ever need to add a category, you will need to create a new role with a new
value in the DAX expression.
Notice how the DAX filter is applied on the dimension table. Row-level security performs better when
the data is organized in a star schema. Apply the DAX filter to a dimension table, as was done with
the Products table.
The DAX filter is applied to every interaction, slicer, and filter that the user applies. If you have a DAX
filter that performs poorly, the user experience will be negatively impacted. Therefore, keep the DAX
filter as simple as possible.
Test the roles in Power BI Desktop
You can validate that the filter is working by selecting the Modeling tab and then selecting View as
Roles.
In the View as roles window, select the Game role. The report now renders as if you were in that
role, and you will only see the records that are included in the Game department.
You can undo this filter by selecting View as roles again and then selecting None.
Deploy the report to Power BI service
You can deploy the report to Power BI service by selecting the Publish button on the Home tab and
then selecting a workspace.
Add members to the role in Power BI service
To add members to the role in Power BI service, go to your workspace in Power BI service. Find the
semantic model that you created with the same name as your report. Select the ellipsis (...) button
and then select Security.
In the Row-Level Security screen, you can add Microsoft Entra ID users and security groups to the
security role. When members are added to this role, the DAX filter that you previously defined will be
applied to them. If members are not added to the role, but they have access to the report, RLS will
not apply to them. You can add the three people in the Game department to the Game role. Now,
when those members sign in, they will only see the report with data that applies to them.
Test the roles in Power BI service
You can test the roles inside Power BI service by selecting the ellipsis (...) next to the Game role on
the Row-Level Security screen and then selecting Test as role.
This selection will display the report as if you were a member of the role in Power BI service.
And there you have it! We've successfully implemented row-level security in Power BI.
CONFIGURE ROW-LEVEL SECURITY WITH THE DYNAMIC METHOD
You can set up row-level security only once, without the need to continue maintaining it dynamically.
As the admin at Tailwind Traders, you want Power BI row-level security to only show sales to the
person who made them. In this example, Russel King has made four sales. When viewing your report,
Russel should only see the sales that he's responsible for and no other sales. You can configure row-
level security exactly the way you configured it previously, with only a single change. Instead of
creating four roles, you only need to create one role. The DAX filter for that role would look similar to
the following image.
Notice that instead of the fixed string, such as Game or Clothing, this uses a DAX function in the row-
level security filter. The userprincipalname() function will compare the email address from the
Employees table with the email that the user entered when signing in to Power BI service. If Russel
King uses the email address [email protected] to sign in to Power BI service, the system
will compare that value to the email address in the Employees table. Assuming that a relationship
has been created between Employees and Sales, Russel will only see his four sales.
CHECK YOUR KNOWLEDGE
Answer the following questions to see what you've learned.
1. Which function will tell you the username of the person who is signed in to Power BI service?
USERPRINCIPALNAME(). Correct: The USERPRINCIPALNAME() function will tell you which user is
signed in to view a report.
LOOKUPVALUE()
USEROBJECTID()
2. Where can you test RLS by using different security roles?
Power BI Desktop only
Power BI service only
Both Power BI Desktop and Power BI service. Correct: You can use Power BI Desktop and Power BI
service to test RLS.
SUMMARY
This module described row-level security (RLS), the ability in Power BI to limit what a user sees on a
specific report. RLS targets the data to a specific user, for instance, only allowing a manager to see
the salary of their direct reports. RLS is implemented with a combination of Power BI Desktop and
Power BI service. To implement RLS, you can create a DAX formula that restricts their data access,
which makes RLS versatile. You can use DAX to indicate that someone can only see records in the
United States or sales transactions that are below a certain dollar amount. This programmatic
approach means that RLS can be used in a variety of solutions. After you have created the DAX
formula in a specific security role, you can deploy the report and then add users to that role. RLS is
an effortlessly implemented, powerful security feature of Power BI.
