Row level security (preview) with multiple tables (having many to one relationship)

[Anonymous]

Hi All,

I am exploring RLS(preview) on service with multiple tables (join - many to one). I have 2 tables having cluster common.

Table -1 : Cluster (Many side in Many to one relationship)

Table -2 : Cluster (One side in Many to one relationship) 

When I applied RLS using - [Cluster] = "XXX" on Table - 1, it is filtering only in Table -1. I can see all other cluster in Table -2 (no impact on table 2 inspite of relationship).

However, When I applied RLS using - [Cluster] = "XXX" on Table - 2 , it is filtering on both the tables.

Can someone guide me on this behaviour? Is this a limitation with current Preview version or something I am missing?

Regards,

Niket Talati

[ankitpatira]

@Anonymous This question is more related about relationships in power bi desktop than service I believe. In power bi desktop in relationship view, edit relationship and check cross filter direction is set to Both. 

[Anonymous]

Hi Ankit,

Thanks For the response.

Actually RLS is a feature of Service so I put my question here. Cross filter direction is set to both and as I said in the descirption that issue is with Many to One (when RLS apply on Many side table) relationship. With One to many, its working fine.

Regards,

Niket Talati

[v-yuezhe-msft]

Hi @Anonymous,

I test the whole scenario as yours, Row level security(RLS) works as expected on both Many side table and One side table.

Could you please share sample data of your tables? I would check that if I can reproduce the RLS issue with your data. In addition, I will appreciate it if you elaborate detailed steps about that how you manage the relationship in Power BI Desktop and how you apply RLS in the dataset.

Thanks,
Lydia Zhang

[Anonymous]

Hi @v-yuezhe-msft,

Below are table-1 (Access) and table-2 (Pie chart data) screenshot with relationship on Cluster field.

Access TablePie Chart Data

RelationshipRLS on serviceResult of RLS

Please use this and let me know if you could reproduce the issue.

Regards,

Niket Talati

[Anonymous]

Hi Guys,

Any help on this issue?

Regards,

Niket Talati

[Vicky_Song]

@Anonymous, I can reproduce your issue and I will reported it internally. I will reply back once I get any updates. 

[Anonymous]

@Vicky_Song, Thank you for your response. I hope it will resolve soon.

[Vicky_Song]

@Anonymous, sorry for the late response. I got an update from our product team that the behavior described in your screnario is by desgin. 

Your tables without RLS have this data:

However you have a relationship (1:Many), which actually states that any [Cluster] value in Access table should have a value In Pie Chart Data table.

When you applying RLS ([Cluster]=”Greater China”) on Pie Chart Data table it applies also on the Access table, otherwise you will have a relationship violation for that role.

[Anonymous]

Hi @Vicky_Song,

Sorry for the late reply. Yes, when I apply RLS on "Pie chart data", its working and its filtering both tables. But however, it is not working when we apply on "Access" table. I have bi-directional filter on the relationship so it should work on both the ways.

I think this should be fixed since it is quite confusing to understand. Please let me know if it is possible to fix.

Regards,

Niket Talati

[Michael1]

Hello,

I am curious if there was a resolution to this, as I am having a similar issue.  If so, could you please provide some additional details?

Thank you,

Michael

[BI1]

I read the original issue differently to this reponse and have the same issue.

A model has four four tables with relationships 1:1, 1:1, 1:Many and Many:1. All have two way joins. The first three are all restricted when row level security is implemented against a username in the first table, however the fourth table with the Many:1 relationship is not. 

When chosing to manually filter data using the same field that row level security is implemented against, the fourth table with the Many:1 relationship is filtered.

Is this the intended behaviour, as it appears inconsistent?

Thanks