Calculate Jaccard Similarity between two text strings

Hi jppv20 

If the pairs of strings are known in advance, I would say this task more suited to Power Query. You could create a custom function for Jaccard Similarity.

However, it is interesting to look at doing this with DAX 🙂

I have attached an example with a Jaccard Similarity calculated column (which could be adapted to a measure).

I assumed that strings would be tokenized as words, delimited by spaces.  Does this match your methodolgy? If not, I'm sure it can be modified to suit.

Here is the code for the calculated column:

Jaccard Similarity = 
VAR String1 = Data[Name 1]
VAR String2 = Data[Name 2]
-- Tokenize strings using space
VAR Delimiter = " " 
-- Convert to vertical bar delimiter to allow path functions
VAR Bar = "|" 
VAR String1_BarDelimited =
    SUBSTITUTE ( String1, Delimiter, Bar )
VAR String1_TokenCount =
     PATHLENGTH ( String1_BarDelimited )
VAR String2_BarDelimited =
    SUBSTITUTE ( String2, Delimiter, Bar )
VAR String2_TokenCount =
     PATHLENGTH ( String2_BarDelimited )
VAR String1_Tokens =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, String1_TokenCount ),
        "Token", PATHITEM ( String1_BarDelimited, [Value] )
    )
VAR String2_Tokens =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, String2_TokenCount ),
        "Token", PATHITEM ( String2_BarDelimited, [Value] )
    )
-- DAX INTERSECT function preserves duplicates so is suitable to find intersection
VAR Tokens_Intersection =
    INTERSECT ( String1_Tokens, String2_Tokens )
-- DAX UNION function not suitable as it is equivalent to SQL UNION ALL
-- First get distinct union of tokens from both strings
VAR Tokens_Union_Distinct =
    DISTINCT ( UNION ( String1_Tokens, String2_Tokens ) )
-- Then repeat each token the max number of times it appears in each string
VAR Tokens_Union =
    SELECTCOLUMNS (
        GENERATE (
            Tokens_Union_Distinct,
            VAR CurrentToken = [Token]
            VAR String1_Count =
                COUNTROWS ( FILTER ( String1_Tokens, [Token] = CurrentToken ) )
            VAR String2_Count =
                COUNTROWS ( FILTER ( String2_Tokens, [Token] = CurrentToken ) )
            VAR Max_Count =
                MAX ( String1_Count, String2_Count )
            RETURN
                -- create required number of rows
                GENERATESERIES ( 1, Max_Count ) 
        ),
        "Token", [Token]
    )     
VAR Jaccard =
    DIVIDE (
        COUNTROWS ( Tokens_Intersection ),
        COUNTROWS ( Tokens_Union )
    )
RETURN
    Jaccard

One thing to note is that the DAX UNION function is similar to UNION ALL in SQL, so we need to modify its result to ensure each token is repeated the correct number of times (the max occurrences in any one of the strings).

Regards