search text string in a column and see if it matches a value in a different tables column

[ScORE]

I need help searcing searching a text string in a column to see if it matches a value in a different tables column.  The tables are not connected.  If the text string contains the value, I want to return another value into a new column.  Sample tables: 

 Table 1                                                                                             Table 2

Full Name	Company		Comeidans on set	Sets today
John Stewart	Laugh out loud		John Stewart, Kevin Hart	1
Terry Fator	Funny Business		Terry Fator, Ricky Gervais	2
Larry David	Laugh until your stomach hurts		Larry David, Chevy Chase	3
Gary Chandler	US laughs		Gary Chandler, Jay Leno	4
Jim Gaffin	Electric comedy		Jim Gaffin, David Letterman	5
Dave Chappelle	Comedic Comedy		Dave Chappelle, Adam Sandler	6
Jerry Seinfeld	Funny Guy		Jerry Seinfeld, Vill Murray	7

Basically, I want too search/look in the comedians on set column, for a match to one the names in my full names column and return the company in a column in the table with comedians on set as a value.  example:

seach comedians on set line 1 find john stewart as a match and return Laugh out loud as avalue in a new column in my comedians on set table.  

Your help is greatly appreciated!!!

[v-chuncz-msft · Accepted solution]

@ScORE,

You may use DAX below to add a calculated column.

Column =
VAR d = ", "
RETURN
    CONCATENATEX (
        FILTER (
            Table1,
            SEARCH ( d & Table1[Full Name] & d, d & Table2[Comeidans on set] & d, 1, 0 )
                > 0
        ),
        Table1[Company],
        d
    )

[AlB]

Hi @ScORE

Can you post the sample data in text format rather than on a screen capture? (so that it can be readily copied)

[ScORE]

Hi @AlB my apologies, I added the text.  

[AlB]

Hi @ScORE

I came up with something that might be overly complicated but hopefully useful.

I've used the names 'NamesTable' and 'SetsTable' for what you called Table1 and Table2. Note that I've also updated the name of the first column of 'SetsTable' to 'Comedians on set' from the 'Comeidans on set' that you had. I'm saying just to spare you "Columns not found" errors.

The code should work regardless of the number of names that you have on 'Comedians on set'. That is why it got a bit long. We are creating a new calculated column, SetsTable[Company], that will show the company names for each comedian, in the same order as the comedians appear, separated by ", ". If a company is not found, the code will return "**NOT FOUND**". You can update all this in the code as required.

Let me know if this helps.

'SetsTable'[Company] = 
VAR _SubsChar = UNICHAR ( 167 )
VAR _SubsChar2 = UNICHAR ( 174 )
VAR _ComediansNames = TRIM ( SetsTable[Comedians on set] )
VAR _NumberItems = 1 + LEN ( _ComediansNames ) - LEN ( SUBSTITUTE ( _ComediansNames; ","; "" ) )
VAR _Res = _SubsChar & SUBSTITUTE ( _ComediansNames; ", "; _SubsChar ) & _SubsChar
VAR _Table1 = GENERATESERIES ( 1; _NumberItems )
RETURN
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                _Table1;
                "SingleName";
                VAR _Start = FIND ( _SubsChar2; SUBSTITUTE ( _Res; _SubsChar; _SubsChar2; [Value] ) ) + 1
                VAR _Ending = FIND ( _SubsChar2; SUBSTITUTE ( _Res; _SubsChar; _SubsChar2; [Value] + 1 ) ) - 1
                VAR _NumChars = _Ending - _Start + 1
                RETURN
                    MID ( _Res; _Start; _NumChars )
            );
            "ComedianCompany";
            VAR _ComedianCompany = LOOKUPVALUE ( NamesTable[Company]; NamesTable[Full Name]; [SingleName] )
            RETURN
                IF ( ISBLANK ( _ComedianCompany ); "**NOT FOUND**"; _ComedianCompany )
        );
        [ComedianCompany];
        ", "
    )

[ScORE]

I also corrected the UNICHAR codes to represent  a comma & space sign.  That seemed to have corrected the issue. However, now I am getting the following error:  an argument of function 'MID' has the wrong data type or has an invalid use.  

[AlB]

@ScORE

---

@ScORE wrote:

I also corrected the UNICHAR codes to represent  a comma & space sign.  That seemed to have corrected the issue. However, now I am getting the following error:  an argument of function 'MID' has the wrong data type or has an invalid use.  

---

You should not  do that. The UNICHAR codes are for the substitute function and are used as delimiters. To make things easier wwhen extracting th names.  That is why they have to be "weird" characters that won't come up in normal text. You cannot change them, certainly not for comma and space.  

[AlB]

@ScORE

Here is an example file with the code working. Have a look. 

[ScORE]

This is how i have it entered:

Company =

VAR _SubsChar = UNICHAR (167)

VAR _SubsChar2 = UNICHAR (174)

VAR _Employees = TRIM ( Submittals[Approvers] )

VAR _NumberItems = 1 + LEN ( _Employees) - LEN ( SUBSTITUTE ( _Employees, ",", "" ) )

VAR _Res = _SubsChar & SUBSTITUTE (_Employees , ",", _SubsChar) & _SubsChar

VAR _Table1 = GENERATESERIES ( 1, _NumberItems)

RETURN

CONCATENATEX (

ADDCOLUMNS (

ADDCOLUMNS (

_Table1,

"SingleName",

VAR _Start = FIND ( _SubsChar2, SUBSTITUTE ( _Res, _SubsChar, _SubsChar2, [Value] ) ) + 1

VAR _Ending = FIND ( _SubsChar2, SUBSTITUTE ( _Res, _SubsChar, _SubsChar2, [Value]+ 1 ) ) - 1

VAR _NumChars = _Ending - _Start + 1

RETURN

MID ( _Res, _Start, _NumChars )

),

"ApproverCompany",

VAR _ApproverCompany = LOOKUPVALUE(directory[Company], directory[Full Name],[SingleName])

RETURN

IF ( ISBLANK (_ApproverCompany), "**NOT FOUND**", _ApproverCompany)

),

[ApproverCompany],

", "

)

[AlB]

@ScORE

It probably has to do with the LOOKUPVALUES() finding more than one row with the name for the comedian in the NamesTable  

[ScORE]

i changed it to you revised formula that uses nameswithdelimiters and now i get this error:  

The Search Text provided to function 'FIND' could not be found in the given text.

[AlB]

@ScORE

If you upload the pbix I might be able to have a look

[ScORE]

Ok, I'll have to trim down a copy.  Theres sensitive data in it.  

[ScORE]

Oh, okay, I changed them back to your numbers, but now I am getting an error.  These are lation unicodes 167 § and 174 ®; a section sign and a registered sign.  

[AlB]

@ScORE

What error?

[ScORE]

A table of multiple values was supplied where a single value was expected

[ScORE]

Thanks for the response.  I had to make a some changes because i was getting abunch of error lines. I replaced all the ";"'s with ","'s.  and removed all ther error lines.  However, now I'm getting an error that says:

The Search Text provided to function 'FIND' could not be found in the given text.   

Do you know what could be causing this?  

[AlB]

@ScORE

Yeah but it shouldn't throw that error.

The ";" or the  ", " as code delimiters depends on the locale settings of your machine

[AlB]

@ScORE

the result should look like this

[AlB]

@ScORE

by the way, make sure sure you have the latest version of Power BI installed. I believe the LOOKUPVALUE behaviour has changed slightly recently (it now returns a blank instead of an error when there is no match) and the code assumes that new behaviour.