Dynamic RLS based on many to many relationships. between two tables

[Optimusprime_25]

Hello All,

We have two tables in the report.

One is report table which contains data, multiple columns along with country and unit as columns.

We have another table where we have user access details in this table.

We have to join many to many relationships between these two tables on country as well as unit, why because we want country and unit to be restricted based on access provided in the second table.

Below is the many -to- many relationships between country and unit columns between two tables.

Can anyone please provide me the solution, how to create dynamic RLS whereas one specif user should be able to see only the data for which he/she has access.

Thank you.

[amitchandak · Accepted solution]

@Optimusprime_25 , Create a combine column

Key = [Country] & "-" & [unit]

for the direct query, you use combinevalues

then you can create RLS using the user 

[user_id] = userprincipalname()

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk

[HiraNegi · Accepted solution]

Hi @Optimusprime_25 ,

You may follow below steps with sample scenario.

1. Main Data with custom column to concat Country and Unit:

2. User Data with custom column to combine Country and Unit.

3. Create join between tables on ID field.

4. Create new role with filter on UserID = userprincipalname()

This will dynamically filter the dataset for the user login mail id.

5. Verify if the roles are working as expected.

Hope this helps.

Regards,

Hira Negi

Please don't forget to upvote and Accept as answer if the reply is helpful

[Sartaj_Singh]

Hi HiraNegi,

Hope you are doing well!

I am also facing the same issue but in Power BI report server. And Many to Many relationship is not available in Power BI report server, could you please help me on priority with some solution.

Regars

Sartaj

[Optimusprime_25]

Thanks @HiraNegi , I have implemented your solution and it's working.

[amitchandak · Accepted solution]

@Optimusprime_25 , Create a combine column

Key = [Country] & "-" & [unit]

for the direct query, you use combinevalues

then you can create RLS using the user 

[user_id] = userprincipalname()

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk

[Optimusprime_25]

Thanks @amitchandak ,  I have implemented your solution and it's working.