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

[GilbertQ]

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.

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

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

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)

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.

[ranbeermakin]

Hi there, was there any resolution to Richard's question?

If there is dynamic security why do I still have to add users/groups in Roles in Power BI Service?

Any alternative?

Hoping for a response

Thanks,

Ranbeer

[GilbertQ]

Hi @ranbeermakin

When uploading to the Power BI Service, you still have to map the PBIX file to the Power BI Service.

By using an Active Directory Security Group this will mean you do not have to add users manually and it will be managed as people are added and removed from AD Security Groups.

The reason it is dynamic is that you only need to create 1 role. And each user can have their own security settings dynamically created on login.

[jatneerjat]

Hi @GilbertQ,

I am working on a report which have data for different users and now i want that whenever a user login into power bi then the user should only see the data that is related to him.

I have a column [hiringmanageremail] in table [userinfo] which have the emailid of the users so I created a role in power bi desktop as

so when i login to the power bi it shows data related to me but when anybody else(whose id is present in table [userinfo] ) login to power bi and see the reports then it shows as below:

then i added this user as a member of role in power bi service 

then click on apply

then it shows all the reports to this user

Why it is not taking the email dynamically and i have to enter them in roles manually.

what i have to do so that power bi dynamically takes emailids for the login user and show the data related to it.

Thanks,

Jat

[GilbertQ]

Hi @jatneerjat

In order to make it Dynamic you have to use Dynamic Row Level Security, in which you create a single role and all the users are passed via the single role.

If all the users are in an Active Directory Security Group, they can then be added via the Security group.

Below is a great blog post by Kasper from Microsoft, in which he explains how to use Row Level Security with a working example. I would suggest that you work through this and get it working first. And then apply those learnings to your model.

http://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/ 

[Anonymous]

@jatneerjat : did you fix the issue if yes can you let me know details step please

[Supritha1594]

Hi, 
is it possible to hide a slicer values in the same way. I want to show slicer values based on the logged in user. The slicer I have is for a list of cost centres. I want to show the cost centres that are belong to logged in user. Would be really great if you or someone in the community help me with this.

Thanks in advance!