Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

Dynamic RLS in One to many relationship

Hello BI folks ,

 

I have three different  tables A, B and C And there's both sided many-to-one relationships.

C Many to one ->  A -> One to many B (Here i defined as bidirectional and enabled secuirty )

Here B has email id column.

pranay2k2_0-1677987477063.png

 

Report has two different visualizations .Hence, I am only able to turn on row level security filter for only table A .

Applied this role : B.mail id = Userprincipalname()

 Please tell me a workaround to enable RLS on Table C

 

Thanks in advance. 

8 REPLIES 8
Sahir_Maharaj
Super User
Super User

5. In the "Filter" section, click "Add filter" and select the "B Count" measure you created in step 1. Choose the appropriate operator (e.g. "is greater than" or "is equal to") and enter the appropriate value to control access to the data in Table C based on the related data in Table B.

 

6. Click "OK" to save your RLS filter and apply it to your report.

 

This approach uses a measure to indirectly filter Table C based on the related data in Table B. The measure calculates the number of related rows in Table B for each row in Table C, and the RLS filter uses this measure to control access to the data in Table C.

 

Let me know if you might need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

What does it mean ?  enter the appropriate value to control access to the data in Table C based on the related data in Table B. 

 

Can you please clarify above point

It means that you will need to determine a specific numerical value that will be used to filter the data in Table C. This value will be based on the number of related rows in Table B that are associated with each row in Table C, as calculated by the measure you created in step 1.

 

For example, if you want to restrict access to Table C to only those rows that have at least 5 related rows in Table B, you would enter the value "5" in the RLS filter using the appropriate operator (e.g. "is greater than or equal to"). This would ensure that only users who have permission to access at least 5 related rows in Table B can see the corresponding rows in Table C.

 

Hope this helps.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

But it can be any number as its dynamic.  There is another role which i created on table B

Mail ID=Userprinicipalname()

 

I think we need to use mail id column  on table C. Can you please share views on this.

pranay2k2_1-1677970530689.png

 

 

Anonymous
Not applicable

I tried all available options but unable to acheive correct values for RLS from Table C 😞 

Sahir_Maharaj
Super User
Super User

2. Go to the "Modeling" tab in Power BI Desktop and click on "Manage roles" to create a new role for your RLS filter.

 

3. In the "Manage roles" dialog, click "Create" to create a new role. Give the role a name, such as "Table C RLS".

 

4. In the "Table permissions" section, select Table C and check the "Enable Load" box to allow users to view data in Table C.

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

1. Create a new measure in Table C that counts the number of related rows in Table B for each row in Table C. You can use the RELATEDTABLE function to get a reference to Table B and the COUNTROWS function to count the number of related rows.

 

B Count = COUNTROWS(RELATEDTABLE(B))

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

Here is a thread to create the measure and apply the RLS filter:


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors