Forum Discussion

Jenni-Sky's avatar
Jenni-Sky
Icon for Helper I rankHelper I
6 years ago
Solved

Compare Two Columns and Check for Partial Match

Hi,    I have a table of data which includes a "Customer Name" column and an "Invoice Customer" column.    I am looking to create a "Match" column, to check whether Customer Name and Invoice Name...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Jenni-Sky 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column as below.

    Match = 
var _cname = 'Table'[Customer Name]
var _iname = 'Table'[Invoice Customer]
var _firstname = LEFT(_cname,SEARCH(" ",_cname)-1)
var _lastname = MID(_cname,SEARCH(" ",_cname)+1,LEN(_cname))
return
IF(
    EXACT([Customer Name],[Invoice Customer]),
    "Match",
    IF(
           NOT(CONTAINSSTRING(_iname,_firstname))&&NOT(CONTAINSSTRING(_iname,_lastname)),
           "Not Match",
           IF(
               CONTAINSSTRING(_iname,_firstname)||CONTAINSSTRING(_iname,_lastname),
               "Partial Match"
           )
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi Jenni-Sky 

 

I have answered to a similar question on the post below:

https://community.powerbi.com/t5/Desktop/Matching-and-returning-a-list-of-all-matching-words-within-a/m-p/1056040

 

this answer was based on the blog post below:

 

https://radacad.com/quick-dax-word-count

https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-ta...

 

Looking at you concrete problem what you can do is the following column:

 

Output to a Table = 
VAR Sentence = 'FInd'[Customer Name]
VAR SentenceCleaned = " " & Sentence & " "
VAR LengthOfSentence = LEN(SentenceCleaned)
VAR PivotedSentence  = 
    ADDCOLUMNS(
        GENERATESERIES(1,LengthOfSentence) ,
        "Letter" , 
        MID(SentenceCleaned,[Value],1)
    )  
var Boundaries = 
  ADDCOLUMNS(
    PivotedSentence ,
    "PrevSpace", MAXX(FILTER(PivotedSentence ,''[Value] < EARLIER([Value]) && [Letter] = " "),[Value]) + 1,
    "NextSpace", MINX(FILTER(PivotedSentence ,''[Value] > EARLIER([Value]) && [Letter] = " "),[Value]) - 1 
       )
VAR TableOfWords =        
    SELECTCOLUMNS(
        SUMMARIZE(
             FILTER(Boundaries, not [Letter] IN {" "}) ,
            [PrevSpace]
            ),"Word Position",[PrevSpace]
            ) 
    
VAR TableOfWords2 = 
    ADDCOLUMNS(
        TableOfWords,
        "Word",
        CONCATENATEX(
            FILTER(
                Boundaries,[PrevSpace]=[Word Position]),
                [Letter],
                ,
                [Value]
                )
        )
       
RETURN 
iF('FInd'[Customer Name] = 'FInd'[Invoice Customer], "Match",
IF(
      SUMX(TableOfWords2,
           SEARCH(
                upper(TRIM([Word])),
                upper(TRIM('FInd'[Invoice Customer]))
                ,,0
               )
          ) 
> 0,
      "Partial Match",
      "No Match"
     ))

 

 

Check the PBIX file attach.

 

Please be aware that this may need some changes because the data you have given was very reduced but believe can work correctly,is also possible to change ti to a measure however the performance will suffer a lot.