Security Filter based on admin and user

[G_S]

I am having a table called Employee

I have key value as [Email]

[Role] column 

I want to set a security filter that Admin role people can see all records and user can see only their records 

I used below expression but it throws error 

Expression which is used :

IF(IN(USEREMAIL(),SELECT(Employee[Email ID],[Role]=Admin)),TRUE, [Email ID] = USEREMAIL())

Please suggest correct expression !

[G_S]

Any Suggestion on the above expression?

[G_S]

Please suggest Expression if anyone find the solution

[baba_sawane]

Hi G_S,

Try :

• any(select(Employee[Role], [Email ID] = useremail())) = "Admin"

or

• INDEX(Employee[Role], 1) = "Admin"

[G_S]

Getting Same Errror @baba_sawane 

Table 'Employee' has an invalid security filter '=IF(any(select(Employee[Role], [Email ID] = useremail())) = "Admin",true,[Email ID]=USEREMAIL())'. The filter expression cannot reference the table being filtered.

[baba_sawane]

I suggest you either create a 2nd table for Admin, or use a slice and then filter rows with the above formula.

If you only have 2 roles you can also use userrole() instead of a role column.

[G_S]

No change , I am getting same error , I can't create separate table ,any suggestion to get with existing table @baba_sawane 

[baba_sawane]

Using slices :

user slice : " [Email ID]=useremail() "

admin slice : no filter

Then create a view for both slice with display condition : 

user view : any(select(Employee[Role], [Email ID] = useremail() )) = "User"

admin view : user view : any(select(Employee[Role], [Email ID] = useremail() )) = "Admin" 

I don't know if it will be convenient for you tough

[Aurelien]

Hi @G_S 

You can't use a security filter that is running against the same table.

I would suggest:

- create a mirror table "Employee_Mirror", which content will have the email and any relevant column and will be updated by a bot at any change on your Employee table

For example, Employee_Mirror would have two columns: [Email ID] and [Role]

- use this one to filter your "Employee" table with the security filter

Such expression would be:

OR(
  [Email ID] = USEREMAIL(),
  IN(USEREMAIL(),FILTER("Employee_Mirror",[Role]="Admin")
)

- make sure there is no Ref type column in the mirror table, so that there is no breadcrumb trail that will lead to it

- don't create any view on this mirror table