Dynamic Security in Power BI

UPDATE: the feature has changed since its introduction. For more information, see the update at the bottom of the post.

In case you haven’t noticed, Power BI added a new feature in preview mode: row level security (RLS). This blog post describes how you  can use this feature to implement dynamic security. In other words, the data being displayed depends on the user that’s viewing it. I will not talk about live connections to Analysis Services, there are plenty of resources on how to implement dynamic dimension security in SSAS (for example the excellent Expert Cube Development).

Let’s start with a very basic data set:

I included the email address of the user, as well as a (fictional) domain login. I loaded the data into a Power BI Desktop model. For testing purposes, I tried to add a calculated column that would show my username, just to see if it would display an email address or a domain login. Alas, this is not possible.

So I needed to create a measure instead. This measure just counts the number of rows where the email address matches the USERNAME() function. I created a similar measure for the domain login.

When I threw everything on a canvas, I got the following result:

Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).

OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems  that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.

Now, let’s add the RLS by clicking on the three dots at the data source and choosing Security. As a member, I added the distribution list of the Microsoft Competence Center of my company. This works because we are using Office 365 for mail. Currently it’s not possible to use Active Directory Security Groups, unfortunately.

Then I defined a simple rule that just checks if the email equals the USERNAME() function.

You can test your role by clicking on the three dots and choosing Test data as role.

Now my measures seem to be working fine, as well as the rule.

At the top, you can choose between roles or you can specify a specific user to test the security.

When I choose my own account, I still see all of the data (and the measures go totally crazy. It appears I’m all people at once). This is probably because I’m the owner of the data set and thus some sort of god-like admin. It’s the same in Analysis Services: if you’re an administrator you can always see all of the data, even if roles are denying you access to it.

When I choose another user, you can see that the security is correctly applied.

When someone is chosen that has access (because they are part of the distribution list) but whose email is not in the email column, the table is just empty. When someone is chosen that doesn’t have access, the visual gives an error.

You can only test reports though, dashboards are not listed. This makes it a bit harder to test them, since you’d have to actually log in as that person to see what the results are. I quickly created an easy dashboard with two tiles: one tile is a live tile displaying the entire report, the other a simple card displaying the value for MeasureA.

This is what I see (as owner of the data):

(expertly photoshopped in Paint to make it a bit smaller)

I kindly asked Davy to log in into Power BI and sent me a screenshot of what he saw. The result:

So RLS works on live tiles (as I would expect), but also on regular tiles. Thanks again Davy for the assistance.

That’s it for this blog post. RLS is still in preview, so it’s possible some things can change along the way. Currently there are some limitations (AD groups being one of them) and I hope they are resolved soon.

UPDATE:
Row level security in Power BI is now in general availability. There are some changes however: you have to define the roles and their filters inside Power BI Desktop instead of in the service. Adding members to a role still remains in the service. This separation makes sure that you can republish Power BI Desktop files without losing the defined security.