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. Be aware that in the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn't restrict this data access.
You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. Previously, you were only able to implement RLS within on-premises Analysis Services models outside of Power BI. For Analysis Services live connections, you configure Row-level security on the on-premises model. The security option will not show up for live connection datasets.
Define roles and rules in Power BI Desktop
You can define roles and rules within Power BI Desktop. When you publish to Power BI, it also publishes the role definitions.
To define security roles, follow these steps.
Import data into your Power BI Desktop report, or configure a DirectQuery connection.
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 Create.
Under Roles, provide a name for the role.
Under Tables, select the table to which you want to apply a DAX rule.
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”.
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, firstname.lastname@example.org.
After you've created the DAX expression, select the checkmark above the expression box to validate the expression.
In this expression box, you use commas to separate DAX function arguments even if you're using a locale that normally uses semicolon separators (e.g. French or German).
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, regardless of whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. You should check this box when your've also implemented dynamic row-level security at the server level, where row-level security is based on user name or login ID.
For more information, see Bidirectional cross-filtering using DirectQuery in Power BI Desktop and the Securing the Tabular BI Semantic Model technical article.
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 Roles.
The View as roles window appears, where you see the roles you've created.
Select a role you created, and then select 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) as 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.
The report renders based on what that user can see.
Manage security on your model
To manage security on your data model, you will want to do the following.
Select the ellipse (…) for a dataset.
This will take you to the RLS page for you to add members to a role you created in Power BI Desktop. Only the owners of the dataset will see Security available. If the dataset is in a Group, only Administrators of the group will see the security option.
You can only create or modify roles within Power BI Desktop.
Working with members
You can add a member to the role by typing in the email address, or name, of the user, security group or distribution list you want to add. You cannot add Groups created within Power BI. You can add members external to your organization.
You can also see how many members are part of the role by the number in parenthesis next to the role name, or next to 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 by testing the role.
- Select More options (...) next to the role.
- Select Test data as role
You will then see reports that are available for this role. Dashboards are not presented in this view. In the blue bar above, you will see what is being applied.
You can test other roles, or combination of roles, by selecting Now viewing as.
You can choose to view data as a specific person, or you can select a combination of available roles to validate they are working.
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 email@example.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 within the Power BI service, the roles will be applied to read-only members. You will need to indicate that members can only view Power BI content within the workspace settings.
If you have configured the workspace so that members have edit permissions, the RLS roles will not be applied to them. Users will be able to see all of the data.
The current limitations for row-level security on cloud models are as follows:
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 datasets created with Power BI Desktop. If you want to enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first. Learn more.
Only Import and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.
There's a known issue where you'll get an error message if you try to publish a previously published report from Power BI Desktop. The scenario is as follows:
Anna has a dataset that is published to the Power BI service and has configured RLS.
Anna updates the report in Power BI Desktop and republishes.
Anna receives an error.
Workaround: Republish the Power BI Desktop file from the Power BI service until this issue is resolved. You can do that by selecting Get Data > Files.
Question: What if I had previously created roles and rules for a dataset in the Power BI service? Will they still work if I do nothing?
Answer: No, visuals will not render properly. You will 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: You can if you imported the data into Power BI Desktop. If you are using a live connection, you will not be able to configure RLS within the Power BI service. This is defined within 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.
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 is the relationship between these 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.
More questions? Try asking the Power BI Community