Forum Discussion
Measure equavalent for string similarity formula
Anonymous
It is remarkable how much more adept than DAX M is at cases like this. What in the query editor looks relatively straightforward becomes rather irksome here. You can create your calculated column in the table you show (Table1):
Edited: Removed var _Word2 which was no longer necessary
Similarity = VAR _String1 = LOWER ( Table1[Name1] ) VAR _String2 = LOWER ( Table1[Name2] ) VAR _Word1 = ADDCOLUMNS ( GENERATESERIES ( 1; LEN ( _String1 ) ); "Letter"; MID ( _String1; [Value]; 1 ) ) VAR _SourceNotInReferenceCount = SUMX ( SUMMARIZE ( _Word1; [Letter]; "Occurrences"; MAX ( ( LEN ( _String1 ) - LEN ( SUBSTITUTE ( _String1; [Letter]; "" ) ) ) - ( LEN ( _String2 ) - LEN ( SUBSTITUTE ( _String2; [Letter]; "" ) ) ); 0 ) ); [Occurrences] ) VAR _SourceCount = LEN ( _String1 ) VAR _PercentSourceInReference = 1 - DIVIDE ( _SourceNotInReferenceCount; _SourceCount ) RETURN _PercentSourceInReference
Hi AlB, thanks or your response, and your advice. Apologies for not being as clear as I could have been. I've mocked up some example results using the initial formula, the results of which are below:
Name1 Name2 Similarity
| Cat | Cat | 1 |
| Dog | Dog | 1 |
| Tree | Trees | 1 |
| Marked | Marking | 0.666666667 |
| Hello | Goodbye | 0.4 |
| Hello how are you | Hello how are you | 1 |
| Goodbye how are you | Goodbye how are you | 1 |
| Stop to smell the flowers | I don't care about flowers | 0.64 |
| Knock on the door | Rattle the window latch | 0.647058824 |
I've had a play around with your measures (thanks again), but unfortunately all I'm getting is a similarity rating of '1' for each line. I think I wasn't as clear as I should have been with my requirements.
Hopefully this helps to clear things up a bit?
Yo quiero revisar similitudes pero en una misma columna como podria hacer eso?}