Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

RLS with multiple dimensions

Dear Power BI experts,

 

I'm running into an issue where I can't get my head around, completely stuck on this.

 

In my simplified model, there's a 'fact_sales' which is linked many-to-one with 'dim_department' and 'dim_product'. I also have two tables for dynamic RLS linked many-to-many with my dim tables, being 'rls_department' and 'rls_product'. Both those connections are bi-directional with security filters applied in both directions. 

 

The input for my rls tables is an Excel sheet composed as follows:

 

DepartmentProduct UPN
Department AProduct AUPN A
Department BProduct AUPN B
Department CProduct CUPN C
 Product BUPN A
 Product CUPN A

 

This is split into my two rls tables in Power BI desktop:

rls_department

DepartmentUPN
Department AUPN A
Department BUPN B
Department CUPN C

 

rls_product

ProductUPN
Product AUPN A
Product AUPN B
Product CUPN C
Product BUPN A
Product CUPN A

 

I have defined one RLS role with the rules:

1. rls_department[UPN] = userprincipalname()

2. rls_product[UPN] = userprincipalname()

 

There are two possible scenarios in my security model

1. UPN B and C: User is allocated to both a department and a product > RLS is working 

2. UPN A: User is allocated to both a specific department and a product but also to another product. This user can see the following: department A from product C but all departments from product B and C. In this case my RLS is not working because it's too restrictive, it will filter department A and product A, B and C. Therefore, UPN A can't see the other departments of product B and C.

 

The goal is to setup dynamic RLS which is reflecting the business logic from the input Excel sheet.

I hope this clarifies my issue. If not, please let me know and I'll further explain.

Many thanks in advance for your time and help!

 

Ruben.

Data Protection
Need Help
Tips and Tricks

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    change your rules from 

    rls_department[UPN] = userprincipalname() && rls_product[UPN] = userprincipalname()

    to

    rls_department[UPN] = userprincipalname() || rls_product[UPN] = userprincipalname()
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ibendlin,

       

      thanks for your time looking into this!

      I'm not sure how I can implement this in my current model. Changing the role as suggested give me the following error (without further explanation):

       

      Thank you very much in advance for your feedback.

       

      Ruben.

       

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        please show a sample version of your data model.