IN list not working as expected.

[scit]

I have a user table with an email as KEY, and a department col that is an enum list from a department table.

a user can be in multiple departments. so an example USER table record is this.

FirstName	LastName	email	Department
First	Last	email@email.com	DataStds,IT,Finance,Service,Engineering,Operations,Sales,Manufacturing

trying to determine if a user is in a specific department with IN

in('DataStds',select(User[Department],[email]=USEREMAIL()))

returns FALSE

select(User[Department],[email]=USEREMAIL())

returns 

DataStds,IT,Finance,Service,Engineering,Operations,Sales,Manufacturing

why is this not working?

[scit]

I was able to accomplish what I was after with FIND, but I can't understand WHY IN didn't work... Anyone?

if(find([Worker].[Department],select(User[Department],[email]=USEREMAIL()))>0,TRUE,FALSE)

[Markus_Malessa · Accepted solution]

Using SELECT() of a EnumList column does some funky things since it constructs a list of enumlists. I hope that makes sense. So to get around that I would first suggest the following:

IN("DataStds",SPLIT(SELECT(User[Department],[email]=USEREMAIL()), ","))

Now to avoid a IN() with SELECT() in a single formula I would suggest a security filter on your User datasource or creating a slice "This_User" that filters the User data to the user currently using the app. Then the IN() formula would become one of the following two options:

IN('DataStds',ANY(User[Department]))

IN('DataStds',ANY(This_User[Department]))

[scit]

thank you I assumed it has something to do with the way the data was returned. I did not think of SPLIT!