Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
08-16-2021 18:58 PM - last edited 08-16-2021 19:00 PM
Couldn't help doing this in DAX. Comes from this thread.
Jaccard =
VAR __Table = DISTINCT('Recipe Ingredients'[Recipe])
VAR __Table1 = GENERATE(SELECTCOLUMNS(__Table,"Recipe 1",[Recipe]),SELECTCOLUMNS(__Table,"Recipe 2",[Recipe]))
VAR __Table2 = FILTER(__Table1,[Recipe 1] <> [Recipe 2])
VAR __Table3 = ADDCOLUMNS(__Table2,"1",RIGHT([Recipe 2],1),"2",RIGHT([Recipe 1],1))
VAR __Table4 = FILTER(__Table3,[1]<[2])
VAR __Table5 = ADDCOLUMNS(__Table4,"Recipe","Recipe " & [1] & "&" & [2])
VAR __Table6 = ADDCOLUMNS(__Table5,"Total",
COUNTROWS(
DISTINCT(
UNION(
SELECTCOLUMNS(FILTER('Recipe Ingredients',[Recipe] = [Recipe 1]),"Ingredient",[Ingredient]),
SELECTCOLUMNS(FILTER('Recipe Ingredients',[Recipe] = [Recipe 2]),"Ingredient",[Ingredient])
)
)
)
)
VAR __Table7 = ADDCOLUMNS(__Table6,"Same",
COUNTROWS(
INTERSECT(
SELECTCOLUMNS(FILTER('Recipe Ingredients',[Recipe] = [Recipe 1]),"Ingredient",[Ingredient]),
SELECTCOLUMNS(FILTER('Recipe Ingredients',[Recipe] = [Recipe 2]),"Ingredient",[Ingredient])
)
)
)
VAR __Table8 = ADDCOLUMNS(__Table7,"Jaccard Index",DIVIDE([Same],[Total],0))
RETURN
SELECTCOLUMNS(__Table8,"Recipe",[Recipe],"Jaccard Index",[Jaccard Index])
eyJrIjoiNzIzYTM3MzQtOWFhZS00YjU5LWE3NTctMDE4NGViNDNjNzY2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9