Row-level security is a powerful data governance capability across many business intelligence platforms, and Power BI is no exception. Learning how to implement row-level security is critical for any Power BI developer hoping to add an extra layer of security to their reports and datasets.
In this blog, we will provide a high-level summary of row-level security, why it’s important for your team, when to use it, and how to set it up in Power BI.
What is Row-Level Security, and Why Does it Matter?
Row-level security (RLS) is a type of data governance that allows developers and admins to limit the data an end-user has visibility to within a Power BI report and/or dataset based on the logic applied at the row level. You can think of RLS as a horizontal limitation applied to the dataset, whereas object-level security is applied to columns or tables.
To implement RLS, the developer provides Power BI instructions, or logic, that becomes the rules for how data will be filtered out for the end users.
RLS is important because it is a key component of many organization’s data security strategy. By implementing RLS, your organization can ensure that each Power BI user only has visibility to the data that is appropriate for them to see. Without RLS end users could have visibility into data that they shouldn’t have access to (e.g. payroll or sales data).
What are some Row-Level Security Use Cases?
Below are several examples of how RLS can limit access to data and how many organizations apply RLS.
Location Based RLS
An organization wants specific subsets of users only to see information about locations that are specific to them (City/State/Region/Country, etc.)
Ex: We are the VP of Finance for the Western region and should only see data for that specific area.
Employee Based RLS
A developer wants users only to see information that they are tied to or responsible for (Email/ Name)
Ex: We are a Business Development Representative and should only see data for CRM pipeline opportunities that fall under my name.
Business Line Based RLS
Users should only see information within a specific business line (Product/Service/Department).
Ex: We are a Buyer of men’s clothing and should only see data related to my business domain/division.
What are the Different Types of Row-Level Security?
Two types of RLS can be implemented within a Power BI report and/or dataset: Static RLS and Dynamic RLS.
Static RLS is the simplest of the two and requires a Power BI developer to define security-based logic manually within the PBIX file (explained later).Â
Use Static RLS if:
You need to restrict data visibility for a specific user group that requires the same level of information (e.g., a regional sales team needs to view data for their region).
Your report has fewer users and requires fewer security groups.
Your report’s security logic is high-level and straightforward (e.g., by user, region, or company)
Your user security requirements don’t change (e.g., security groups)
Users are not added or removed frequently
Pros: Easy to set up and implement with minimal involvement from IT.
Cons: High level of maintenance if it needs to be updated, not reusable, and is manual.
Dynamic RLS is more complex and requires logic to be defined within the PBIX file and the data model using relationships (explained later).
Use Dynamic RLS if:
You need to restrict data visibility for a specific user group that requires different levels of information (e.g., the regional sales team needs to view data for their specific regional territory(s)).
Your report has a large number of users and requires lots of security groups.
Your user security requirements often change (security groups and members of the security groups change frequently).
The RLS logic in the report is very complex.
Users will be added or removed frequently.
Pros: The solution is built to be reusable, requiring minimal maintenance, and operates in an automated manner.
Cons: time-consuming to set up, requiring dimension tables, and greater involvement from IT.
How to Set Up Row-Level Security in Power BI
Let’s start with an example. Imagine a rapidly growing burrito chain in Minnesota, Ohio, and New York hired phData (a premier data and analytic consulting firm) to develop a Power BI dashboard for the managers of its restaurants.
The dashboard aims to share sales information so managers can better understand trends and make more informed business decisions. Corporate wants each restaurant manager to see only the sales numbers for stores within their state.
Static RLS
Directions:
With the Power BI report open, select Modeling on the top ribbon and then Manage Roles.
Select Create → add a name for the new role (‘Minnesota,’ ‘Ohio’, ‘New York’).
Under Tables → select the vertical ellipses (…). Then select Add filter → choose the field from the table you want to filter by (‘State’).
Add the logic you want to filter the field by (‘State = MN,’ ‘State = OH,’ ‘State = NY’) →. Select the check mark (Verify DAX Expression), then select Save.
Validate: Select Modeling → Select View As → Select the role you just set up → Select Ok → Then verify that the data is filtering as expected.
Save and publish the Power BI Report to a workspace.
Navigate to the Power BI Workspace.
On the dataset (in Orange) → select the vertical ellipses (…) (More options) → Select Security.
Add users to the roles (‘Minnesota,’ ‘Ohio,’ ‘New York’) you established → Select Save
Select the vertical ellipses (…) next to the security group (More options) → Select Test as role → ensure that the RLS is working as expected.
Dynamic RLS:
To implement dynamic RLS, similar steps to the ones above are performed. However, you will use a dimension table (dim_user in the example) to identify the location(s) that each manager is responsible for.
The benefit of using a dimension table is that it will always reflect the most current data and won’t require manual security group changes if appropriately updated in the source. The dimension table in the example is related to the fact tab via fct_sales, with the dimension table filtering the fact table.
Directions
Import the dimension table to the Power BI report.
Create a relationship between the dimension and fact tables (with the dimension table filtering the fact table).
On the top ribbon, select Modeling  → then Manage Roles.
Select Create → add a name to identify your new role (‘Users’).
Under Tables → select the vertical ellipses (…) → select Add filter and then choose the field from the table you want to filter by (‘user_email’)
Add the logic for what the field should filter by (‘user_email = userprincipalname()’) → select the check mark (Verify DAX Expression) → select Save.
Validate: Select Modeling → select View As → select the role you just set up → select Ok → verify that the data is filtering as expected.
Save and publish the Power BI report to a Power BI Workspace.
Navigate to the Power BI Workspace.
On the dataset (in Orange) → Select the vertical ellipses (…) (More Options) → select Security.
Add users to the role (‘Users’) you established → select Save.
Select the vertical ellipses (…) next to the security group (More options) → select Test as role → ensure that the RLS is working as expected.
Setting up and Editing RLS From Power BI Service Directly in the Workspace
Microsoft recently released an update that allows those with Contributor, Member, or Admin access to edit the data model for a Power BI dataset within the cloud (Power BI Service). Before this update, developers needed to either redownload the PBIX file or use a third-party tool like Tabular Editor.
This is a feature that needs to be enabled in the Workspace settings. Open up the General page of the workspace settings and ensure that the box next to Users can edit data models in Power BI service  under Data model settings is checked.
After enabling this setting, follow the directions below:
Open the report directly in the workspace, click Edit, and then Open data model on the top bar.
In the new window, click Manage roles.
For Static RLS, start from Step 2 in the previous section, and for Dynamic RLS, start from Step 4.
Important: All changes when editing the data model in Power BI Service are automatically saved. Use this feature with caution.
Closing
RLS can seem like a complex and, at times, even daunting task. We hope that you now understand what row-level security is, why it’s important, and how you can implement it in several different ways. As Power BI developers, we are responsible for safeguarding sensitive data and ensuring that it is used appropriately.
If you need additional help or are curious about leveraging Power BI better, contact our team of experts today for help, guidance, and best practices!
This post was originally written by Ryan Lurquin.
