topic Re: IN Operator where the string is a Field in DAX Commands and Tips

IN Operator where the string is a Field

AdamWhittaker — Wed, 21 Dec 2022 19:51:55 GMT

Hello,

I am trying to find an application version based on a string of comma delimetered values. If i add the values manually in the DAX Calculated column then it works fine but if i use a field that contains the string then it doesnt not find any matches.

This works:

MAXX(FILTER('Applications', 'Applications'[application] IN {"ProdA", "ProdZ","ProdXY"} && 'Applications'[id] = 'Devices All'[id]),'Applications'[version])

but if i subsitute in a field that contains exactly the same string "ProdA", "ProdZ","ProdXY" then it doesnt find any matches.
Like:

MAXX(FILTER('Applications', 'Applications'[application] IN {'Device Type to Application'[AppNames]} && 'Applications'[device id] = 'Devices All'[device id]),'Applications'[version])

I tried outputting 'Device Type to Application'[AppNames] in the same calculated columns and the output matches exactly ("ProdA", "ProdZ","ProdXY").

So does the IN not work when you contruct a table with {} and a column name?

Thanks,

Re: IN Operator where the string is a Field

AlexisOlson — Wed, 21 Dec 2022 21:27:15 GMT

Try using

VALUES ( 'Device Type to Application'[AppNames] )

instead of

{ 'Device Type to Application'[AppNames] }

Re: IN Operator where the string is a Field

AdamWhittaker — Wed, 21 Dec 2022 21:40:22 GMT

Thanks 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

Re: IN Operator where the string is a Field

AlexisOlson — Wed, 21 Dec 2022 22:07:26 GMT

Ah, 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])

Re: IN Operator where the string is a Field

AdamWhittaker — Thu, 22 Dec 2022 00:03:10 GMT

No its not related.

I have 3 tables like this

Devices

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.

Re: IN Operator where the string is a Field

AlexisOlson — Thu, 22 Dec 2022 02:03:58 GMT

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.