Multi-level many-to-many dynamic RLS

[Anonymous]

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: Director can see all transactions based on Level 1 (e.g. Electronics). Manager can see transactions based on Level 2 (e.g. Bikes) and Seller can only see transactions on Level 3 (e.g. Mountain Bikes).

Can you, please, help me how to do it? I have tried a lot of things from the communitry, but nothing really solved the whole problem.

Thank you in advance!

[jdbuchanan71]

@Anonymous 

The way to do that is to use active directory security groups.  You assign the security group to the role then any user you add to the security group will get that role's permissions.

[Anonymous]

@jdbuchanan71 thank you!

[jdbuchanan71 · Accepted solution]

@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]

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?