Row Level Security Power Bi Desktop
Floriana Monterroza
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.
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, user...@contoso.com.
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.
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.
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. 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.
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.
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.
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.
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'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.
The Test as role feature doesn't work for DirectQuery models with Single Sign-On (SSO) enabled. Additionally, not all aspects of a report can be validated in the Test as role feature including Q&A visualizations, Quick insights visualizations, and Copilot.
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.
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.
Also, it doesn't cover enforcing RLS in live connections to external-hosted models with Azure Analysis Services or SQL Server Analysis Services. In these cases, RLS is enforced by Analysis Services. When Power BI connects using single-sign on (SSO), Analysis Services will enforce RLS (unless the account has admin privileges).
It's possible to create multiple roles. When you're considering the permission needs for a single report user, strive to create a single role that grants all those permissions, instead of a design where a report user will be a member of multiple roles. It's because a report user could map to multiple roles, either directly by using their user account or indirectly by security group membership. Multiple role mappings can result in unexpected outcomes.
When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters. What's more, in some scenarios it's not possible to guarantee that a report user doesn't see rows in a table. So, unlike permissions applied to SQL Server database objects (and other permission models), the "once denied always denied" principle doesn't apply.
RLS works by automatically applying filters to every DAX query, and these filters may have a negative impact on query performance. So, efficient RLS comes down to good model design. It's important to follow model design guidance, as discussed in the following articles:
In general, it's often more efficient to enforce RLS filters on dimension-type tables, and not fact-type tables. And, rely on well-designed relationships to ensure RLS filters propagate to other model tables. RLS filters only propagate through active relationships. So, avoid using the LOOKUPVALUE DAX function when model relationships could achieve the same result.
Whenever RLS filters are enforced on DirectQuery tables and there are relationships to other DirectQuery tables, be sure to optimize the source database. It can involve designing appropriate indexes or using persisted computed columns. For more information, see DirectQuery model guidance in Power BI Desktop.
It's possible to measure the performance impact of RLS filters in Power BI Desktop by using Performance Analyzer. First, determine report visual query durations when RLS isn't enforced. Then, use the View As command on the Modeling ribbon tab to enforce RLS and determine and compare query durations.
Once published to Power BI, you must map members to semantic model (previously known as a dataset) roles. Only semantic model owners or workspace admins can add members to roles. For more information, see Row-level security (RLS) with Power BI (Manage security on your model).
d3342ee215