Dynamic RLS in One to many relationship

[Anonymous]

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.

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. 

[Sahir_Maharaj]

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.

[Anonymous]

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

[Sahir_Maharaj]

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.

[Anonymous]

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.

[Anonymous]

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

[Sahir_Maharaj]

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.

[Sahir_Maharaj]

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))

[Sahir_Maharaj]

Hello @Anonymous,

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