Forum Discussion
Dynamic RLS By Group and Many to Many
Hi,
I have quite a few data tables that I need to use RLS based upon their contract. There are some employees where they need to be able to see more than one contract.
I have a Sales Table and and an Employee Table and I want the employee to only see the sales for the contracts that they have access to.
As an Example, Employee 2 could only see Contract B Sales.
I have been trying to play around witht this and I cannot seem to get it to work with using userpprincipal name. I am guessing that I need a bridge table but just need a push in the right direction to get this work.
Can someone help me?
Thanks
| Sales | |||
| Contract | Month | Value | |
| A | Jan-24 | 500 | |
| A | Feb-24 | 550 | |
| B | Jan-24 | 600 | |
| B | Feb-24 | 650 | |
| C | Jan-24 | 700 | |
| C | Feb-24 | 750 |
| Employee Table | |||
| EmployeeID | Contract | ||
| 1 | [email protected] | A | |
| 2 | [email protected] | B | |
| 3 | [email protected] | A | |
| 4 | [email protected] | C | |
| 5 | [email protected] | B | |
| 1 | [email protected] | C |
Desired Results
| View A | View by employee 1, 3 | ||||
| Contract | Month | Value | |||
| A | Jan-24 | 500 | |||
| A | Feb-24 | 550 | |||
| View C | View by Employee 1,4 | ||||
| C | Jan-24 | 50 | |||
| C | Feb-24 | 100 |
Hi Anonymous ,
I created my own data for testing:Remember to create relationship between the two tables:
Then create Dynamic RLS:
After publishing to Service, in Semantic model > security:
Add the accounts involved in RLS:
Make sure that the user in the RLS is in the viewer role in the workspace, otherwise the RLS will not work!
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Anonymous ,
I created my own data for testing:Remember to create relationship between the two tables:
Then create Dynamic RLS:
After publishing to Service, in Semantic model > security:
Add the accounts involved in RLS:
Make sure that the user in the RLS is in the viewer role in the workspace, otherwise the RLS will not work!
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
Sorry I can't share the pbix file. Because testing Dynamic RLS requires the use of some organisational accounts, which means that it contains sensitive data that I can't share with others because of the company's security policy.
But really you don't need to worry about what type of relationship you have, you just need to have a clear definition of who can see what between the two tables and then create the relationship based on that definition.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi there,
To implement Row-Level Security (RLS) in Power BI so employees only see sales data for their contracts, follow these steps:Step 1: Create and Verify Tables
You already have:
- Sales Table with columns: Contract, Month, Value
- Employee Table with columns: EmployeeIDEmail, Contract
Step 2: Define Relationships
- Load both tables into Power BI.
- In Model view, create a relationship between:
- Sales[Contract] and Employee[Contract].
Step 3: Set Up RLS
- Go to Modeling > Manage Roles.
- Create a new role named ContractAccess.
- Add a DAX filter to the Employee table:
Use the following Code:
[EmployeeIDEmail] = USERPRINCIPALNAME()
Step 4: Assign and Test Roles
- Publish your report to Power BI Service.
- In the workspace, go to Security.
- Assign users to the ContractAccess role.
- Test by viewing the report as specific users to ensure proper data visibility.
This setup ensures employees only see the sales data relevant to their contracts.
