Dynamic Row Level Security using a GUID column in Power BI

[Mike282]

Hi all,

So I've got a question in regards to dynamic row level security based on data in Power BI.

I've got my Power BI connected to a SharePoint online list for hours worked that requires manager approval. In the list there is a column for Manager which includes the manager GUID of our Organization O365 instance. I want to add row level security so that when I disaply in a table visual a list of all the staff members and their worked hours. It will only show the relevant records based on the Manager who's viewing the list (using the Manager GUID to determine what they see).

For example if I'm a manager and my GUID is "123" and there's the following 2 rows for hours worked by staff

In theory because my GUID is 123 and I view the Power BI report I should only be able to see the first row of data and not the second one. I'm not sure how O365 and Row Level Security would work and whether I can specify a dynamic Row Level Security based on matching the manager GUID on the dataset with the person who views the data.

[Mike282]

Hi all,

So I've done a quick read of http://angryanalyticsblog.azurewebsites.net/index.php/2016/12/27/dynamic-rls-via-hierarchy-in-power-...

Still not too sure how to get the heirarchy RLS working. I've attached a sample PBIX file with the data structure I'm currently working with. Basically I've got a Sales Order table linked to a Department Table as a many to one. You can see in the SalesOrder table there is a name and email for both the person who made the sale (the Test 1, 3, etc.) and the manager name and email (manager 1, 2, 3 etc). The RLS needs to be set so that the rows in the table visual in my PBIX file is only visible to the person who've made the sale and the manager linked to the sales order. 

Download my sample PBIX file here

I see I can use the =USERPRINCIPALNAME() DAX to dynamically set the report so that it's filtered by the email of whoever logs into the Power BI. How do I need to structure the data table so that the manager heirarchy would work?

Any help would be greatly appreciated. Thanks all!

Kind regards,

Mike

[Anonymous · Accepted solution]

Mike, the user principal name is the root to assigning role based security as this is the id passed to the power bi report from the power bi service. You need to set up 2 roles in your scenario, one that matches the manager login and one for the salesperson. The role for the manager should filter the manager column containing the upn, either in the sales table or via your employee manager lookup. The second role for the salesperson should filter the salesperson column containing the UPN. If you don’t already have the UPN you need to get that imported as well as power bi won’t map directly to a GUID. Hope this helps.