Forum Discussion
Dynamic Row Level Security using a GUID column in Power BI
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.
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-bi/
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