Recently I had a need to build a Power BI report for a client. This client has a multi-tenant database and their own custom web app. They want to show each client their data without showing any other clients’ data. They also don’t want to require their customers have a Power BI license. Easy enough, just use Power BI Embedded with the App Owns Data model and set up Roles (row-level security) in the Power BI Desktop file, should take less than five minutes to set up. Well, we all know it took longer than five minutes or this blog post wouldn’t exist.
There are some great tutorials from Microsoft out there, they even provide a sample app on Github that you can download if you are not a web developer (I am so NOT a web developer!) to help you get started. There are some great blog posts about it too, one from Reza Rad and one from Kasper de Jonge. So why I am writing yet another blog post about it? Because the devil is in the details, and I completely missed the details which means someone else must have as well.
I don’t want to repeat what others have already written, so go ahead, go read their posts, it’s okay I’ll wait here.
Now that you familiar with Row Level Security in Power BI, how do you make it work when you want to pass in your customer’s identifier because your customers don’t have Power BI accounts? It seems like the only way to make dynamic row level security is to use the Username() DAX function? But wait, doesn’t that require the user to have a Power BI account? Sigh, it seems we are going in circles.
The one thing these articles don’t talk about is that when you are using Power BI Embedded, you can pass in whatever you like for the EffectiveIdentity via the Power BI API and it will “overwrite” the Username() function. What?! That’s right, it will completely ignore the Username() function and use whatever you give it. WooHoo!
Let’s see an example, so it’s crystal clear. I have a small sample of data that has some sales.
Let’s say I want to limit the view of the data by Region. Here are my available regions.
I would need to create a role based on Region that looks like this
Notice that I am using the Username() Dax function for Region. I know, I know, my customers aren’t Power BI users, so this makes no sense, why would I use this? Like I said earlier, the value that you pass via the Web API as the EffectiveIdentity will overwrite the Username() function.
Now that I have my Roles set in my Power BI Desktop file, I can publish my file to the Power BI service. All that’s left to do is to add members to my role. Now when using Power BI Embedded in this scenario, you only need to add one account to the role that was just created. Just keep in mind that this account must be have a Power BI Pro license.
Navigate to the Security for the dataset.
Add your user to the role
It should look like this now
Now, let’s look at the sample web app and how this will work. In the homecontroller.cs there is a call to EmbedReport which contains the creation of the EffectiveIdentity, this is where the magic happens.
Let’s take a look at how this works from the front end. Here’s my report with no security.
Now I just click the checkbox View as a different user, enter my values then click Reload.
Presto change-o, the data is filtered for just the Midwest Region. But wait, Midwest isn’t a Power BI user, so how does that work? Again, the EffectiveIdentity overwrites the Username() function and applies whatever you have entered. It’s magic, I’m telling you.
One thing that tripped me up was that I wanted to filter by RegionID, not region name. When we work with data, we want to be as precise as possible so we typically use a primary key (or other unique identifier) to filter our data. However I have discovered through trial and error that you cannot pass a number, you have to pass character data in order for the filter to work. This means for your data you may need to ensure that there is some unique character data field to identify your customers other than a numeric ID field.
Oh, and if you want to play around with this you can use the sample app from Github and this Power BI Desktop file.
I hope this was helpful.
Update: 2-Feb-2019
I was asked an interesting question via Twitter by Dave Ruijter about converting data to string so you could essentially still use the numeric ID field.
Instead I added a computed column to my dataset that uses the FORMAT() function to convert the existing numeric ID field to a string.
Then I based my role on that column, still using the Username() function.
Now I can filter based on the ID field, WooHoo!
Thanks Dave Ruijter for making me think about this a little bit more.
SQLSwimmer 