Security Filter for Email and company name

[Aleksander_Rogo]

Im creating client database app. There is 3 companies serving clients. In each of them 3 or 4 people who can see only data  belonging to his company. I want to see in my view all data. Filtering column is called "COMPANY" and EnumList with names of companies. Can someone help me with this expression?

[WillowMobileSys]

First, you will have needed to establish a Users table that indicates which company each user belong to.  The table would need the ID, Login Email, Company...at the very least.

Second, each data table you want to filter MUST have a [Company] column to record the company of the logged in user.  You would set this when the row is created/entered by retrieving the [Company] column value from the Users table where the [Login Email] = USEREMAIL()

Then in each table you need to filter, you would add to the Security Filter something like:

[Company] = SELECT(Users[Company], [Login Email] = USEREMAIL())

I hope it helps!

[Aleksander_Rogo]

Hi, sorry for no answer. I was trying now do it like you told. I Have User table with column: ID, Company, Email. It's working for single email to single company. If I want type twice ex. my email in email column (one for first company, and second for another company) I cant see anything.  SELECT give a list of value from column "company" only if email is correct? Where I have mistake? Thanks for your reply! 

[WillowMobileSys · Accepted solution]

Sorry,  I am not sure what I was thinking that day when I created that expression. 

Based on what you have described, I understand that you can have multiple entries per user in the users table - each for a different company.

The expression needs to be modified slightly to this:

IN([Company], SELECT(Users[Company], [Email] = USEREMAIL()))

The SELECT() returns a List of 1 or more companies associated with the USEREMAIL() - i.e. logged in user.

The IN() function checks that the [Company] associated with the row to be filtered is one of the companies associated with that user.  If it is then it is included, otherwise it is not.

[Aleksander_Rogo]

That's work great! Thank you a lot for helping with this problem!