Forum Discussion

tosho9's avatar
tosho9
Frequent Visitor
4 years ago
Solved

Help with multiple value search (array or table) with direct query

Hello,    I struggle to find the correct solution for my problem:   I have two direct query tables. Table 1 contains search terms, Table 2 contains the fields to be searched: Search tems tabl...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, tosho9 

     

    I don't know this function can't be used in DQ column, but you can try to use measure to display results in table visual.

    Like this:

    Measure 2 = CALCULATE (
        MAX(  'Table'[Search tems table] ),
        FILTER (
            ALL ( 'Table' ),
            CONTAINSSTRING (max('Table (2)'[Table 2]), [Search tems table])
              
        )
    )

     

    Best Regards,
    Community Support Team _ Janey

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

Hi, tosho9 

 

You can create a column.

Like this:

Column = CALCULATE (
        MAX(  'Table'[Search tems table] ),
        FILTER (
            ALL ( 'Table' ),
            CONTAINSSTRING ('Table (2)'[Table 2], [Search tems table])
              
        )
    )

 

CONTAINSSTRING function (DAX) - DAX | Microsoft Docs

Below is my sample.

 

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

  • tosho9's avatar
    tosho9
    Frequent Visitor
    4 years ago

    Thank you very much! 

    Unfortunately I get the error, that the function calculate cannot be used in direct query.

     

    Do you have any Idea how I could do the task with direct query? 

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      4 years ago

      Hi, tosho9 

       

      I don't know this function can't be used in DQ column, but you can try to use measure to display results in table visual.

      Like this:

      Measure 2 = CALCULATE (
        MAX(  'Table'[Search tems table] ),
        FILTER (
            ALL ( 'Table' ),
            CONTAINSSTRING (max('Table (2)'[Table 2]), [Search tems table])
              
        )
    )

       

      Best Regards,
      Community Support Team _ Janey