cancel
Showing results for 
Search instead for 
Did you mean: 
GilbertQ

Power BI – Dynamic Row Level Security – Tips to get it working!

 

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 I could 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.

 

Power BI Desktop Dynamic security cheat sheet

 

And this is what the Relationship Model looks like, which will make the explanation below a little easier to understand.

 

PBI - Dynamic Row Level Security - 1.RelationshipModel.png

 

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 PBI - Dynamic Row Level Security - 2.Relationships Icon.png(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.
  • PBI - Dynamic Row Level Security - 3.Edit Relationship.png
  • 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:
  • PBI - Dynamic Row Level Security - 4.Relationship Filter.png

 

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 UserGroup Table.

 

If not, you will get the following screen when click on the PBI - Dynamic Row Level Security - 5.View Roles As.png button.

 

PBI - Dynamic Row Level Security - 6.Blank Sales.png

 

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 fellow worker 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.

 

PBI - Dynamic Row Level Security - 7.RLS Members.png

 

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

 

PBI - Dynamic Row Level Security - 8.RLS Users.png

 

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.

 

PBI - Dynamic Row Level Security - 9.See All Data.png

 

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)

 

PBI - Dynamic Row Level Security - 10.Filtered Security.png

 

Conclusion

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.

 

Comments

I have to download the smaple. And after test the role i found the Total does not equal with the data in table as in pithe picture as below.

 

 RLS.jpg

how to solve this problem?

 

Thanks

Chawalit