RLS filtering based on sub-group membership

[gettingupthere]

Hi and glad to add more info if it helps (as with many, I can't share precise data so I apologize in advance). I have 2 huge tables:

AllUserRaw - contains a lengthy list of AD Service accounts with a column of "supportgroup" among others (lots of other columns of info that would interest the owners). That SupportGroup column contains the Service Now support group name per account. It also has a dedicated owner "OwenerSamAccountName" in addition to the support group:

SamAccountName	SupportGroup	OwenerSamAccountName
serviceAccountABC	HelpdeskGroup	fred
serviceAccountZYX	HelpdeskGroup	jane
serviceAccountQWE	Janitors	tom
...	...	...

SnowGroups - is a dump of Service Now that has columns "groupname" and the member's "usernames". Each group is enumerated and shows the username in each group:

GroupName	Username
HelpdeskGroup	fred
HelpdeskGroup	sally
Janitors	mcrib
Janitors	fillet
...	...

The issue in a nutshell: users connect to the dashboard using RLS and it works well to display the rows where they are the "direct owner" from the "AllUserRaw" table (this is easy to use RLS to match the UserPrincipalName to the Owner column). The issue is that if the logged in users is not a direct owner, we still want to display the rows from the Raw Users if they are a member of a support group. (In this example, Sally cannot see any rows because they are not the direct owner yet they are a member of the support group so they still should)

What I tried: I tried linking the 2 tables together in the model by the "groupName" but that throws a "many to many" error as each table has many duplicate values. (eg many service accounts can have the same support group and then the support group table has many people in the same group). I tried using a DAX created bridge table between them but the issue still exists that if Sally connects, the bridge doesn't work correctly and displays nothing. 

I also tried creating a dynamic table using USERPRINCAPLNAME() that would only show support groups of the viewing user but PBI doesn't let you do that in dynamic tables.

What I'm after: I don't mind 2 separate table visuals if it helps/matters: 1 for the "direct view" and the other for "support group view". If there was a filter/DAX way of doing a "reverse lookup" to say "if you're a direct member OR you're a member of a support group attached to that account" then show those rows. (eg filter on snowgroups based on who's logged in, get the unique group name(s) they are a member of as it could be many, and then for each group name, display the associated accounts to those groups)

I hope I'm just overthinking this but I'll try and answer any questions. Thanks in advance!

[lbendlin · Accepted solution]

you can simplify your RLS rules by checking if USERPRINCIPALNAME() is in the same group as the ticket owner.

[gettingupthere]

I understand what you're saying and I also have that working. RLS will also filter the "supportGroup" table. The problem is that the "AllUserRaw" table will then not be what I would call "reverse filtered" based on the support Group table. In other words, both tables are easily filtered with RLS, but I can't display rows from the first based on the second.

[lbendlin · Accepted solution]

Use OR conditions.

(Personal note:  This is the moment in the process where you should ask yourself:" What is the value add of RLS? Is it really, really necessary?")

[v-bmanikante]

Hi @gettingupthere ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @gettingupthere ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @gettingupthere 

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[lbendlin · Accepted solution]

you can simplify your RLS rules by checking if USERPRINCIPALNAME() is in the same group as the ticket owner.