Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure equavalent for string similarity formula

I'm currently using the formula below to compare strings from two columns and give me a line-by-line % match: let Source = Text.ToList([Name1]), Reference = Text.ToList([Name2]), SourceCount ...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

     

     

    Code formatted with  

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

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

 

 

Code formatted with  

Anonymous's avatar
Anonymous
Not applicable
7 years ago

"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 :)