Row Level Security in Power BI Desktop
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 at the row level and you can define filters within roles. RLS is a Pro feature.
EPC Group Power BI teams configure RLS for data models imported into Power BI with Power BI Desktop. We can also configure RLS on datasets that are using DirectQuery, such as SQL Server. Previously, we were only able to implement RLS within on-premises Analysis Services models outside of Power BI. For Analysis Services live connections, we configure Row-level security on the on-premises model. The security option will not show up for live connection datasets.
Define roles and rules within Power BI Desktop
EPC Group can define roles and rules within Power BI Desktop. When we publish to Power BI, it will also publish the role definitions.
If we want to take advantage of dynamic security, we will need to enable the preview switch Enable cross filtering in both directions for DirectQuery. This will allow the ability to cross filter and apply the security filter in both directions.
To define security roles, we can do the following.
- Import data into our Power BI Desktop report, or configure a DirectQuery connection.
We cannot define roles within Power BI Desktop for Analysis Services live connections. We will need to do that within the Analysis Services model.
- Select the Modeling tab.
- Select Manage Roles.
- Select Create.
- Provide a name for the role.
- Select the table that you want to apply a DAX rule.
- Enter the DAX expressions. This expression should return a true or false. For example: [Entity ID] = “Value”.
We can use username() within this expression. Be aware that username() will have the format of DOMAIN\username within Power BI Desktop. Within the Power BI service, it will be in the format of the user’s UPN.
Understanding DAX Username()
This filter simply means that the user logged in will only see his/her own records in the whole data set. As we remember the username field in table will be the same as username format generated by Power BI. So filtering one table will affect others and a result, this single line filter will enable dynamic row level security in the whole Power BI solution here.
1. After you have created the DAX expression, you can select the check above the expression box to validate the expression.
2. Select Save.
We cannot assign users to a role within Power BI Desktop. This is done within the Power BI service. You can enable dynamic security within Power BI Desktop by making use of the username () DAX functions and having the proper relationships configured.
Validating the role within Power BI Desktop
After we have created our role, we can test the results of the role within Power BI Desktop. To do this, select View as Roles.
The View as roles dialog allows us to change the view of what we are seeing for that specific user or role. We will see the roles we have created.
We select the role we created and then select OK to apply that role to what we are viewing. The reports will only render the data relevant for that role.
We can also select other user and supply a given user. It is best to supply the User Principal Name (UPN) as that is what the Power BI service will use. Select OK and the reports will render based on what that user can see.
Within Power BI Desktop, this will only display different results if we are using dynamic security based on your DAX expressions.
Manage security on our model
To manage security on our data model, we will want to do the following.
- Select the ellipse (…) for a dataset.
- Select Security.
This will take us to the RLS page for us to add members to a role we 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.
We can only create or modify roles within Power BI Desktop.
Working with members
We can add a member to the role by typing in the email address, or name, of the user, security group or distribution list we want to add. This member has to be within our organization. We cannot add Groups created within Power BI.
We 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.
We can remove members by selecting the X next to their name.
Validating the role within the Power BI service
We can validate that the role you defined is working correctly by testing the role.
- Select the ellipse (…) next to the role.
- Select Test data as role
We 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.
We can test other roles, or combination of roles, by selecting Now viewing as.
We can choose to view data as a specific person, or we 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 () DAX function
We can take advantage of the DAX function username () within our dataset. We can use it within expressions in Power BI Desktop. When we 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.
Within the Power BI service, username() will return the user’s User Principal Name (UPN). This looks similar to an email address.
Using RLS with Groups in Power BI
If we publish your Power BI Desktop report to a group within the Power BI service, the roles will be applied to read-only members. We will need to indicate that members can only view Power BI content within the group settings.
If we have configured the Group 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.
Here is a list of the current limitations for row-level security on cloud models.
- If we previously had roles/rules defined within the Power BI service, we will need to recreate them within Power BI Desktop.
- We can define RLS only on the datasets created using Power BI Desktop client. If we want to enable RLS for datasets created with Excel, we will need to convert your files into PBIX files first.
- Only ETL and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.
- Q&A and Cortana is not supported with RLS at this time. We will not see the Q&A input box for dashboards if all models have RLS configured. This is on the roadmap, but a timeline is not available.
External sharing is not currently supported with datasets that use RLS.
With over 25 years of experience in Information Technology and Management Consulting, Errin O’Connor has led hundreds of large-scale enterprise implementations from Business Intelligence, Power BI, Office 365, SharePoint, Exchange, IT Security, Azure and Hybrid Cloud eﬀorts for over 165 Fortune 500 companies.