Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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: 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!

Data Protection
Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    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.

     

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    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.