Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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
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
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 , 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |