Can nto compare list with text

[Phil_Waite]

Hi

I have the table below with users and values assigned to the user in column [First Call List To Combine]

In column  [User Id Viewer]  the user can select other users id to add to their own chosen values to their list.

Table is called User Assign Alpha

     [User Id]                                           [User Id Viewer]                        [First Call List To Combine]

Phil@blob.com                                  Bill@blob.com                           Bravo

Bill@blob.com                                    0                                                  Hotel

below is the expression to see if the [District User 2] value of a row in another table appears in a list of values assigned to the user in  [First Call List To Combine]

IN([District User 2],LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" ))

Rows with [District User 2] that matches Bravo are shown.

I need the expression to also include  rows from the other table that have a [District User 2] value that matches the second and possible third etc ) user selcted in  [User Id Viewer] .

Expression should show rows in second table that have a [District User 2] value that matches Bravo and Hotel.

The expression below would work except that [User Id Viewer] is an enumlist so [User Id] can not be compared to it.

AND(IN([District User 2],LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" )),

IN([District User 2],LOOKUP([User Id Viewer], "User Assign Alpha", "User Id", "First Call List To Combine" )))

How can I change the expression to allow for the list

Thanks

phil

Thanks

Phil

[WillowMobileSys]

To clarify something first, the LOOKUP() function is designed to return a single value.  When you use an IN() function and supply a LOOKUP(), it's a bit confusing because that second parameter is expected to be a LIST of values.  

If it's true that the column [First Call List To Combine] is not a list then the first portion of your expression can be :

[District User 2] = LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" )

For the second portion, there is the same argument.  However, since the column  [User Id Viewer]  can have multiple values, you wouldn't be able to use LOOKUP.  Instead, the SELECT() expression is required. 

In words you want to... Match the [District User 2] column with the column [First Call List To Combine], where USEREMAIL() is in the list of values of the column [User Id Viewer]. 

This expression would be:

 [District User 2] = ANY(SELECT(User Assign Alpha[First Call List To Combine], IN(USEREMAIL(), [User Id Viewer])))

Note the use of ANY().  SELECT's return lists.  ANY returns the first value of that list.  The expression above assumes, based on what you implemented before, that the USEREMAIL() would appear in only one row's [User Id Viewer] list.  If that is not true, then an adjustment to this expression will be needed to accurately find a matching row.

Lastly, I am wondering about the use of AND.  AND means, in this case, that you must have a match to the [District User 2] value when you have both the [User ID] = USEREMAIL() and that the USEREMAIL() is in someone's [User Id Viewer] list.

It seems more likely you would want OR,  one or the other is true but not necessarily both are true.  So putting it all together I think you would want this expression:

OR(
   [District User 2] = LOOKUP(USEREMAIL(), "User Assign Alpha", "User Id", "First Call List To Combine" ),
   [District User 2] = ANY(SELECT(User Assign Alpha[First Call List To Combine], IN(USEREMAIL(), [User Id Viewer])))
)

I hope this helps!!