Hi, I have the same problem. I needed to classify these texts as "recordable" if not "non recordable"...
these are recordable
Medical treatment beyond first aid
Restricted work case
my syntax goes like this.
Recordable Injuries IF(Injuriesver2[Severity]="Fatality",IF(Injuriesver2[Severity]="Irreversible",IF(Injuriesver2[Severity]="Lost time",IF(Injuriesver2[Severity]="Medical treatment beyond first aid",IF(Injuriesver2[Severity]="Restricted work case","Recordable","Non-recordable")))))
Prepare a simple 2 column table with Text entries in the first column and Category in a second column. The second column should have Recordable and Non-recordable. Thereafter, build a relationship from the text column of your base data to the text column of thsi new 2 column dataset. Write the =RELATED('Table2'[Category]) calculated column formula to fetch data from the Category column to Table1.
I know this is quite old, but this is the closest thread I came to solving what I think is the same need I had, but the proposed solutipms don't solve it. My use case - I have Table A, which has column X, and Table B, which has columns Y and Z. I want to do a fuzzy match on 'Table A'[X] against 'Table B'[Y] and return the value in 'Table B'[Z], so basically a LOOKUPVALUE using search.
In my example, Table A is called "Merged NPA5 and JIRA Query" and column X is "NPAID". Table B is called "FakeJira" and columns Y and Z are, respectively, "External Ticket Id" and "Value To Return". So it looks like this:
Merged NPA5 and JIRA Query:
And this is the result I wanted, where it can find a reference within External Ticket ID, but also handle blanks and duplicates, so gets this as a result:
Here's the query:
LookupBasedJiraID = FIRSTNONBLANK(selectcolumns(filter(FakeJira,search('Merged NPA5 and JIRA Query'[NPAID],FakeJira[External Ticket Id],,0)),"MyColumn",FakeJira[Value To Return]),TRUE())
The only thing now is to sort in the case where there's a duplicate, but that'll be trivial based on some other data.
Anyway, hope this helps - I think this is what the original requestor might have been wanting to do.