Forum Discussion
Multi-level many-to-many dynamic RLS
- 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
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.
how do you also make the role assignment dynamic... i.e. the fact that Hoegart is a director is currently manual... you would need to factor in a table or field that drives a role identification and automatically assigns the filtering level... can you show an extended example?