Cannot compare text with list in(.....)

[Michael_Venner]

Hi All

I am trying to make a security filter so that personnel can only see certain records but i am stuck.  I have watched a few videos and reviewed some of the helpful posts on here already but i cant figure this out.  I am sure its pretty simple for someone who is experienced.

Basically I have all of my clients assigned to an agent.  Then all of my users are a different agent.  so i want the users to only see the clients whos agent column matches the users agent column.  I have super and admin roles also who can see everything and that appears to be ok in the expression, its the agent part thats the issue.

Here is the expression:

IFS(
ANY(Current_user[Role])="super",
TRUE,
ANY(Current_user[Role])="Admin",
TRUE,
OR(
[Client ID].[Agent]=(current_user[Agent]
)
)
)

I am getting the error "Cannot compare Text with List in ([Client ID].[Agent] = Current_user[Agent])"

I am guessing this is something to do with me using ENUM for the roles, I believe i need to stick an IN() expression in there somewhere?

Any help would be appreciated, been trying to figure this out for days

[WillowMobileSys]

Just like you have used the ANY() function with the role comparison logic,  you also need to use ANY() for the agent logic - red text below.  

IFS(
ANY(Current_user[Role])="super",
TRUE,
ANY(Current_user[Role])="Admin",
TRUE,
OR(
[Client ID].[Agent]=ANY(current_user[Agent]
)
)
)

If you are not aware current_user[column name] construct returns a list of values.  Even when there is a single value returned it is a LIST of 1.  You need to extract the value from the list which is what ANY() does.  More specifically, ANY() extracts the FIRST value from the list but since there is only a single value it works just fine in this case.

[Michael_Venner]

This didnt work, it then said i was using the ANY function incorrectly so i must have something else wrong for this not to work as that was an option i found before and i couldnt get it working.  Thanks for the input

[SkrOYC]

It didn't work because you were using the OR() expression wrong

[Aleksander_Rogo]

I was have this problem, and I was must go to "DATA" ---> ""COLUMNS" and change column to "enumlist" type 🙂 I hope this will work 

[SkrOYC]

Let me ask... why?

[SkrOYC · Accepted solution]

@WillowMobileSys's solution is the right one.

Here is just another way to do the same thing:

OR(
  IN(
    INDEX(Current_user[Role], 1),
    LIST(
      "Super",
      "Admin"
    )
  ),
  [Client ID].[Agent]=INDEX(Current_user[Agent], 1)
)

[Michael_Venner]

This worked perfectly thanks