What is RLS in Power BI?

Row-Level Security, or RLS, is one of Power BI's most powerful features for controlling who sees what in your reports. Instead of building separate reports for different teams or regions, you can use one master report and simply filter the data based on the user's credentials. This article will walk you through what RLS is, why it matters, and how to set it up in both static and dynamic ways.

What is Row-Level Security (RLS)?

Row-Level Security is a data governance feature that restricts data access for specific users at the data row level. In plain English, it means you can define rules that filter data so that people only see the information they’re authorized to see, all within the same dashboard.

Imagine a global sales dashboard. Without RLS, everyone from a regional sales rep in France to the VP of Sales in New York would see the exact same data. This isn't just inefficient, it's a security risk. With RLS, you can set it up so that:

• The French Sales Manager only sees sales data for France.
• The European Regional Manager sees sales across all European countries.
• The VP of Sales sees all global data without any filters.

All three people are viewing the exact same Power BI report, but RLS automatically serves each of them a different slice of the underlying data based on who they are. This eliminates the need to maintain dozens of separate, almost identical reports.

Why Is RLS So Important?

Implementing RLS isn't just a "nice-to-have" feature, it provides significant, tangible benefits for any organization that takes data seriously.

Enhanced Data Security and Compliance

The most obvious benefit is security. You can ensure sensitive information, like employee salaries, customer personal data, or regional profit margins, is only accessible to authorized personnel. This is absolutely critical for complying with data privacy regulations like GDPR or HIPAA.

Simplified Report Management

Think about the alternative to RLS. You'd have to create and manage a separate PBIX file for each region, role, or user group. A new report for the German team, another for the UK team, one for French marketing, another for French sales... it quickly becomes an unmanageable mess. RLS allows you to consolidate everything into a single, clean master report, saving countless hours on development and maintenance.

Improved User Experience

Reports overwhelmed with irrelevant data are confusing and difficult to use. By filtering a report to show only what’s relevant to a user's role, you provide a cleaner, more focused experience. A sales rep can immediately see their performance metrics without having to manually filter through a massive company-wide dataset, enabling them to find insights faster.

Built-in Scalability

As your company grows, a well-implemented RLS strategy can scale effortlessly. When a new sales manager joins the North American team, you don't need to build them a new report. If you've set up dynamic RLS, you might only need to add their email to a list, and their permissions are instantly configured.

How to Implement Static RLS in Power BI

Static RLS is the most straightforward method. You create a new rule for each role and manually type in the filter. This method works well when you have a small and relatively fixed number of roles that don't change very often (e.g., a few specific regions or departments).

Let's walk through an example using a simple sales table with a 'Region' column.

Step 1: Create Roles in Power BI Desktop

First, you need to define the roles within your Power BI report.

1. On the Power BI Desktop ribbon, click the Modeling tab.
2. Click on Manage roles.
3. In the "Manage roles" window, click Create.
4. Name your first role. Let's call it 'USA_Sales'.
5. Create another role and name it 'Canada_Sales'.

Step 2: Define DAX Filter Expressions

Now, let's tell Power BI how to filter the data for each role. You do this with a DAX (Data Analysis Expressions) formula, but don't worry - it’s very simple for static RLS.

1. In the "Manage roles" window, select the 'USA_Sales' role.
2. Find your sales data table (let's say it's called 'SalesData') in the "Tables" section and click the ellipsis (...).
3. Select Add filter and choose the column you want to filter. In our case, it's [Region].
4. In the "Table filter DAX expression" box, you’ll write the rule. For the USA role, the rule is:
5. Now, select the 'Canada_Sales' role and repeat the process for the SalesData table. This time, the expression will be:
6. Click Save when you're done.

Step 3: Test Your Roles

Before publishing, it's critical to make sure your roles work as expected.

1. On the Modeling tab, click on View as.
2. In the "View as roles" window, check the box for the role you want to test, for example, 'USA_Sales'.
3. Click OK.

Your report will now be filtered as if you were logged in as a member of the USA Sales team. You should only see data where the 'Region' column is "USA." You'll see a yellow bar at the top of your report reminding you that you are viewing the report in a specific role. To stop viewing, just click "Stop viewing" on that yellow bar.

Step 4: Publish and Assign Users

Once you are confident the roles are working, you are ready to publish the report to the Power BI Service and assign users.

1. Publish your report from Power BI Desktop.
2. In the Power BI Service (app.powerbi.com), navigate to the workspace where you published the report.
3. Find your dataset, click the ellipsis (...), and select Security.
4. You'll see the roles you created ('USA_Sales' and 'Canada_Sales'). Select a role, and then type the email address or security group of the people you want to add to that role.
5. Click Add and then Save.

That's it! Now, when a user assigned to the 'USA_Sales' role opens the report, they'll only see USA data.

How to Implement Dynamic RLS in Power BI

Static RLS is fine for small teams, but it quickly becomes a pain to manage if you have tens or hundreds of different access levels. This is where Dynamic RLS shines. It uses a single role and an extra table to dynamically filter the data based on the user's logged-in credentials.

The key here is using the USERPRINCIPALNAME() function in DAX, which returns the email address of the current user.

Step 1: Create a User-Role Mapping Table

First, you need a table that maps users to the data they're allowed to see. This table should contain at least two columns: one with users' email addresses and one with the value you want to filter by (e.g., the region).

Let's call our table Users:

Notice that the EMEA VP is mapped to both Germany and France. This setup can handle many-to-many relationships elegantly.

Import or create this table in your data model.

Step 2: Create a Table Relationship

Next, you need to create a relationship between your new Users table and your SalesData table. Drag the Region column from the Users table to the Region column in the SalesData table.

In the relationship settings, make sure arrows are applied with a single direction, filtering the SalesData table.

Important! Go to Security/Users and make sure "apply security filter in both directions" is checked.

Step 3: Create a Single Dynamic Role

Now back to the roles manager.

1. Go back into Modeling > Manage roles in Power BI Desktop.
2. Create just one role - let’s name it UserFilter.
3. Select this role, find your Users table, and create a filter on the email column. This is where the magic happens. Here's your DAX rule:
4. Click Save.

This simple expression filters the Users table for the user who is looking at the report. It will only return the rows of the Users table for their respective logins. With the relationships set up in Step 2, that will propagate to your data tables, filtering them to only include regions, etc., they are assigned to.

Step 4: Test, Publish and Assign

The process of publishing is very similar to the static RLS workflow, but the testing step is different:

1. In Power BI Desktop, use View as in the roles manager. Select your UserFilter role, then check the box for Other user and enter the email of someone who would normally view the data - for example, manager.usa@company.com. This will filter the entire dataset as if this user is logged in. This gives you confidence that everything is working properly before deployment.
2. Publish the report.
3. Navigate to the dataset's security settings in the Power BI Service.
4. Assign all your users to the single UserFilter role. Now, there is no need to manage USA, Canada, and other groups separately. Any user added to this role will automatically get their data view based on entries in the Users table.

Static vs. Dynamic RLS: Which One Should You Choose?

Choosing between static and dynamic RLS usually comes down to your data size and management needs. Here’s a quick comparison:

Static RLS

• Pros: Very simple to set up for a few, well-defined roles.
• Cons: Becomes cumbersome to manage as the number of roles grows. Requires republishing the report to add new roles.

Dynamic RLS

• Pros: Highly scalable, can be used for thousands of users with the same single role. User permissions can be managed in one place - the user mapping table - without touching the report file.
• Cons: Slightly more complex initial setup, as it requires understanding relationships and the user mapping table.

My Recommendation: For 99% of all workloads, dynamic RLS is the way to go. It's more robust and will save you tons of time in the long run.

Final Thoughts

RLS is a fundamental feature in Power BI that allows you to provide a secure, filtered experience for your users while drastically simplifying your report management. Whether you choose static or dynamic RLS, implementing RLS will alleviate your efforts from static hardcoded dashboards to a more interactive set of tools that empowers each member of your team.

The core goal of making data accessible and actionable is what drives technologies like Power BI and Graphed. While Power BI itself requires a bit of manual setup - like configuring DAX rules - you can use Graphed to make this process more seamless. With Graphed, you can optimize resources and effectively handle complex parts that can streamline your management of relevant data using real-time analytics.