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!
13 Comments. Leave new
Great article.
Can RLS work on apps with external users ?
I want to share app instead of individual report with external user.
Yep. Apps can be shared with external users, and RLS can be used, same as when sharing an individual report.
Ziv.
Nice solution Ziv.
We also encountered many organizations wish to share reports with external users or business partners. They can create beautiful reports with Power BI but lack that final step to easily share them. More often than not they get tripped up by the Azure B2B requirement or having their own Power BI Pro licenses. Especially, when the user numbers become quite large. We created Binokula specifcally to address the final step for report sharing with Power BI. RLS is still applied but there is no need for users to have a Power BI Pro license or even have their email associated as a Microsoft account. Feel free to have a look when you get some time.
Hi,
I am also in the same bucket to share data to external users without providing access to the environment and having PowerBI licenses . can you let us know how you did. And also looking to automated process to send pdf of reports with RLS on it for induvial of external and internal customers .
This was a great hel to me, this litle trick works fine!
Thanks for sharing this!
Nice article
You are a total Life saver, total Panic when this did not work this morning and I already have dashboard created and “Assumed” it would just work, why would it not!
At your service Jimmy 🙂
So, I have created a report with RLS enabled based on account names (eg. Adidas, Puma, Reebok etc..). However, my users neither have a pro nor a premium capacity. If I share the report with them, they cannot view it unless they upgrade to a Pro license.
Is there a way for them to view the report, for the roles they’ve been set up with in RLS without a pro or premium license?
TIA!
Unfortunately in order to share a report, your users need a license too.
Thank you for the detailed explanation Ziv. In my case, the external users are added in Azure AD in my organization, one of them is given an email id from my organization, basically made member and one more user is given guest access and they have accepted the invitation from my organization. And they have Power BI Pro license from their organization. How can the RLS be implemented now? I do see the person who is given our company domain email, so when I add that email to the role, will the user have to use my company’s domain email to login to see the Power BI report? Do they have to have PowerBI Pro from my company now to view the report?
Did you find an answer for this question?
i want only share dataset to external user.external user want to work on data. how can i use RLS for external user to share dataset