\nSilver Snakes<\/p>\n<\/td>
\nBruce.Wayne<\/p>\n<\/td><\/tr><\/tbody><\/table>Table 1 \u2013 Original Table<\/strong><\/figcaption><\/figure>\n\n\n\n<\/p>\n\n\n
\n
\nSet up The Table<\/strong> \u2013 Before applying the cosine similarity algorithm you need to create a table where each row represents the group and each column represents each individual member (e.g., universal principal name\/email address). Table 2 provides an example. Notice that 1 represents that the individual member is a member of the group and 0 represents that the individual member is NOT a member of the group.<\/li>\n<\/ol>\n<\/div>\n\n\n
\n<\/li>\n<\/ol>\n<\/div>\n\n\n<\/p>\n\n\n\n\nGroup Name<\/strong><\/p>\n<\/th>\nJane.Doe<\/strong><\/p>\n<\/th>\nJohn.Doe<\/strong><\/p>\n<\/th>\nBarry.Allen<\/strong><\/p>\n<\/th>\nDiana.Prince<\/strong><\/p>\n<\/th>\nBruce.Wayne<\/strong><\/p>\n<\/th><\/tr><\/thead>\nBlue Barracudas<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n0<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n0<\/p>\n<\/td><\/tr>
\nOrange Iguanas<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n0<\/p>\n<\/td><\/tr>
\nPurple Parrots<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td><\/tr>
\nSilver Snakes<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td>
\n1<\/p>\n<\/td><\/tr><\/tbody><\/table>Table 2 \u2013 Set up The Table<\/strong><\/figcaption><\/figure>\n\n\n\n<\/p>\n\n\n
\n
\nConvert the Columns to Vectors<\/strong> \u2013 Using Power Query we get the individual members (column names) and their value (1,0) and convert that to a Power Query list. We are left with a table with two columns Group Name, and Membership List. Table 3 provides an example of the transformed table.<\/li>\n<\/ol>\n<\/div>\n\n\n<\/p>\n\n\n\n\nGroup Name<\/strong><\/p>\n<\/th>\nMembership as Vectors<\/strong><\/p>\n<\/th><\/tr><\/thead>\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td><\/tr>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td><\/tr><\/tbody><\/table>Table 3 \u2013 Example of Table with Membership as Vectors (Membership List column)<\/strong><\/figcaption><\/figure>\n\n\n\n<\/p>\n\n\n
\n
\nCross Join the Table<\/strong> \u2013 Next, we need to Cross Join the Tables. This will create a permutation of each Group to compare. We also remove rows that would compare the same group and remove duplicate combinations; moreover, we don\u2019t need both Red Jaguars compared to Blue Barracudas and Blue Barracudas compared to Red Jaguars. Table 4 provides an example output.<\/li>\n<\/ol>\n<\/div>\n\n\n<\/p>\n\n\n\n\nGroup Name<\/strong><\/p>\n<\/th>\nMembership as Vectors<\/strong><\/p>\n<\/th>\nGroup Name 2<\/strong><\/p>\n<\/th>\nMembership as Vectors 2<\/strong><\/p>\n<\/th><\/tr><\/thead>\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td><\/tr>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td><\/tr>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td><\/tr>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td><\/tr><\/tbody><\/table>Table 4 \u2013 Cross Joined Table<\/strong><\/figcaption><\/figure>\n\n\n\n<\/p>\n\n\n
\n
\nConduct Cosine Similarity<\/strong> \u2013 Now it\u2019s time to apply the algorithm by taking each Membership as Vectors<\/em> and Membership as Vectors 2<\/em> and then calculate the dot product of the two lists (a.k.a. vectors), calculate the magnitude of each vector, calculate the square root for each magnitude and then divide the dot product by the square roots multiplied. This must be done with a custom Power Query function, and Figure 1 provides the current code.<\/li>\n<\/ol>\n<\/div>\n\n\n(group1 as list, group2 as list) =>\n let\n \/\/ Extract values of both groups\n vec1 = group1,\n vec2 = group2,\n \n \/\/ Calculate dot product\n dotProduct = List.Sum(List.Transform(List.Zip({vec1, vec2}), \n each _{0} * _{1})),\n \n \/\/ Calculate magnitudes of both vectors\n magnitude1 = List.Sum(List.Transform(vec1, each _ * _)),\n magnitude2 = List.Sum(List.Transform(vec2, each _ * _)),\n \n \/\/ Handle case when magnitudes are zero \n \/\/(to avoid division by zero)\n magnitude1Sqrt = if magnitude1 = 0 then 0 else \n Number.Sqrt(magnitude1),\n magnitude2Sqrt = if magnitude2 = 0 then 0 \n else Number.Sqrt(magnitude2),\n \n \/\/ Calculate cosine similarity\n cosineSimilarity = if magnitude1Sqrt = 0 or magnitude2Sqrt = 0\n then 0 else dotProduct \/ (magnitude1Sqrt * magnitude2Sqrt)\n in\n cosineSimilarity<\/pre>\n\n\n\nListing 1 \u2013 fnConsineSimilarity function<\/strong><\/p>\n\n\n\nAnd you now have a calculation that defines the similarity of the group memberships. You can see that the Green Monkeys and Orange Monkeys have the same membership as the Cosine Similarity is 1. We also see the Red Jaguars and Silver Snakes have very similar membership makeup (the difference being just one person).<\/p>\n\n\n\n
<\/p>\n\n\n\n\nGroup Name<\/strong><\/p>\n<\/th>\nMembership as Vectors (String)<\/strong><\/p>\n<\/th>\nGroup Name 2<\/strong><\/p>\n<\/th>\nMembership as Vectors (String) 2<\/strong><\/p>\n<\/th>\nCosine Similarity<\/strong><\/p>\n<\/th>\nList Differences by Name<\/strong><\/p>\n<\/th><\/tr><\/thead>\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\n0.816496581<\/p>\n<\/td>
\nDiana.Prince<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\n0.816496581<\/p>\n<\/td>
\nJohn.Doe<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\n0.816496581<\/p>\n<\/td>
\nJohn.Doe<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\nDiana.Prince, Bruce.Wayne, Jane.Doe, Barry.Allen<\/p>\n<\/td><\/tr>
\nBlue Barracudas<\/p>\n<\/td>
\n{1, 0, 1, 0, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td>
\n0.707106781<\/p>\n<\/td>
\nDiana.Prince, Bruce.Wayne<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\n0.666666667<\/p>\n<\/td>
\nJohn.Doe, Diana.Prince<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\n0.666666667<\/p>\n<\/td>
\nJohn.Doe, Diana.Prince<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td>
\n0.40824829<\/p>\n<\/td>
\nBruce.Wayne, Jane.Doe, Barry.Allen<\/p>\n<\/td><\/tr>
\nRed Jaguars<\/p>\n<\/td>
\n{1, 0, 1, 1, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td>
\n0.866025404<\/p>\n<\/td>
\nBruce.Wayne<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\n1<\/p>\n<\/td>
<\/td><\/tr> \nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\nDiana.Prince, Bruce.Wayne, Jane.Doe, John.Doe, Barry.Allen<\/p>\n<\/td><\/tr>
\nGreen Monkeys<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td>
\n0.577350269<\/p>\n<\/td>
\nDiana.Prince, Bruce.Wayne, John.Doe<\/p>\n<\/td><\/tr>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td>
\n0<\/p>\n<\/td>
\nDiana.Prince, Bruce.Wayne, Jane.Doe, John.Doe, Barry.Allen<\/p>\n<\/td><\/tr>
\nOrange Iguanas<\/p>\n<\/td>
\n{1, 1, 1, 0, 0}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td>
\n0.577350269<\/p>\n<\/td>
\nDiana.Prince, Bruce.Wayne, John.Doe<\/p>\n<\/td><\/tr>
\nPurple Parrots<\/p>\n<\/td>
\n{0, 0, 0, 1, 1}<\/p>\n<\/td>
\nSilver Snakes<\/p>\n<\/td>
\n{1, 0, 1, 1, 1}<\/p>\n<\/td>
\n0.707106781<\/p>\n<\/td>
\nJane.Doe, Barry.Allen<\/p>\n<\/td><\/tr><\/tbody><\/table>Table 5 – Conduct Cosine Similarity<\/strong><\/figcaption><\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n While the examples above are trivial, running this process across hundreds of groups with thousands of users may take some time; yet you can let Power BI do the hard work of identifying groups with similar memberships. You can also extend this to other comparison needs, like finding similar respondents to survey questions or product recommendations.<\/p>\n\n\n\n
If all you have is Power BI, calculating cosine similarity is possible and can help you compare group memberships to find similar groups. If you\u2019re interested in trying this out, we have an example file to share that contains the example tables and code used to calculate cosine similarity.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ll admit upfront\u2014I am not a data scientist by trade. Instead, I’ve picked up my data science skills over time, learning through a combination of osmosis from talented colleagues and tackling real-world data challenges. It\u2019s been a journey of trial, error, and refinement, as I’ve worked to bridge gaps between complex data science techniques and……<\/p>\n","protected":false},"author":344919,"featured_media":104703,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[47,53,159166],"tags":[5764],"coauthors":[159224],"class_list":["post-104693","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-science","category-featured","category-powerbi","tag-data-science"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104693","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/344919"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104693"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104693\/revisions"}],"predecessor-version":[{"id":105104,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104693\/revisions\/105104"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104703"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104693"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104693"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104693"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104693"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}