Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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())
Hi! Thanks for this suggestion, but it returns FALSE for me for every row which can not be true.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
44 | |
16 | |
12 |