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

[tosho9]

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 table

xyz-001

xyz-002

xyz-003

abc-001

abc-002

…

…

Table 2	Identifier
randomtext_xyz-001_randomtext	xyz-001
randomtext_xyz-002	xyz-002
randomtext_xyz-002	xyz-002
xyz-002_randomtext	xyz-002
abc-002	abc-002
xyz-001	xyz-001

I need to somehow search Table 2 for the terms in table 1 and then create the Identifier column in table 2 with the search term. 

Any help would be greatly appreciated! 

Best wishes, T.

[v-janeyg-msft]

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]

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 · Accepted solution]

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

[BMH]

The column inventory value is calculate by the sum of Cost price an Quantity. But the sum in the buttom is not correct for the coulumn. How can I get it right ?