Row level security and relationship

Question

Hello,I have this data model:relationship model:&nbsp;I want to use Row Level security via UserPrincipalName() expression.I have set up a role for table Employees:[UserPrincipalName] = userprincipalname()&nbsp;When I view report as a specific user only two tables are filtered correctly:&nbsp;&nbsp;But when I tick "Apply security filter in both directions", it filters all the tables:&nbsp;&nbsp;My question:Why do I have to tick "Apply security filter in both directions" for the second relationship, but and not for the first?&nbsp;What exactly does this option do?&nbsp;sample data here:https://drive.google.com/file/d/1chwlT_cP3QPvMCC2IH2hxq-TkJhulTLb/view?usp=sharing&nbsp;As you can see I want to use Power BI and Row Level Security for HR reporting with sensitive information. I want to be sure that employees cannot reach irrelevant data.&nbsp;Warm regards,Jakub&nbsp;

v-danhe-msft · Answer

Hi&nbsp;jdusek92&nbsp;,
This feature is for direct query and by default, the&nbsp;row-level security filtering uses single-directional filters, regardless of whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the&nbsp;Apply security filter in both directions&nbsp;checkbox to apply bi-directional.
Reference:
https://docs.microsoft.com/en-us/power-bi/desktop-bidirectional-filtering
https://docs.microsoft.com/en-us/power-bi/service-admin-rls#define-roles-and-rules-in-power-bi-desktop
&nbsp;
Regards,
Daniel He

jdusek92 · Answer

Apply security filter in both directions&nbsp;does the trick indeed.But when I want to have more tables and apply RLS to all of them, I am getting this error message:&nbsp;How can I work this around ? I want to filter all the tables based on a record in "Employees" table.&nbsp;

v-danhe-msft · Answer

Hi&nbsp;jdusek92&nbsp;,
Based on my research, it is due to your relationship will result in a circle filtering that&nbsp;Power BI will not let to&nbsp; create a full circle of relationships between tables due to ambiguity&nbsp;in filtering.&nbsp;

Reference:
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships#adjusting-cross-filter-direction-for-a-complex-set-of-tables-and-relationships
&nbsp;
Regards,
Daniel He

jdusek92 · Answer

Hello&nbsp;v-danhe-msft&nbsp;, thank you for your research. the reference is very helpful, but still:&nbsp;I understand that I cannot create circular relationships like you show show in the picture.According to your reference it is recommended to use a Star Schema:&nbsp;In my opinion I am using this type of schema, I do not have any circular relationships:Yet I cannot use RLS filtering from EMPLOYEES table to filter BOTH look-up tables (JOBS and CHAIRS). I can be set to filter only one at a time. I need all the related tables to be filtered by RLS&nbsp;Example pbix is attached here:https://drive.google.com/open?id=1Y0sYDcSULkRQGpYWv9nfMzaOVzWFrozoTry view as role:&nbsp;Maybe this is a limitation of Row Level Security / Roles ?&nbsp;&nbsp;Regards,Jakub&nbsp;

anonymous · Answer

jdusek92&nbsp; you added 4 seperate tables and viewing them seperately. You dont visualize data like that right.&nbsp; &nbsp;Just add columns from all 4 tables into one table(visual) and see.....

Forum Discussion

Row level security and relationship

8 Replies

Related content

Role Level Security

Page level security

Dynamic Row level security

page level security

Dynamic Row level security issue