Row-level security (RLS) with Power BI

Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, users with access to a workspace have access to semantic models in that workspace. RLS only restricts data access for users with Viewer permissions. It doesn't apply to Admins, Members, or Contributors.

You can configure RLS for data models imported into Power BI with Power BI. You can also configure RLS on semantic models that are using DirectQuery, such as SQL Server. For Analysis Services or Azure Analysis Services lives connections, you configure row-level security in the model, not in Power BI. The security option doesn't show up for live connection semantic models.

Define roles and rules in Power BI Desktop

You can define roles and rules within Power BI Desktop. When you publish to Power BI, you also publish the role definitions.

To define security roles:

1. Import data into your Power BI Desktop report, or configure a DirectQuery connection.

   Note

   You can't define roles within Power BI Desktop for Analysis Services live connections. You need to do that within the Analysis Services model.

2. From the Modeling tab, select Manage Roles.

   

3. From the Manage roles window, select Create.

   

4. Under Roles, provide a name for the role.

   Note

   You can't define a role with a comma, for example London,ParisRole.

5. Under Tables, select the table to which you want to apply a DAX (Data Analysis Expression) rule.

6. In the Table filter DAX expression box, enter the DAX expressions. This expression returns a value of true or false. For example: [Entity ID] = “Value”.

   

   Note

   You can use username() within this expression. Be aware that username() has the format of DOMAIN\username within Power BI Desktop. Within the Power BI service and Power BI Report Server, it's in the format of the user's User Principal Name (UPN). Alternatively, you can use userprincipalname(), which always returns the user in the format of their user principal name, username@contoso.com.

7. After you've created the DAX expression, select the checkmark above the expression box to validate the expression.

   

   Note

   In this expression box, use commas to separate DAX function arguments even if you're using a locale that normally uses semicolon separators (e.g. French or German).

8. Select Save.

You can't assign users to a role within Power BI Desktop. You assign them in the Power BI service. You can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured.

By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filtering with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. Note that if a table takes part in multiple bi-directional relationships you can only select this option for one of those relationships. Select this option when you've also implemented dynamic row-level security at the server level, where row-level security is based on username or login ID.

For more information, see Bidirectional cross-filtering using DirectQuery in Power BI and the Securing the Tabular BI Semantic Model technical article.

Define roles and rules in Power BI using enhanced row-level security editor (Preview)

You can quickly and easily define row-level security roles and filters within Power BI using the enhanced row-level security editor. With this editor, you can toggle between using the default drop-down interface and a DAX interface. When you publish to Power BI, you also publish the role definitions.

To define security roles using the enhanced row-level security editor:

1. In Power BI Desktop, enable the preview by going to Files > Options and Settings > Options > Preview features and turn on “Enhanced row-level security editor”. Alternatively you can use this editor in the Service by editing your data model in the Power BI service.

2. Import data into your Power BI semantic model, or configure a DirectQuery connection.

3. From the ribbon, select Manage roles.

   

4. From the Manage roles window, select New to create a new role.

   

5. Under Roles, provide a name for the role and select enter.

   

6. Under Select tables, select the table you want to apply a row-level security filter to.

7. Under Filter data, use the default editor to define your roles. The expressions created return a true or false value.

   

   Note

   Not all row-level security filters supported in Power BI can be defined using the default editor. Limitations include expressions that today can only be defined using DAX including dynamic rules such as username() or userprincipalname(). To define roles using these filters switch to use the DAX editor.

8. Optionally select Switch to DAX editor to switch to using the DAX editor to define your role. You can switch back to the default editor by selecting Switch to default editor. All changes made in either editor interface persist when switching interfaces when possible.

   

   When defining a role using the DAX editor that can't be defined in the default editor, if you attempt to switch to the default editor you'll be prompted with a warning that switching editors may result in some information being lost. To keep this information, select Cancel and continue only editing this role in the DAX editor.

   

9. Select Save

Validate the roles within Power BI Desktop

After you've created your roles, test the results of the roles within Power BI Desktop.

1. From the Modeling tab, select View as.

   

   The View as roles window appears, where you see the roles you've created.

   

2. Select a role you created. Then choose OK to apply that role.

   The report renders the data relevant for that role.

3. You can also select Other user and supply a given user.

   

   It's best to supply the User Principal Name (UPN) because that's what the Power BI service and Power BI Report Server use.

   Within Power BI Desktop, Other user displays different results only if you're using dynamic security based on your DAX expressions. In this case, you need to include the username as well as the role.

4. Select OK.

   The report renders based on what the RLS filters allow the user to see.

   Note

   The View as roles feature doesn't work for DirectQuery models with Single Sign-On (SSO) enabled.

Manage security on your model

To manage security on your semantic model, open the workspace where you saved your semantic model in the Power BI service and do the following steps:

1. In the Power BI service, select the More options menu for a semantic model. This menu appears when you hover on a semantic model name, whether you select it from the navigation menu or the workspace page.

   

   

2. Select Security.

   

Security takes you to the Role-Level Security page where you add members to a role you created. Contributor (and higher workspace roles) will see Security and can assign users to a role.

Working with members

Add members

In the Power BI service, you can add a member to the role by typing in the email address or name of the user or security group. You can't add Groups created in Power BI. You can add members external to your organization.

You can use the following groups to set up row-level security.

• Distribution Group
• Mail-enabled Group
• Microsoft Entra Security Group

Note that Microsoft 365 groups aren't supported and can't be added to any roles.

You can also see how many members are part of the role by the number in parentheses next to the role name, or next to Members.

Remove members

You can remove members by selecting the X next to their name.

Validating the role within the Power BI service

You can validate that the role you defined is working correctly in the Power BI service by testing the role.

1. Select More options (...) next to the role.
2. Select Test as role.

You're redirected to the report that was published from Power BI Desktop with this semantic model, if it exists. Dashboards aren't available for testing using the Test as role option.

In the page header, the role being applied is shown. Test other roles, a combination of roles, or a specific person by selecting Now viewing as. Here you see important permissions details pertaining to the individual or role being tested. For more information about how permissions interact with RLS, see RLS user experience.

Test other reports connected to the semantic model by selecting Viewing in the page header. You can only test reports located in the same workspace as your semantic model.

To return to normal viewing, select Back to Row-Level Security.

Using the username() or userprincipalname() DAX function

You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You can use them within expressions in Power BI Desktop. When you publish your model, it will be used within the Power BI service.

Within Power BI Desktop, username() will return a user in the format of DOMAIN\User and userprincipalname() will return a user in the format of user@contoso.com.

Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.

Using RLS with workspaces in Power BI

If you publish your Power BI Desktop report to a workspace in the Power BI service, the RLS roles are applied to members who are assigned to the Viewer role in the workspace. Even if Viewers are given Build permissions to the semantic model, RLS still applies. For example, if Viewers with Build permissions use Analyze in Excel, their view of the data is restricted by RLS. Workspace members assigned Admin, Member, or Contributor have edit permission for the semantic model and, therefore, RLS doesn’t apply to them. If you want RLS to apply to people in a workspace, you can only assign them the Viewer role. Read more about roles in workspaces.

Considerations and limitations

You can see the current limitations for row-level security on cloud models here:

• If you previously defined roles and rules in the Power BI service, you must re-create them in Power BI Desktop.
• You can define RLS only on the semantic models created with Power BI Desktop. If you want to enable RLS for semantic models created with Excel, you must convert your files into Power BI Desktop (PBIX) files first. Learn more.
• Service principals can't be added to an RLS role. Accordingly, RLS isn't applied for apps using a service principal as the final effective identity.
• Only Import and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.
• The Test as role/View as role feature doesn't work for DirectQuery models with single sign-on (SSO) enabled.
• The Test as role/view as role feature shows only reports from semantic models workspace.
• The Test as role/View as role feature doesn't work for paginated reports.

Keep in mind that if a Power BI report references a row with RLS configured then the same message displays as for a deleted or non-existing field. To these users, it looks like the report is broken.

FAQ

Question: What if I have previously created roles and rules for a dataset in the Power BI service? Do they still work if I do nothing?
Answer: No, visuals won't render properly. You have to re-create the roles and rules within Power BI Desktop and then publish to the Power BI service.

Question: Can I create these roles for Analysis Services data sources?
Answer: Yes, if you imported the data into Power BI Desktop. If you're using a live connection, you can't configure RLS within the Power BI service. You define RLS in the Analysis Services model on-premises.

Question: Can I use RLS to limit the columns or measures accessible by my users?
Answer: No, if a user has access to a particular row of data, they can see all the columns of data for that row. To restrict access to columns and column metadata, consider using object-level security.

Question: Does RLS let me hide detailed data but give access to data summarized in visuals?
Answer: No, you secure individual rows of data, but users can always see either the details or the summarized data.

Question: My data source already has security roles defined (for example SQL Server roles or SAP BW roles). What's the relationship between these roles and RLS?
Answer: The answer depends on whether you're importing data or using DirectQuery. If you're importing data into your Power BI dataset, the security roles in your data source aren't used. In this case, you should define RLS to enforce security rules for users who connect in Power BI. If you're using DirectQuery, the security roles in your data source are used. When a user opens a report, Power BI sends a query to the underlying data source, which applies security rules to the data based on the user's credentials.

Question: Can a user belong to more than one role?
Answer: A user can belong to multiple roles, and the roles are additive. For example, if a user belongs to both the "Sales" and "Marketing" roles, they can see data for both these roles.

Related content

• Restrict data access with row-level security (RLS) for Power BI Desktop
• Row-level security (RLS) guidance in Power BI Desktop
• Power BI implementation planning: Report consumer security planning
• RLS for Embedded scenarios for ISVs

Questions? Try asking the Power BI Community Suggestions? Contribute ideas to improve Power BI