Security filter for table data shared by some users over multiple business units

[Jeffrey_Stokes]

I'm struggling with establishing security within a single dataset that four distinct business units share. 15 users share the dataset. Most have access to just one business unit's data. But some have access to two or three, and at least one user will have access to all four.

The app is specialized in service ticket tracking over multiple service boards. It works great on a single data set for one company (mine). But now, I need to create a copy that will work on a single data set for four distinct business units. 

I've created a table named Master to list the four business units. I've also created a table named Master User List to establish which USEREMAIL() accounts have access to which Business Units.

Master table

Key	Business Unit Name	Address...	etc..
9873jfa3	Alpha Business Unit
839la8x	Omega Business Unit

Master User List table

Key	Master List (Enum List, Ref)	User Name	Appsheet User Email	Etc..
8sliej2	9873jfa3	Alpha User	user1@...
j29kdjfl	9873jfa3, 839la8x	Mixed User	user2@...
8c9sl32	839la8x	Omega User	user3@...

The app has separate tables for Company, Site, and Contacts so users can track multiple locations and contacts for each company. The Tickets table holds all tickets for all companies across all business units.

I've tried to scope out various options to filter which users can access which data entries in Company, Site, Contacts, Tickets, and other similar tables based on the Master and Master User List lookup tables, but it's just not coming together.

Any guidance is very much appreciated. Thank you.

[WillowMobileSys · Accepted solution]

First, to help simply filtering based on the current logged in user,  I recommend following this post on creating a CurrentUser slice.  It allows "clean" access to the current user details.

Once you have created the above, then you can apply a  Security Filter to the "Master" table like so:

In([Key], ANY(CurrentUser[Master List])

With the Security Filter applied to the "Master" table, you can take advantage of "cascade filter" by using the filtered "Master" table to filter other tables.

For example, you might filter the "Company" table like (don't know you column names) :

IN([Business Unit], Master[Key])

Master[Key] provides a list of all row keys but due to the SEcurity Filter it will only be the filtered rows.

The maybe the "Site" table relates to the "Company" table.  You could filter the "Site" table  like so:

IN([Company], Company[Key])

and so on....

I hope this helps!! 

[Jeffrey_Stokes]

Wow! Such an eloquent solution. I'm not all through my tables yet, but It's working like a charm. I created the Current User slice using my existing master and user tables. That worked well with the IN() expressions you provided. The cascading filter is the "bomb." Very powerful. 

Thanks for the insight, knowledge, and assistance!

[Jeffrey_Stokes]

I will work on it later tonight or tomorrow morning and let you know. Thanks!!

[MultiTech]

You might find this video helpful:

[Jeffrey_Stokes]

Yes. Great info. Thank you.