Forum Discussion
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 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.
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
4 Replies
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 ?
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 _ JaneyThank 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?
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
