Dynamic RLS By Group and Many to Many

[Anonymous]

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	Email	Contract
	1	1@whatever.com	A
	2	2@whatever.com	B
	3	3@whatever.com	A
	4	4@whatever.com	C
	5	5@whatever.com	B
	1	1@whatever.com	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

[connect]

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:

1. Sales Table with columns: Contract, Month, Value
2. Employee Table with columns: EmployeeIDEmail, Contract

Step 2: Define Relationships

1. Load both tables into Power BI.
2. In Model view, create a relationship between:
• Sales[Contract] and Employee[Contract].

Step 3: Set Up RLS

1. Go to Modeling > Manage Roles.
2. Create a new role named ContractAccess.
3. Add a DAX filter to the Employee table:
    
   Use the following Code: 
    
   [EmployeeIDEmail] = USERPRINCIPALNAME()

Step 4: Assign and Test Roles

1. Publish your report to Power BI Service.
2. In the workspace, go to Security.
3. Assign users to the ContractAccess role.
4. 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.

[Anonymous]

Actually I guess I am just a bit worried about the many to many with the RLS.  It looks like that doesn't seem to be an issue here at all?  Thanks!

[Anonymous]

Would you mind sharing that file with me?  Thanks!

[Anonymous]

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.

[Anonymous · Accepted solution]

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.