Problem with security filter or slice row condition using lookup

I try to use the following expression as a security filter or as a row condition in a slice but the result is not what I would expect. I want the user (identified by his email) to be able to see only the data of the remaining members of the same Coro (Choir) than him. Why a randomly chosen value from the list? The email is the key value of table Miembros and it should identify uniquely the user if he is in that table and therefore, tell us his choir to compare with the choir of every row and only show the user the rows with his same choir. What am I doing wrong?

[Coro]=lookup(useremail(),Miembros,Email,Coro)

(The value of column ‘Coro’) is equal to (One randomly chosen value from this list (

…The list of values of column ‘Coro’

…from rows of table ‘Miembros’

…where this condition is true: ((The value of column ‘Email’) is equal to (USEREMAIL()))))

1 Like

The LOOKUP() expression is just a wrapper for ANY( SELECT() ). The ANY() expression is labelled as selecting a random value from a List, but really it just selects the first value. In most use-cases of LOOKUP(), you should be specifying a condition that will only return a List of a single item. Nothing to worry about there.

Your expression seems to be correct otherwise.

What result do you get?

1 Like

Even I have noticed some issues with LOOKUP. ANY(SELECT()) just works perfect. I have seen lookup only taking the first value from ENUMLIST. But ANY(SELECT()) Selects the entire value as a list from ENUMLIST.

I am not sure if this is true. When I Use LOOKUP() for an ENUMLIST it only select the first value from that ENUMLIST Column.

From my use case i have found ANY(SELECT()) is not equivalent in to the LOOKUP() Sometimes like mentioned in below image.

Even if i wrap LOOKUP() around LIST() its not giving me right output but ANY(SELECT()) Works perfect.

You can try using

[Coro]=ANY(SELECT(Miembros[Coro],USEREMAIL()=[Email]))