Partial match from unrelated tables .

[mahajan]

I have two tables ( not related to each other) A and B. Both of the tables contains about 500,000 rows.

Columns as 

A -      Name                      |      Revenue         |       Industry

           Microsoft Company       3.3K                        IT

           Microsoft technology     1.1K                       IT

           HOnda                             5K                         Auto

B-  Company_name     |        Index ( Unique Values) 

       Microsft                          1 

        Toyota                            2

        Honda of Texas              3

I want to write a function/Query/ DAX which will create a column in the table A, Basically saying that "take name from the Table A, compare it with Company_name in table B ( partially) and return back the related Index value in Table do it for each row".

which will make table A like this:

A -      Name                      |      Revenue         |       Industry |     Index 

           Microsoft Company       3.3K                        IT                   1

           Microsoft technology     1.1K                       IT                    1

           HOnda                             5K                         Auto                3

[v-cherch-msft]

Hi @mahajan

You may refer to below formula to create a calculated column.

Index =
CALCULATE (
    MAX ( B[Index] ),
    FILTER (
        B,
        SEARCH ( A[Name], B[Company_name],, 0 ) > 0
            || SEARCH ( B[Company_name], A[Name],, 0 ) > 0
    )
)

Regards,

Cherie

[mahajan]

Thanks for your reply can you put the comments on the code stating what each step is doing? Can It also do the fuzzy string comparison ?

[v-cherch-msft · Accepted solution]

Hi @mahajan

You may refer to SEARCH Function. This could help do the fuzzy string comparison. So the two conditions in formula are doing it. If the conditions match, then it returns the index value. Below is the post about SEARCH Function.

https://community.powerbi.com/t5/Desktop/DAX-FIND-or-SEARCH-part-of-a-string-in-a-column/td-p/134518

Regards,

Cherie