CONTAINS with ANY(SELECT expression

Phil_Waite · 03-15-2020 10:02 AM

Hi,

I have an enumlist that is made up from another enumlist. Because of this the Valid IF formula is this below with SPLIT

SPLIT(ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL())), " , ")

This works fine except if the first numlist has only one value as there are no “,” in the cell to SPLIT. I get a “This entry is invalid” message.

I want to put an IF function in that asks if the cell CONTAINS “,” if it does then use the first expression above otherwise just EQUAL the single value.

I have added LIST to the last part to give the right typr of result even though there is only one value in the list. It does however give the correct result when I hit “TEST” in the expression assistant!

It’s not working though and won’t even let me toggle to the view with the enumlist in now though.

IF(CONTAINS(ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL())), " , "),

SPLIT(ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL())), " , "),

LIST(ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL()))))

thanks

Phil

Steve

Where does this occur? Screenshot?

Koichi_Tsuji

Not entirely sure what the problem is.

But to generate the dropdown list, why dont you use the expression in Suggested value like

unique(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL()))

To generate the dropdown list without master table, as well as not using split expression, we are are able to get the dropdown list, using one of the most recent implemented expression of unique.

Probably, not addressing to your point, but I saw bunch of post to generate the list (for drop down) with split expression, but for me, unique expression is much easier to do the same.

Phil_Waite

Hi Guys

looks like this expression is now working.

IF(CONTAINS(ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL())), " , "),

SPLIT(ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL())), " , "),

ANY(SELECT(Users[Counties Allowed], [User Id]=USEREMAIL())))

Cheers

Phil