Row Level Security Question

[willpage]

I have a very simple report that is comprised of two fact tables and a dimension table. The dimension table is just a list of about 10 team names and each fact table has a team name column.

I set up a role for each Team, filtering the dimension table by that team name, published the report then linked each role to an Azure AD group.

This is fine if a person is only in one of the groups. If a person is in two groups, they don't see the data for the two groups in the report, they get an error like: The query encountered security filters on a table relationship that cannot be reconciled for the current user.

How can I have it so a member of more than one group can see the data for each group they are in without setting up explicit roles for those people and creating special AD groups for them? Like it needs to be an OR operation that combines the filters for each role or something?

I'd rather use the existing groups in Azure AD than create new ones just for the security filtering of this one report, but I know if it come to it I can do that, I just want to know the best practice in this scenario, or if I'm doing something wrong with the way I set up the roles.

[v-yanjiang-msft · Accepted solution]

Hi @willpage ,

According to your description, I create a sample and reproduce your problem.

Fact Table1:

Fact Table2:

Dimension table:

If you make relationship between Fact table and dimension table, and filter the dimension table by that team name, the error will occur as you say.

Here's my solution.

1.Create a team table listing all the teams.

2.Make relationship like this:

Fact table and Team table should be single direction, Team table and Dimension table should be both direction, but unselect the "Apply security filter in both directions".

3.In manage roles, filter the Team table by the team name.

Get the result correctly both in Desktop and Service.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

RLS comes in two flavors, static and dynamic.  Have you decided yet which of these you want to implement?