Forum Discussion
Anonymous
4 years agoNot applicable
Multi-level many-to-many dynamic RLS
Hello everyone! I would like to define 3 roles of RLS: Director, Manager and Seller. Here is sample file: https://www.dropbox.com/s/93hwf2lsfe28wlj/example.pbix?dl=0 So, the problem is: Direc...
- 4 years ago
Anonymous
First disconnect the Sellers table from the Departments table, that link is not needed.
Then you will create the three roles that read the correct field from the Sellers table and applies the filter too the Departments table.
Director:
VAR _UPN = USERPRINCIPALNAME() RETURN Departments[Level 1] IN CALCULATETABLE ( DISTINCT ( Sellers[Level 1] ), Sellers[PrincipalName] = _UPN )Manager:
VAR _UPN = USERPRINCIPALNAME() RETURN Departments[Level 2] IN CALCULATETABLE ( DISTINCT ( Sellers[Level 2] ), Sellers[PrincipalName] = _UPN )Seller:
VAR _UPN = USERPRINCIPALNAME() RETURN Departments[Level 3] IN CALCULATETABLE ( DISTINCT ( Sellers[Level 3] ), Sellers[PrincipalName] = _UPN )Then you assign the users to the correct role. When they log in, RLS will get their USERPRINCIPALNAME(), figure out what role they are in an apply the filtering. You can test this using the 'View as' in PowerBI desktop. You pick the user and the role. This replicates what that user would see if they were assigned to that role.
I have attached my updated version of your file for you to look at.
Anonymous
4 years agoNot applicable
jdbuchanan71 thank you!