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])
- AdamWhittaker
No its not related.
I have 3 tables like thisDevices
Applications
Device Type to Application
What I want to do is for each device type in Devices find out what application we should be looking for in Applications via Device to Application.
So imagine the tables have the following (simplified):
Devices:
Device ID, Device Name, Device Type
Applications:
Device ID, Application Name, Application Version
Device Type to Application: (this is a table that i made myself to try and find the apps we are after based on the device type):
Device Type, Applications
I currently have Devices tied to Applications via device ID which should give the apps per device and Device Type to Applications tied to Devices via the device type.
So for each device in Devices their Device Type will look into Device Type to Applications to find what applications to look for and from there search in Applications (after filtering on the device id link).
The reason why there is multiple comma delim applications is because one device type can have different names for the application we are looking for depending on the year it was made.
I tried to do it this way as well for Devices to applications:
Device Type A, ProdA
Device Type A, ProdZ
Device Type A, ProdXY
Device Type B, Prod2
Device Type B, Prod4
Device Type B, Prod53
But I coulnt get the relationships working correctly that way hence why i bundled up the apps into a comma delimited list (hoping IN would work)
OH and containsstring might work but there are 200,000 devices with a few hundred apps each so not sure what the performance would be.
Thanks again for your help.
- AlexisOlson
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.