Forum Discussion

AdamWhittaker's avatar
AdamWhittaker
Icon for Helper I rankHelper I
3 years ago
Solved

IN Operator where the string is a Field

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,

  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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.

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    Try using

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

     instead of

    { 'Device Type to Application'[AppNames] }
    • AdamWhittaker's avatar
      AdamWhittaker
      Icon for Helper I rankHelper I
      3 years ago

      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

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        3 years ago

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