CONTAINSSTRING checking multiple inputs

[Johannes_W]

Dear all,

I have a situation I am sure should be possible to resolve, but I can not crack it.

I have several unique processes (A, B, C, D .. ) that have error_ids associated to them, some only a few, some hundreds

Table1

Process_id	error_ids
A	tree
A	apple
A	mouse
B	tree
B	horse
C	desk
C	tiger
D	tree
D	mouse
D	horse
D	tiger

All these processes also have messages associated to them, again, some only a few, others up to a hundred.

Only this time, the messages are convoluted strings.

Table2

Process_id	event_message
A	tree mouse sequenze
A	apple pie
A	some text
B	some more
C	desktop
C	mouse soup
D	tree house
D	mouse house
D	wooden desk

I am trying to create a calculated col that gives me true/false if ANY of the error codes that belong to a process appears in the event_message. Or in other words, 'go through the evene messages one by one and check if any of the error codes of the corresponding process appears in the the message'

The expected result would be:

Table2 - with new col

Process_id	event_message	error found?
A	tree mouse sequence	True
A	apple pie	True
A	some text	False
B	some more	False
C	desktop	True
C	mouse soup	False
D	tree house	True
D	mouse house	True
D	wooden desk	False

I have a solution for a SINGLE error code (like 'tree')

error_found? =  
VAR get_error = 
CALCULATE(  
FIRSTNONBLANK( 
Table1[error_ids],1), filter( ALL(Table1), Table1[error_ids] = 'tree' && 'Table2'[Process_ID] = 'Table1'[Process_ID])) 
RETURN 
CONTAINSSTRING('Table2'[even_message], get_error ))

but that is of limited use to me. I also can not predict how many error codes a process could have. So I need to find a solution that turns a column of error codes over to COINTAINSSTRING, somehow.

I apprechiate all your help!

Thanks

Joh

[amitchandak]

@Johannes_W , Try like

new column =
var _cnt = countx(filter(Table1, search(Table1[error_ids], Table2[event_message],,0)>0 && Table1[Process_id] = table2[Process_id]), Table1[Process_id])
return
if(not(isblank(_cnt)), True(), false())

[Johannes_W]

Hi! Thanks for this suggestion, but it returns FALSE for me for every row which can not be true.