Below I am going to explain some of the caveats that you need to be aware of when implementing Dynamic Row Level Security (RLS) in Power BI. Without this Icould not get Dynamic RLS security working for me and my data.
I found that there are a few things that are currently not mentioned anywhere and it took me some time to gain an understanding. So I am hoping that with this blog post it will make it easier for you to implement Row Level Security using Dynamic Security.
Below is the link to a blog post by Kasper De Jonge in which he explains how to very quickly get up and running with RLS, as well as providing a sample Power BI Desktop Model.
And this is what the Relationship Model looks like, which will make the explanation below a little easier to understand.
Things you need to Know!
Below are the things that I learnt and that will help with understanding the bits that make it all work together.
Testing RLS in Power BI Desktop
The first thing that you will need to update if you have downloaded Kasper De Jonge’s Power BI Desktop model is for in the relationships area. This is because it is missing a key tick box, which without it the Dynamic RLS will not work.
Go into the (Relationships) area in the Power BI Desktop model.
Then edit the relationship between the UserGroup and Group Table, when opening you will see the following below.
Now in order for this to work, you will need to apply the tick next to “Apply security filter in both directions”
NOTE: If this is not enabled or ticked the Dynamic RLS will not work correctly.
So once done it will now look like the following below:
The next thing to note is if you want to test RLS in Power BI Desktop you have to ensure that you have included yourself in both the Users and UserGroupTable.
If not, you will get the following screen when click on the button.
As you can see above the Bar Visual is Blank and the Sales Amount is Blank. This is because with my current login context I am not specified in any of the Dynamic RLS tables.
Which lead me onto the next piece in understanding how the Power BI Service works.
Testing RLS in the Power BI Service
What happened was when I was initially testing this, I put in myself as a user and then a fellowworker as a user in both the Users and UsersGroup table.
I then uploaded the Power BI Desktop file to the Service. Once it was uploaded I went into the Security for my dataset and put in name under the Roles.
Now what I expected to happen is that when I went into the report I should only see the data for Group B and Group C, the reason is because in the UserGroup table I had rows for Group B and Group C
But when I went and viewed the Report I saw the following below. As you can see I can see all the data and NOT Group B and Group C.
This took me quite a to understand and I did try a whole host of things to get it working.
This is unconfirmed by Microsoft but my own conclusion was that because I am the person who is uploading the Power BI Desktop model into the Power BI Service, I must by default have Admin (Server Administrator) rights to the model.
So no matter what I do, I will always see everything. Which makes perfect sense because I am the author of the model.
So to test this I then shared my Dashboard with another user who only had access to Group C, and when he viewed the dashboard as well as the reports he saw the following below. (NOTE: I did add his email address under Security in the Dataset)
Whilst it is great to now have Dynamic RLS in the Power BI Service I did struggle for some time to get it working, as well as to understand how it all pieces and works together.
And since I now know the above information I have been able to successfully roll out and test other Power BI Models successfully.