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 = List.Count(Source),
SourceNotInReferenceCount = List.Count(List.Difference(Source, Reference, Comparer.OrdinalIgnoreCase)),
PercentSourceInReference = 1 - (SourceNotInReferenceCount / SourceCount)
in
PercentSourceInReference

 

However, I now want to apply the same formula to a calculated table, but I can't, because I'm not able to use Power Query. Is there a way to achieve the same outcome with a Measure?


Any help that can be provided would really be appreciated.

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  

6 Replies

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

    Hi Anonymous

     

    It would help immensly if you showed an example based on your data with the expected result. So that what you need is clear.

    Additionally, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

     

     

    That said, and if I understand your requirement correctly, try this, where Table1 is the table that contains the two columns [Name1] and [Name2]

     

     

    Measure =
VAR SourceCount =
    COUNTROWS ( Table1 )
VAR SourceNotInReferenceCount =
    COUNTROWS ( FILTER ( Table1, Table1[Name1] = Table1[Name2] ) )
VAR PercentSourceInReference =
    1 - DIVIDE ( SourceNotInReferenceCount, SourceCount )
RETURN
    PercentSourceInReference

    or, in another interpretation, since duplicate values are supported in List.Difference:

     

    Measure =
VAR SourceCount =
    COUNTROWS ( Table1 )
VAR SourceNotInReferenceCount =
    SUMX (
        DISTINCT ( Table1[Name1] ),
        MIN (
            CALCULATE ( COUNT ( Table1[Name1] ) )
                - CALCULATE (
                    COUNT ( Table1[Name2] ),
                    FILTER ( Table1, Table1[Name2] = EARLIER ( Table1[Name1] ) )
                ),
            0
        )
    )
VAR PercentSourceInReference =
    1 - DIVIDE ( SourceNotInReferenceCount, SourceCount )
RETURN
    PercentSourceInReference
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      CatCat1
      DogDog1
      TreeTrees1
      MarkedMarking0.666666667
      HelloGoodbye0.4
      Hello how are youHello how are you1
      Goodbye how are youGoodbye how are you1
      Stop to smell the flowersI don't care about flowers0.64
      Knock on the doorRattle the window latch0.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?

      • 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