Forum Discussion
Dynamic RLS based on many to many relationships. between two tables
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.
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
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
5 Replies
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
Thanks amitchandak , I have implemented your solution and it's working.
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
Thanks HiraNegi , I have implemented your solution and it's working.
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
