As promised at the Microsoft Data Insight Summit last month, Power BI row-level security for published Power BI Desktop files (aka as cloud models) is now in preview. This means that soon you’ll be able to restrict the data that the user is authorized to see based on the user identity. If you have experience in Power Pivot, you’d probably recall that Power Pivot doesn’t support row-level security. If the user gains access to the model, the user can see all the data. Row-level security requirements for Power Pivot would necessitate migrating to Tabular. This is still a limitation for Excel Power Pivot models. However, if you use Power BI Desktop and publish the file to Power BI, you can now create roles that use rules (similar to DAX filters). Row-level security is well documented here.
From an implementation standpoint, Microsoft has decided to externalize row-level security in Power BI. At least for now, instead of defining security inside the PBIX file, you define roles in powerbi.com and associate them with a dataset. To do so, you simply click the ellipsis (…) button next to the dataset (it must be created by publishing a pbix file) and then click Security. You create a new role, assign users, and define rules using the same syntax as DAX row filters when creating Tabular roles. The RLS preview doesn’t seem to check currently for grammar errors. It takes any text so be sure to use correct DAX syntax.
To test the role, click the ellipsis (…) next to the role and click “Test data as role”. This action opens the report that is associated with the dataset. If you see nothing on the report, more than likely the rule syntax is incorrect or the rule has no match. Notice that you can also implement dynamic data security by using the DAX Username() function. For example, if the Employee dataset includes a LoginID column that stores the user principle name (UPN), the following rule would apply a filter on the Employee table to return only the employee who’s logged in to powerbi.com and running the report. UPN typically corresponds to the user email address. This is important to know because unlike Tabular where Username would return domain\user, here it returns the email address so make sure your filtered column stores UPNs.
As a preview feature, RLS has a few significant limitations that I expect to be removed when it becomes GA. For now, use it for testing and learning purposes only. As the documentation states:
Note: The preview is intended to let users to start trying out the feature. It will also allow us to collect feedback for improvements. It is not intended for operational usage. Rules defined during the preview may not be available when the feature is generally available.
The most important limitations are:
- Republishing the dataset removes the role definitions. Not only is this annoying but it might also present security vulnerability during the period when you need to recreate the rules (users will see all the data).
- RLS doesn’t support group workspaces. It’s not very useful to apply RLS to your private data unless you share dashboards with someone else.
- When I did my first test a few days ago, Username would return a GUID which apparently is fixed now (Power BI moves fast!).
- You cannot add security groups or distribution lists to the member list. As a best practice to simplify maintenance, instead of adding individual users, you should assign groups.