Row-level security (RLS) in Power BI Report Server
Setting up row-level security (RLS) with Power BI Report Server can restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. If you're using the default permissions in Power BI Report Server, any user with Publisher or Content Manager permissions for the Power BI report can assign members to roles for that report.
You configure RLS for reports imported into Power BI with Power BI Desktop. You can also configure RLS on reports that use DirectQuery, such as SQL Server. Keep in mind that RLS isn't respected if your DirectQuery connection uses integrated authentication for report readers. For Analysis Services live connections, you configure row-level security on the on-premises model. The security option doesn't show up for live connection datasets.
Define roles and rules in Power BI Desktop
You can define roles and rules within Power BI Desktop. 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:
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.
From the Modeling tab, select Manage Roles.
From the Manage roles window, select New to create a new role.
Under Roles, provide a name for the role and select enter.
Note
You can't define a role with a comma, for example
London,ParisRole.Under Select tables, select the table to which you want to apply a row-level security filter.
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.
Optionally select Switch to DAX editor to switch to using the DAX editor to define your role. DAX expressions return a value of true or false. For example:
[Entity ID] = “Value”. The DAX editor is complete with autocomplete for formulas (intellisense). You can select the checkmark above the expression box to validate the expression and the X button above the expression box to revert changes.
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). Additionally, in this expression box, use commas to separate DAX function arguments even if you're using a locale that normally uses semicolon separators, such as French or German.
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.
Note
In this expression box, use commas to separate DAX function arguments even if you're using a locale that normally uses semicolon separators, such as French or German.
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.
Bidirectional cross-filtering
By default, row-level security filtering uses single-directional filters, regardless of whether the relationships are set to single direction or bidirectional. You can manually enable bidirectional cross-filter with row-level security.
Select the relationship and check the Apply security filter in both directions checkbox.
Check this box when implementing dynamic row-level security based on user name or login ID.
To learn more, see Bidirectional cross-filtering using DirectQuery in Power BI Desktop and the Securing the Tabular BI Semantic Model technical whitepaper.
Validate the roles within Power BI Desktop
After you've created your roles, test the results of the roles within Power BI Desktop.
From the Modeling tab, select View as.
The View as roles window appears, where you see the roles you've created.
Select a role you created. Then choose OK to apply that role.
The report renders the data relevant for that role.
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.
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.
Add members to roles
After you save your report in Power BI Report Server, you manage security and add or remove members on the server. Only users with either Publisher or Content Manager permissions for the report have the row-level security option available and not greyed out.
If the report doesn't have the roles it needs, you need to open it in Power BI Desktop, add or modify roles, then save it back to Power BI Report Server.
In Power BI Desktop, save the report to Power BI Report Server. You need to use the version of Power BI Desktop for Power BI Report Server.
In Power BI Report Service, select the ellipsis (…) next to the report.
Select Manage > Row-level security.
On the Row-level security page, you add members to a role you created in Power BI Desktop.
To add a member, select Add Member.
Enter the user or group in the text box in the Username format (DOMAIN\user) and select the roles you wish to assign to them. The member has to be within your organization.
Depending on how you have Active Directory configured, entering the User Principal Name here also works. In that case, the Report Server shows the corresponding username in the list.
Click OK to apply.
To remove members, check the box next to their names and select Delete. You can delete multiple members at a time.
username() and userprincipalname()
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, Power BI Report Server uses them.
Within Power BI Desktop, username() returns a user in the format of DOMAIN\User and userprincipalname() returns a user in the format of user@contoso.com.
Within Power BI Report Server, username() and userprincipalname() both return the user's User Principal Name (UPN), which is similar to an email address.
If you're using custom authentication in Power BI Report Server, it returns the username format you’ve set up for users.
Considerations and limitations
Here are the current limitations for row-level security on Power BI models.
Users that had reports using the username() DAX function will notice new behavior now where the User Principal Name (UPN) is returned EXCEPT when using DirectQuery with integrated security. Since RLS isn't respected in that scenario, the behavior in that scenario is unchanged.
You can define RLS only on datasets created with Power BI Desktop. To enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first. Learn more about converting Excel files.
Only Extract, Transform, Load (ETL) and DirectQuery connections using stored credentials are supported. Live connections to Analysis Services and DirectQuery connections using integrated authentication are handled in the underlying data source.
If you're using integrated security with DirectQuery, then your users may notice:
- RLS is disabled and all data is returned.
- Users can't update their role assignments, and get an error on the RLS Manage page.
- For the DAX username function, you continue to receive the username as DOMAIN\USER.
Report authors don't have access to view the report data in Power BI Report Server until they've assigned themselves roles accordingly after uploading the report.
Role assignments through group memberships are only supported when the Power BI Report Server is configured to run with NTLM or Kerberos authentication. Servers running with Custom authentication or Windows Basic need users explicitly assigned to roles.
FAQ
Can I create these roles for Analysis Services data sources?
You can 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. RLS is defined within the Analysis Services model on-premises.
Can I use RLS to limit the columns or measures accessible by my users?
No. If a user has access to a particular row of data, they can see all the columns of data for that row.
Does RLS let me hide detailed data but give access to data summarized in visuals?
No, you secure individual rows of data but users can always see either the details or the summarized data.
Can I add new roles in Power BI Desktop if I already have existing roles and members assigned?
Yes, if you already have existing roles defined and members assigned in Power BI Report Server, you can make additional roles and republish your report with no effect on your current assignments.
Related content
More questions? Try asking the Power BI Community