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?
Community ChampionAnonymous
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
"It is glaring how much more adept than DAX M is for cases like this. What in the query editor looks relatively straightforward becomes rather irksome here."
Looking at your solution, the statement above appears to be a bit of an understatement! And that's not meant to sound like a criticism - the solution works perfectly, thank you so much AlB :)
SGrigaAdvocate I
The code seems to work great. For those who are going to use it, please make sure you replace semicolons with comas.