Dynamic RLS in Power BI with duplicate values

[Swati20]

Hi,

I'm trying to apply RLS in Power BI, the sample of the tables being used have been attached below:

1. The EmpTable on which the RLS is to be applied is in this format where Account coulum has duplicates

2. The UsersTable with Employee & Admin emails in this fromat where there are multiple users under single account

I want to apply RLS on the EmpTable and have created many-to-many relationship by Account name between the EmpTable and UsersTable. Following are the requirement:

1. Admins should be able to access data related to all Account names

2. Emp should be able to access data related to their Account data only

Your help will be appriciated!!

[Anonymous]

Hi @Swati20,

I'd like to suggest you create bridge table with unique records extract from these two tables and work as relationship key to link these tables. Then you can set RLS on this table fields to accurately control the RLS filter effects.

Joining Many to Many with a Bridge in Power BI | Seer Interactive

Regards,

Xiaoxin Sheng

[Swati20]

Hi @Anonymous , 

Thanks for your response. 
Actually, my main issue here is that Admins should have full visibility of the data but I'm not able to create Admin role for that since neither can I assign a single account to the Admin similar to the users nor can I assign "ALL" for all accounts as there is no "ALL" or blank values in the Account column of EmpTable .

[Anonymous · Accepted solution]

Hi @Swati20,

Perhaps you can create these mange fields on power bi side, you can create calculated table to append custom row from the raw table and use this new table to link other tables.
After these steps, you can setting additional if statement condition batch in RLS filter to check current role are include in the exception scenarios.

Regards,

Xiaoxin Sheng