Calculate row similarity / fuzzy matching

[DeBIe]

Hi All,

I would like to know if it is possible to calculate the similarity (row-based) between two columns from two related tables. I am trying fuzzy matching, but the problem here is that it's looking at the entire column of the second table, while I need it to check the same row and if possible add a matching %. I've already added the relation. In my example, I have a relationship based on ID.

What my expected result should be

ID	Name	(related table) ID	(related table) Name	Similarity (based on name)
1	Peter	1	Pete	80%

The problem with fuzzy matching is that even though I put a relation on the ID, it will still check the whole column of the names in my 2nd table. That is why I would like to know if it is possible to do this row-based. 

2nd table:

ID	Name
1	Pete
2	Peterp
3	Peters
4	Pete

Thank you for your time.

[Anonymous]

Hi @DeBIe,

AFAIk, DAX formulas not support the fuzzy match. Currently, it only supports fuzzy search based on text functions)

From SQL to DAX: String Comparison  

In my opinion, I'd like to suggest you take a look at power query fuzzy merge functions.

Power query Fuzzy merge 

Regards,

Xiaoxin Sheng

[DeBIe]

Hi @Anonymous ,

Thank you for your reply. Power Query Fuzzy Merge is exactly what I have been trying, but it does not meet my requirements as mentioned in my original post. Probably a nice feature for the future.

[Anonymous]

Hi @DeBIe,

Perhaps you can try to nest the 'filter' into the 'fuzzy merge' function table parameter to do pre-filter before they used in the functions.

In addition, you can also try to creating a custom function to package multiple functions, then you only need to past the parameter to invoke the functions and get result items.
BTW, the similarity rate is hard to manually calculate. (normally they has complex expressions with math comparison with poor performances) Perhaps you can consider calculating the characters that existed in both compare strings.

Regards,

Xiaoxin Sheng