Row level security and relationship

[jdusek92]

Hello,

I have this data model:

relationship model:

I want to use Row Level security via UserPrincipalName() expression.

I have set up a role for table Employees:

[UserPrincipalName] = userprincipalname()

When I view report as a specific user only two tables are filtered correctly:

But when I tick "Apply security filter in both directions", it filters all the tables:

My question:

1. Why do I have to tick "Apply security filter in both directions" for the second relationship, but and not for the first? 
2. What exactly does this option do?

sample data here:

https://drive.google.com/file/d/1chwlT_cP3QPvMCC2IH2hxq-TkJhulTLb/view?usp=sharing

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.

Warm regards,

Jakub

[v-danhe-msft]

Hi @jdusek92 ,

This feature is for direct query and by default, the 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 Apply security filter in both directions 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-deskt...

Regards,

Daniel He

[jdusek92]

Apply security filter in both directions 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:

How can I work this around ? I want to filter all the tables based on a record in "Employees" table.

[v-danhe-msft]

Hi @jdusek92 ,

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

Reference:

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships#adjusting-cross-fi...

Regards,

Daniel He

[jdusek92]

Hello @v-danhe-msft , thank you for your research. the reference is very helpful, but still:

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:

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

Example pbix is attached here:

https://drive.google.com/open?id=1Y0sYDcSULkRQGpYWv9nfMzaOVzWFrozo

Try view as role:

Maybe this is a limitation of Row Level Security / Roles ? 

Regards,

Jakub

[Anonymous]

@jdusek92  you added 4 seperate tables and viewing them seperately. You dont visualize data like that right.

   Just add columns from all 4 tables into one table(visual) and see.....

[Anonymous]

HI,

   I have the same issue. Did you get any solution on this

[jdusek92]

To be sure, I include a UserPrincipalName column in each table and set up a role filtering for each table. Also I eliminate the tables that cannot be enriched with UPN column (like Job Catalog)  - data from such tables must be therefore merged+expanded to main datatables. Otherwise I cannot be sure that unwanted information will be hidden.

[Anonymous]

Thanks for your quick reply. But when we have a lookup tables it will be difficult to bring the UserName or UserPrincipal to lookup tables. I think I have a different issue, so I have created another thread here