View Filter Experssion Troubleshooting

[mykrobinson]

I am apparently "doing it wrong" and need some guidance to correct an expression. I am building a time tracking app for employees, and I have imported an employee roles table to inform the app of roles an employee may have. The list of employees is in the app, and i currently only want the view showing the list and roles to only be visible to users who have the role "admin."

I have the roles thing configured in a way that allows a user to have multiple roles. For example, one of the doctors has the Provider role as well as the Admin role.

My filter worked fine for me, as my only role is Admin, but it is not working for him in my preview app test. My current expression is:

IN("Admin",LIST(Lookup(useremail(),"User Roles", "Email Address", "Role")))

and if we look at the employee list, you can see that both Shree and I have Admin in the Role, but it doesn't work for him 🤔


What have i done wrong?

Thanks

[Suvrutt_Gurjar]

Please try 

IN("Admin", SELECT(User Roles[Role], [Email Address]=USEREMAIL()))

LOOKUP() returns a single value rather than a list so list gets converted to text and  IN() expects second argument as list. I believe you are getting error because of that. The other person has two roles (Admin, Provider) but LOOKUP() returns a text thereby treating Admin, Provider as a single text instead of a list of two elements. 

[mykrobinson]

I tried that previously because that made sense, but its still not working for some reason 🤔
seems this should work

[SkrOYC · Accepted solution]

IN(
  "Admin",
  SPLIT(
    LOOKUP(
      USEREMAIL(),
      "User Roles",
      "Email Address",
      "Role"
    ),
    " , "
  )
)

If for some reason that doesn't do the trick:

IN(
  "Admin",
  SPLIT(
    CONCATENATE(
      LOOKUP(
        USEREMAIL(),
        "User Roles",
        "Email Address",
        "Role"
      )
    ),
    " , "
  )
)

[mykrobinson]

your first example worked, thank you! If i understand, this is essentially creating multiple single item lists and checking each for the value i am looking for?

Are you able to explain why the initial attempts failed?

[SkrOYC]

IN() looks for a value inside a list.
LOOKUP() returns a value, eventhough it was a list originally, but that's what is returned.
SPLIT() generates a list from a value by taking the string/text and dividing/splitting it into multiple values, a list