What is Static and Dynamic RLS in Power BI?

When you build a report in Power BI, you want your team to see the data they need to make smart decisions. The challenge is, not everyone should see everything. Your EU sales director doesn't need to see US performance data, and your marketing lead for one product line shouldn't be distracted by another's metrics. This is where Row-Level Security, or RLS, comes in. This article explains how to use both Static and Dynamic RLS in Power BI to control exactly who sees what data within the same report.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Row-Level Security (RLS) in Power BI?

Row-Level Security (RLS) is a Power BI feature that restricts data access for specific users. Instead of creating dozens of new reports for each team or manager, you can build a single master report and apply security rules that filter the data based on who is viewing it. It ensures that users only see the data rows relevant to them.

Think of it like being a national sales director at a company that operates in the United States, Canada, and Mexico. You build one comprehensive sales dashboard covering all three countries. Without RLS, if you share this dashboard, your Canadian regional manager sees the US numbers, and the Mexican manager sees Canadian deals. It's confusing at best and a data governance headache at worst.

With RLS, you can create a rule that says, "When the Canadian manager logs in, only show the rows where the 'Country' column is 'Canada'." They see the exact same dashboard as everyone else - same charts, same design - but the data is perfectly filtered for them. RLS gives you a powerful and efficient way to share sensitive data securely, all from a single source of truth. Power BI offers two main approaches to get this done: Static RLS and Dynamic RLS.

Static RLS: The Basics

Static RLS is the more straightforward approach. You create fixed security roles and manually define the filter for each one. From our previous example, you would create three separate roles: "USA_Manager," "Canada_Manager," and "Mexico_Manager." For each role, you would write a simple, static DAX rule.

• For the "USA_Manager" role, the rule would be: [Country] = "United States"
• For the "Canada_Manager" role: [Country] = "Canada"
• For the "Mexico_Manager" role: [Country] = "Mexico"

Once these roles are defined in Power BI Desktop, you publish the report to the Power BI Service. From there, you manually assign users (via their email addresses) to the appropriate roles. If a new manager for Canada joins the team, you simply add their email to the "Canada_Manager" role.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

When to Use Static RLS?

Static RLS is a great starting point and works best for simple scenarios. Consider using it when:

• You have a small number of user groups. If you only need to manage access for 2-10 distinct teams, static roles are perfectly manageable.
• Your access rules don't change often. If your organizational structure is stable and teams don't frequently shift, you won't be constantly updating roles.
• The security logic is simple. Rules based on single, fixed values like region, department, or business unit are ideal for this method.

Step-by-Step Guide to Setting Up Static RLS

Setting up Static RLS involves defining roles in Power BI Desktop and then assigning users in the Power BI Service. Here’s how to do it.

1. Go to the Modeling Tab

First, open your PBIX file in Power BI Desktop. In the top ribbon, click on the Modeling tab and then select Manage roles.

2. Create Your New Roles

The "Manage roles" window will appear. Click the Create button to add a new role. Give your role a descriptive name, like UK_Manager. Repeat this process for every role you need, such as France_Manager and Germany_Manager.

3. Define the Filter Rule

Select a role (e.g., UK_Manager). Navigate to the table you want to filter (e.g., Sales). Click the three dots (...) next to the table name and select Add filter, then choose the column you want to filter by (e.g., [Country]).

In the DAX filter expression box, write the rule to apply. For the UK_Manager, the expression would be:

[Country] = "United Kingdom"

Click the checkmark to validate the DAX expression, then do the same for the other roles you created. Once you're done, click Save.

4. Test Your Roles Locally

Before publishing, it’s essential to make sure your rules work. In the Modeling tab, click View as. Select the role you want to test (e.g., UK_Manager) and click OK. Your report will now update to show you exactly what a user assigned to that role would see. You should only see data for the United Kingdom.

5. Publish and Assign Users

Once you’ve confirmed your roles work, publish the report to the Power BI Service. After publishing, navigate to the workspace where the report is located, find your dataset, click the three dots, and select Security. Here you will see the roles you created. Select a role and simply type in the email addresses of the users you want to assign to it. That's it!

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Dynamic RLS: A Scalable Solution

While Static RLS is simple, it becomes a nightmare to manage as your organization grows. Imagine a company with 500 sales reps, each needing access to only their own data. Creating 500 static roles is simply not feasible. This is where Dynamic RLS shines.

Dynamic RLS uses a single role with a dynamic filter based on the logged-in user's identity. Instead of "hard-coding" values like "United Kingdom," you use special DAX functions - most commonly USERPRINCIPALNAME() - which returns the email address of the person currently viewing the report.

To make this work, your data model needs a table that maps users to the data dimension they are allowed to see. For example, you might have a SalesReps table with two columns: SalesRepEmail and Country. The dynamic RLS rule uses this table to find the user currently logged in and filter the Sales table accordingly based on their country.

When to Use Dynamic RLS?

Dynamic RLS offers scalability and easy maintenance, making it the preferred method for medium to large organizations. Switch to a dynamic setup when:

• You have a large number of users. If you need to manage access for dozens or hundreds of users, dynamic RLS is a must.
• User permissions change frequently. When employees are promoted, change departments, or leave the company, you can update their permissions by simply editing a row in your user lookup table without ever touching the Power BI report again.
• The security logic is complex. A dynamic setup allows for more intricate rules, like giving managers access to their entire region while individual reps can only see their own accounts.

Step-by-Step Guide to Setting Up Dynamic RLS

Setting up Dynamic RLS requires a bit more upfront work on your data model, but it pays off in long-term maintainability.

1. Prepare Your Data Model

Your data model is the key. You need a Dim (Dimension) table that contains user login credentials. It should include an "Email" column with the user's Power BI login email. This user/permission table must have a relationship with your Fact table or other Dim tables you wish to filter. For example, an Employees table with an EmailAddress column could have a relationship with the Sales table via the Region column.

2. Create a Single Generic Role

In Power BI Desktop, go to Modeling > Manage roles again. This time, create only one role. You can name it something generic like TeamMember or Viewer.

3. Write the Dynamic DAX Expression

Select your user/permissions table (e.g., the Employees table). The dynamic filter is applied here. In the DAX filter expression box, use the USERPRINCIPALNAME() function:

[EmailAddress] = USERPRINCIPALNAME()

This simple expression is incredibly powerful. It compares the EmailAddress column in your Employees table to the Power BI login of the person viewing the report. Because of the relationships in your model, filtering the Employees table will automatically filter your sales data too.

4. Test the Dynamic Role

To test a dynamic filter, go to Modeling > View as. Here, you'll need to simulate a user logging in. Check the new Viewer role and the Other user checkbox. An input box will appear where you can type in the email address of a user from your Employees table (e.g., a manager from the UK). Click OK, and the report will filter as if that person had logged in.

5. Publish and Assign Users

Finally, publish the report to the Power BI Service. Navigate to the dataset's Security settings, find your single Viewer role, and add everyone who needs filtered access to it. Power BI and your DAX rule will handle the rest, dynamically filtering the view for each person when they log in.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Static vs. Dynamic RLS: Which One is Right for You?

Struggling to decide? Here’s a quick breakdown to help you pick the right approach for your needs.

Static RLS

• Best For: Small teams, simple hierarchies, and infrequent changes.
• Pros: Very easy to set up for a few roles, the logic is crystal clear.
• Cons: Not scalable. Becomes incredibly time-consuming to manage with many roles or users.

Dynamic RLS

• Best For: Large organizations, complex security requirements, and frequent personnel changes.
• Pros: Extremely scalable once set up, user access is managed entirely in the data, not the report, highly flexible for complex scenarios.
• Cons: More complex initial setup, requires a well-structured data model and a dedicated table for user management.

Pro Tips and Best Practices

As you implement Row-Level Security, keep these best practices in mind to save yourself time and future headaches.

• Plan Your Data Model: A clean, well-structured data model with proper relationships is the backbone of RLS, especially for the dynamic approach. Invest time here upfront.
• Leverage Security Groups: In Power BI Service, instead of assigning individual users, add Azure Active Directory (AAD) security groups to your roles. User management then becomes as simple as adding or removing users from that security group in AAD.
• Always Test Thoroughly: The View as feature is your best friend. Use it extensively to test all possible scenarios and user types before you go live. Catching a mistake here can prevent a major data breach later.
• Choose the Right "User" Function: For most cloud-based Power BI setups, USERPRINCIPALNAME() is what you need as it returns the email address. USERNAME() returns the user in a DOMAIN\user format, which is more relevant for older, on-premises systems.
• Keep an Eye on Performance: RLS rules add another layer of filtering to your queries. Very complex DAX patterns can sometimes slow down report rendering. Keep your filter logic as efficient as possible.

Final Thoughts

Row-Level Security is an essential feature for securely sharing insights in Power BI. Static RLS offers a straightforward way to manage access for simpler situations, while Dynamic RLS provides a robust, scalable system for complex organizations. By choosing the right method, you can confidently share reports, knowing that each team member sees only the data they’re supposed to see.

Setting up security rules and creating custom reports is one of the many time-consuming tasks that keeps teams from getting the quick answers they need. At Graphed, we help businesses centralize their sales and marketing data, and our mission is to eliminate that friction completely. We designed our platform so you can connect your various data sources in just a few clicks and build entire shareable dashboards just by asking questions in plain English - no wrestling with DAX or permissions needed. This way, your team can get immediate, real-time insights instead of waiting for manual reports.