Forum Discussion

Jenni-Sky's avatar
Jenni-Sky
Helper 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.

v-alq-msft's avatar
v-alq-msft
Community Support
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.