One of the nice benefits of using a cloud based BI service such as Power BI, is the ability to easily share content with users outside the organisation. This is very different than the old school BI solutions that are installed on-premise, and require access to your network.

Another Power BI feature is RLS – Row-Level Security, which allows the publisher to share subsets of the data with different users. For example, allow sales representatives in Canada to see only Canadian sales data.

In this article, I’ll talk about using both features together- sharing a subset of the data (using RLS) with users outside the organisation. You would think all you need to do is just use both features, however my recent experience introduced a deadlock in trying to use both of them together. I’ll explain what happened and how this can be resolved. Let’s start with an overview of both features.

Sharing content with external users

In November 2017, Microsoft has introduced the ability to share dashboards, reports or apps with Power BI users outside your organisation. This was enabled by integrating Power BI with Azure Active Directory B2B collaboration. When you share content with someone outside the organisation, they are sent an email inviting them to accept the shared content. When they do so, they are logged under your own organisation, allowing you to treat them the same way you do internal users.

In terms of licensing, if your organisation is using Power BI premium, you are allowed to share content with any Power BI user, even those on the free tier. A more common scenario is for you to be using Power BI Pro. In that case, the users outside the organisation need a pro or premium license. If they don’t have one, you can assign them a pro license, which will enable them to view your content, but they’ll be “pro” only under your organisation.

The actual sharing is done no differently than how you share content with internal users. Wherever there’s a share functionality (dashboard, report, app), you can simply type an email address of an external user.

If you want to learn more about sharing content outside the organisation, click on the following link. It covers and demonstrates the common use cases: Access to external users

Row-Level Security (RLS)

It is very common to share different parts of your data with different users. The easiest way to do so is to share specific reports, dashboards or apps only with users who should be using them. However, sometimes you want users to get access to your reports and enjoy their full functionality, but be exposed to only a subset of the data. Think about an organisation which shares sales data with its resellers. Usually such organisation will not want (and may not be allowed), to share all the content will all resellers. They are likely to show each reseller their own data.

This is achieved by using Row-Level Security, or RLS. In the Power BI desktop, under “Modelling”, there’s an option to create and manage roles. Using DAX, you can define some rules, allowing every role to be exposed to just a subset of the data. For instance, you might create a role called “Wellington” which will be restricted to [City]=”Wellington”, and then another role called “Christchurch” which will be restricted to [City]=”Christchurch”. After the roles are created, in the Power BI service, you can right click on the dataset, choose “security” and then add members to roles.

If you want to watch an example for how RLS is used, here’s a very good video by Microsoft’s Adam Saxton, demonstrating the feature (If there’s no video below, refresh the page):

One thing that I struggled with a bit at first, was understanding that once you start using RLS, all of your users need to be added to a role. At first I thought that by default all users can see everything, except for those added to a specific role. In the example I mentioned above, if I created a Wellington role and a Christchurch role, I assumed that a user who isn’t added to any of these roles, can view all the data. However, that’s incorrect. A user which isn’t added to a role won’t be able to see anything, and will be shown an error message when loading a visual. An easy solution to that is to create a “general” or “all data” role which has no filters, and add all users to that role, except for those who should be restricted to view partial data.

Sometimes you have way too many users to manually be able to add them to roles. In such case you might want to use groups or distribution lists. There’s also an option to use a more dynamic version of RLS, which will restrict data access based on a table in your database. Here’s an article covering that: Dynamic Security.

Better Together: RLS & External Users

Having tried both features separately, I thought I would try them both together, which is when I ran into a deadlock. I wanted to share a report with an external user, and add her to a specific role, allowing her to view only some of the data. So, before sharing the report with her, I opened the security menu under the report’s dataset in order to add her to a role. However, when you try to add a user to a role, that user should be recognised by your organisation – meaning, you have to share a report with them first, have them accept the invitation, and only then they get recognised by your organisation and you can add them to a role.

This felt like a dead end. I cannot add them to a specific role, because they are not part of the organisation, and I cannot make them part of the organisation without sharing a report with them and have them accept it. But if I share a report without assigning a role, they’ll be able to view all the data.

The solution is quite simple – share a report that shows nothing. Maybe just a text box explaining that this is a dummy report. Then, once they accept this report, you can add them to the right role, and then share the report you wanted to share in the first place.

I hope this article helps a bit. Leave a comment below if you have any questions or if you’ve found better ways to share and restrict data at the same tome.

If you enjoyed the content of this article, please consider sharing it with your network on LinkedIn. As an independent consultant, this helps my business. Thank you!