How to Assign Roles in Power BI

Controlling who sees what in your Power BI reports is essential for data security and clarity. Instead of creating different report versions for different teams, you can use a single report and simply define what data each person is allowed to access. This article provides a step-by-step guide on how to assign roles in Power BI using Row-Level Security (RLS) to control data access effectively.

What Exactly is Row-Level Security (RLS)?

Row-Level Security is a Power BI feature that restricts data access for specific users at the row level. Think of it as applying filters to your data based on who is viewing the report. When a user with a specific role opens a report, Power BI automatically applies the filters you've defined for that role, showing them only the data they’re permitted to see.

For example, imagine you have a single sales report for your entire company. With RLS, you can create roles so that:

• The US Sales Manager sees only sales data from the United States.
• The European Sales Manager sees only sales data from Europe.
• Individual sales reps see only their own deals and customers.
• The CEO or Head of Sales sees all the data from every region.

This allows you to maintain one central report while delivering a tailored, secure experience for every user. There are two primary ways to set up RLS: static and dynamic. We'll cover both.

How to Set Up Static Row-Level Security

Static RLS is the most straightforward approach. You create a separate role for each group or category you want to filter and manually write the filter rule for each one. This method works well when you have a small number of well-defined groups that don't change often, like sales regions.

Step 1: Open Your Report in Power BI Desktop

Your journey begins in Power BI Desktop. Open the PBIX file that contains the report and data you want to secure.

Step 2: Navigate to "Manage Roles"

In the top navigation ribbon, click on the Modeling tab. From there, you will see a section for Security. Click on the Manage roles button.

Step 3: Create and Define Your First Role

The "Manage roles" window will pop up. This is where you will create your roles and the DAX (Data Analysis Expressions) rules that define them.

1. Click the Create button to add a new role.
2. Rename the role to something descriptive. For our example, let's call it "North America Sales".
3. Select the table you want to apply the filter to from the list on the right. Let's use a "Sales" table.
4. In the "Table filter DAX expression" box, write the rule to filter the data. The expression must evaluate to a true or false value. To show only data where the "Region" column is "North America", you would write:

[Region] = "North America"

Step 4: Create Additional Roles

Repeat the process for any other groups you need. For instance, to create a role for the European team:

1. Click Create again.
2. Rename the new role to "Europe Sales".
3. Select the same "Sales" table.
4. Enter the corresponding DAX expression:

[Region] = "Europe"

Once you've created all your roles, click Save.

Step 5: Test Your Roles in Power BI Desktop

Before publishing, you must test that your roles work as expected. Back on the Modeling tab, click the View as button.

A yellow bar will appear at the top of your report canvas. This is a testing environment where you can simulate viewing the report as a specific role.

1. Check the box next to the role you want to test, for example, "North America Sales".
2. Click OK.

Your report will now refresh and display only the data for North America. If you see charts visualizing sales in Europe or Asia, you know something is wrong with your DAX filter. You can switch between roles or stop viewing by clicking the "Stop viewing" button in the yellow bar.

How to Set Up Dynamic Row-Level Security

Static RLS is great for a few roles, but it becomes unmanageable if you need to create a unique view for dozens or hundreds of users (like individual sales reps). This is where dynamic RLS shines. You create a single role, and Power BI dynamically filters the data based on the user's login credentials.

Step 1: Create a User Permissions Table

The key to dynamic RLS is a table that maps users to the data they're allowed to see. This table must contain the email addresses that your users use to log into Power BI. It also needs a column that can be used to filter your main data table.

For example, you could create a "Sales Reps" table with two columns: RepEmail and RepName.

Make sure this table is loaded into your Power BI data model.

Step 2: Create a Relationship Between Tables

Now, you need to connect your new permissions table ("Sales Reps") to your main data table ("Sales"). In the Model view of Power BI Desktop, drag the common dimension (e.g., RepName) from your "Sales Reps" table to the corresponding column in your "Sales" table to create a relationship.

Step 3: Define a Single Dynamic Role

Go back to the Modeling tab and click Manage roles.

1. Click Create and name the new role something like "Sales Rep View".
2. Select the user permissions table you created (the "Sales Reps" table).
3. In the DAX expression box, use the USERPRINCIPALNAME() function. This function automatically returns the email address of the user who is currently logged into Power BI.

Your DAX expression will look like this:

[RepEmail] = USERPRINCIPALNAME()

This single rule tells Power BI: "For the user who is logged in, find their email address in the RepEmail column of the 'Sales Reps' table, and then filter the entire data model based on that row."

Step 4: Test Your Dynamic Role

Click View as again. This time, when you select the "Sales Rep View" role, you'll also see an option to simulate a specific user. Check the box for Other user and enter one of the email addresses from your permissions table (e.g., jane.doe@yourcompany.com).

When you click OK, the report will display only Jane Doe's data. You can repeat this test with other email addresses to confirm your dynamic RLS is working perfectly.

Assigning Users to Roles in the Power BI Service

Creating roles happens in Power BI Desktop, but assigning actual users to those roles happens online in the Power BI Service.

1. Publish Your Report: Once you've saved and tested your roles, publish your report from Power BI Desktop to a workspace in the Power BI Service.
2. Find Your Dataset: Log in to app.powerbi.com and navigate to the workspace where you published the report. Don't click on the report itself – find the corresponding dataset in the content list.
3. Access Security Settings: Click the three dots (...) next to the dataset name and select Security from the menu.
4. Add Users or Groups to Roles: On the Row-Level Security page, you'll see the roles you created ("North America Sales," "Europe Sales," "Sales Rep View," etc.). Select a role, and a text box will appear where you can type in the email address of the user or a user group (e.g., an Azure AD security group) you want to assign to that role. Press Add and then Save.

Now, when an assigned user opens the report in the Power BI Service, they will automatically see the filtered view you've created for them.

A Few Best Practices

• Use Groups, Not Individuals: For better long-term management, assign roles to user groups (like "Europe Sales Team") in Azure Active Directory instead of adding individual email addresses one by one. This way, you can manage membership in the user group, and Power BI will automatically apply the right permissions without you having to update the dataset security.
• Test, Test, Test: Always test your roles thoroughly in both Desktop and the Service. Use the "Test as role" feature in the Service to confirm users see exactly what they should, and nothing more.
• Favor Dynamic for Scale: If you have more than 10-15 roles or expect user permissions to change often, invest the time to set up dynamic RLS. It's more scalable and easier to maintain.

Final Thoughts

Setting up roles in Power BI is a critical skill for sharing data responsibly. By mastering Row-Level Security, you can build a single, powerful report that serves personalized, secure insights to everyone in your organization, from individual contributors to a leadership team.

Once you are comfortable securing and sharing reports across your organization, the next step is automating the entire analysis process. At Graphed, we've built a platform that allows you to connect all your data sources - from marketing platforms to CRMs - and build real-time dashboards just by asking questions in plain English. There’s no need to learn DAX or spend hours setting up complex security rules. Graphed turns hours of manual reporting into a 30-second conversation, giving everyone on your team the power to get the answers they need instantly.