dynamic rls

[powerbiexpert22]

i have below orders data . i want to create security table which should store salesperson details along with address to apply dynamic RLS in such a way so that each salesperson should see respective orders based on salespersonid and the team lead of sales person should see all orders, please guide me on what should be coulmns of security table and how to apply RLS 

order_id	product_id	salespersonid	sales
1	p1	s1	20
2	p2	s2	30
3	p1	s1	10
4	p3	s3	15

[powerbiexpert22]

Hi @Anonymous @KNP @ajaybabuinturi 

i have created seperate admin role with no filter on any table and it is working fine

[Anonymous]

Hi @powerbiexpert22,

Since the admin role has no filters and shows all data, this confirms that your data and relationships are set up correctly.

However, if the team lead’s salesperson IDs in the security table don’t match any salesperson IDs in the orders table, the filtered result will be blank.

Regards,

Vinay Pabbu

[powerbiexpert22]

Hi @Anonymous ,

i will assign admin role to team lead so that they will see all data

[Anonymous]

Hi @powerbiexpert22,

Perfect - assigning the Admin role with no RLS filter.

Please let me know if you have any further questions. We are here to help you.

Regards,

Vinay Pabbu

[Anonymous]

Hi @powerbiexpert22,

As we haven't heard back from you, At this time we are closing this thread. If you have any further issues, please start a new thread in the community forum, and we are here to assist you. Thankyou for your understanding and continuous support.

Thank you for being part of the Microsoft Fabric Community.

Regards,

Vinay Pabbu

[KNP]

When you tested, did you tick BOTH other user with an email filled in AND SalesRole? 

[KNP]

When you test as other user, you still have to tick SalesRole. 

See if that helps. 

[powerbiexpert22]

Hi @KNP ,

i tired with both option (SalesRole and Other user) , it is giving me expected results however when i tried with team lead id ( vibhu.sharma@abc.com) it is giving blank table

[Anonymous]

Hi @powerbiexpert22,

Can you please check USERPRINCIPALNAME() matches the email in the Security table exactly.

Regards,

Vinay Pabbu

[powerbiexpert22]

Hi @Anonymous ,

yes

[powerbiexpert22]

Hi @KNP ,

I have tested as SalesRole however it is giving me blank table as shown below

[ajaybabuinturi]

Hi @powerbiexpert22,

If you want to apply dynamic RLS you need Security Table and Order Table.

1. Secturity table at leat should have UserEmail | SalespersonID  | TeamLeadID | Address columns

2. Make sure 

• UserEmail: the login email used in Power BI service.
• SalespersonID: the individual’s own salesperson ID.
• TeamLeadID: their team lead’s ID (can be their own ID if they are a lead).

3. Create relation ship between SecurityTable[SalespersonID] to Orders[salespersonid].

4. Define the RLS Role

1. Go to Model View → Manage Roles.
2. Create a new role, e.g., SalesRole.
3. Apply this filter on the Orders table:

Orders[salespersonid] IN {
    SELECTCOLUMNS(
        FILTER(SecurityTable, SecurityTable[UserEmail] = USERPRINCIPALNAME()),
        "salesid", SecurityTable[SalespersonID]
    ) }
||
Orders[salespersonid] IN {
    SELECTCOLUMNS(
        FILTER(SecurityTable, SecurityTable[TeamLeadID] = 
            CALCULATE(
                MAXX(SecurityTable[SalespersonID]),
                FILTER(SecurityTable, SecurityTable[UserEmail] = USERPRINCIPALNAME())
            )
        ),
        "salesid", SecurityTable[SalespersonID]
    )
}

5. Tset RLS: Go to Model View → View As Roles.

6. After publishing to Power BI Service:

Go to the dataset settings. Assign real users (email addresses) to the SalesRole defined above.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[powerbiexpert22]

Hi @ajaybabuinturi ,

I tried mentioned above however i am not getting expected results, please see below screenshots

 Security table:

Orders:

Data Model:

Roles:

[KNP]

Security table would need sales person id and email address (the email they use to access Power BI).

Check out this article...

https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple/

[powerbiexpert22]

Hi @KNP ,

i have used the similar set up as mentioned in the article you shared however it is not working