AND inside a SELECT not working

[Rhage]

Has anyone encountered an issue where in AND() doesn't seem to work inside a SELECT() function.

Here is a snippet of the condition I am using

IFS(

LOOKUP(USEREMAIL(), tblemployees, email_address, user_type) = 'Admin',

SELECT(tblemployees[employeeid], TRUE) - SELECT(tbltmtlmapping[teammemberid], TRUE),

LOOKUP(USEREMAIL(), tblemployees, email_address, user_type) = 'HR Admin',

SELECT(tblemployees[employeeid], 

AND(

  [businessunitid] = LOOKUP(USEREMAIL(), tblemployees, email_address, businessunitid),

    [departmentid] = LOOKUP(USEREMAIL(), tblemployees, email_address, departmentid)

)) - SELECT(tbltmtlmapping[teammemberid], TRUE),

TRUE,

SELECT(tblemployees[employeeid], 

AND(

  [businessunitid] = LOOKUP(USEREMAIL(), tblemployees, email_address, businessunitid),

    [departmentid] = LOOKUP(USEREMAIL(), tblemployees, email_address, departmentid)

)) - SELECT(tbltmtlmapping[teammemberid],

AND(

  [Team Member Business Unit] = LOOKUP(USEREMAIL(), tblemployees, email_address,businessunitid),

    [Team Member Department] = LOOKUP(USEREMAIL(), tblemployees, email_address, departmentid),TRUE)

)

)

it doesn't provide any item on the dropdown list, wherein it should be. I tried it on different fields, doesn't seem to work on my end.

[Suvrutt_Gurjar]

This response is not exactly about the issue you have mentioned in the post. But you could much simplify your expression and get rid of several lookups in the expression and thereby simply it.

Please consider using the pioneering Current User system tip   Current User (Slice) | How to conform your app a... - Google Cloud Community  given by  @MultiTech 

Basically please create a slice called say "Current_User" on the table "tblemployees" with a slice filter expression such as 

[email_address]= USEREMAIL()

Then your following lookup expression 

LOOKUP(USEREMAIL(), tblemployees, email_address, user_type) = 'Admin'

can be reduced to a much simpler

ANY(Current_user[user_type])= "Admin"

Other LOOKUPS() also can be similarly reduced.

[Rhage]

Thanks for the tips @Suvrutt_Gurjar I'll definitely give that a try

[Suvrutt_Gurjar]

You are welcome.