Forum Discussion
Helper IIN Operator where the string is a Field
The expanded version is likely to work better.
The IN operator only works on a list, not a string. It's possible to convert a string into a list (with a mix of SUBSTITUTE, GENERATESERIES, and PATHITEM), but that's almost certainly less efficient than CONTAINSSTRING.
Depending on what you're ultimately trying to do, it's not clear if you even need the 'Device Type to Applications' table. If it's just a helper table, then you can almost certainly do better without it.
Helper IThanks but that would just bring back the unique field values for all rows for that column instead of trying to match the individual words within the column row.
I am trying to find all apps that match whats in the string "ProdA", "ProdZ","ProdXY" so if it has ProdA then bring back that version or ProdZ then bring back that.
If i do 'Applications'[application] IN {"ProdA", "ProdZ","ProdXY"} then it works fine (it will look in the application table to find a row that matches either ProdA or ProdZ or ProdXY) but if i replace {"ProdA", "ProdZ","ProdXY"} with the column that contains that string it doesnt.
I also though maybe it doesnt like doing the IN on a column so read in the field and split it by pipe (after replacing the comma with pipe) and made a one column virtual table but that also didnt work.
Thanks
Super UserAh, OK. That's a bit odd. I'd recommend against trying to store multiple values as a single string.
Is 'Device Type to Application'[AppNames] related to Applications at all? How many rows does it have?
You might be able to get away with something like the following but it's not ideal:
CONTAINSSTRING('Device Type to Application'[AppNames], 'Applications'[application])