Multi Comparison in dax of unrelated items

[TFernandes_2022]

Hey all,

I have two tables in my data model: CAV_INT and CAV_SIM, both with 3 columns, and I also have these measures that I use in the visualization.

(Qtd) Incisos = CALCULATE(COUNT(CAV_SIM[Artigos/Incisos]), ALL(CAV_INT))

(Qtd) Incisos Similares = COUNT(CAV_SIM[Artigos/Incisos])

(Qtd) Incisos Dissimilares = [(Qtd) Incisos] - [(Qtd) Incisos Similares]

(%) Similaridade = ROUND([(Qtd) Incisos Similares] / [(Qtd)) Incisos interesse], 1) + 0

(KPI) Dissimilar (Melhor) =

 Var c_sim = CALCULATE(SELECTEDVALUE(CAV_SIM[Artigos/Incisos]), ALL(CAV_INT))

 Var c_int = SELECTEDVALUE(CAV_INT[Artigos/Incisos])



 Return

// Rule 1



          IF( AND(c_sim = "artigo7_inciso_iv", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_v", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xii", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xii", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xiv", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xv", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_iInciso_xvi", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xvii", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xxi", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo7_inciso_xxii", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo8_inciso_iii", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo8_inciso_xii", [(Qtd) Incisos Dissimilares] > 0), 1,

          IF( AND(c_sim = "artigo9_inciso_xi", [(Qtd) Incisos Dissimilares] > 0), 1)))))))))))))

################################  Table 1 ################################

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ld1PjyO3Ecbh77JnH0b/Rq1j9hzAQQCfDGNh5BD0JQaSgODH90j07pCqen/16rSHfVBkFYvsbqln5tdfv/zt689fv729Hc5ffvpyuB5PH//8/t//7//+4/D2bf/Pv/b//fGt7fv+5befLGvDxnD7EbCA8zRZXl8PuXeWtx+wqtAkTbhM8gHOh9utHJqkG9J2L4zdGYrGQFiMrRuYbH8lKtNrvuYEi+R/wF4OvqU7kpwdsZjk5pd9+6y63UgrvO+f8/HtzVifQK+vy94Yympq6UYM1dT0uZpB2tVUZ4eGrns6EGgdzbWppgjnwWXYQ326BimbKEixI8E1ExZOnUMBiss4weaH7K780b7/uF8h7wt4OD68PARzGApZME+5s9vZhS1YMs9hEsmVO4ehEyqGw8Z2qd08v8v1Ia/Hosrgmu882Bu6581eqUYs7RVkZrJWNFMVg6YdRQyHzTuKnTu/ZYNftgHhTj5nokPBNYuZyhy0ubNb2z24eN9TFc8sseewKKpFA0xuHQ1oyrWrbgOei8tLdHm7aGYqc9CndDVc20Vnayq3JuzynUvMc1hj1X0B5icfMTecPe4yw/dxeb6mNwXo8rZihzBvBHCNVNoGyDznDeoXGePlbcBuGfgw5Huxi6JL71mAocrXgpjnvKnZ0Qoo9hrU2F6M5SDlgJMbdzdbdqNpus5OlSZA0VUwwWaNW4RLGyGofK8Rw0HlXtNVKZZN9QtAS61Pzu/jmrBVl8DoRGXQccS8DyiglQeqtHgQyy7Kd/bz3z/g230dTufHk4eOKKgdMxamhn5If5p1SqHqwsWOqGC3h/Yn2euE4rWshrYscg+NPtzl4W7w/ZuCskRauhH7cxsFqipJ0JXFLPWehFr60JbtBelHfd4Z74N+nl0ntdVRrvQ6KN1wCxgfbivY3IjP+WgZ2hPo98+068zZwWpCkfyyu7Iou94ckJEvi9Hl5ghyE2PfBtzqEgUZPrEonR+xmbBwqjEIujJsCqZ+2U1YpK4bM1DVbgT9kE/DHx6teXn7tPLYZLrIbUj6Dr+WzYSFU70Eskwne+qp6HODFrSwokERurIoqGzlSMUNCMFicNXzLP0i2W08w+PbAx4+vypWO4PlSg+DwiO0cOoxI0rRRQhdWUxSdlGkoosIFoOrLmL5NM/jsKe4lLYb8Os/74f2/f8P50tyVe2kwsoIN9/uuBAHDjWsnZmHVZR9jvb+YO/pSx1LvACfb+gFE1XWzovWvRxaMapYMow4uW24/HEQYdqiQYniadfMaNbUdowmi0xFwWxfL/I8wdtg+itBwdKVCCo88xjOhebAU7Lj9Zrze/YA1ZeBo8zyJeVGK4bNWxkcpjtfA72y+M6EzZY7Vjp+giNg+GjCmKLpJnYYLPsCbE04wGueR3DpCmvVrTF3M4dWJCFWDSOabmLHwdLvAHZy6ebFaBju9dnN446DOX93hphoFHI4bFoUUDyqOKi0w1zFwqIzYbOlu2pFZUTPw7otblza0rdT1kwCTBcYFEcTC6xdM6NZk9sxmlxdKh5WRa0uDm26T3YcV5fsBaBl84LbMVzWAqTmXNkhzHsFHCaR37VEJzYGwWZLXg7RLgW0Jzm58Yy18bNdVMlHfaXcMWK6NYF1Hla0jHY4an6W4qjEVGcBrMosOgtcUUC3X9KD6DTab4Mf2qhcY5edRFHl1312LjQHnqsy7ku36j43urSvMJwL2wsQ5bTPMeXnFy6MgU1HLD1dgLmrMc9t3LBv8AJQ7TzWeVyx17Rr7PLiadY9xYO6Rc4OcIzmlfiFpbVDznUZHwxv+BWnkKIy2jUzmje9YtS0CyicXRXTWZNbOvk8mP42T7D0phnY2vHkMF5+gmrWvVR3np26ZDA05W7OcWmCy8Pdsh8/XisdoKh0cHm7aNY9xZPLNyQku7uw2XLHTF5fj7l84w7yVt26RpeWGdSSLDqE+S4PTJzK2nnRmhmNmWoWgM0tS+dVU5uSoT1J003sOhj8AEXlGru0T4MSjyboXDgPPD4XvPHntFHlT7zsdlOa81v2G6TBw4p9SY6YWg6CmK7aHwB5RdKuP48nVHpvV7i0LsA6KVE9gphEXmRIdvdqss5uPADeqodTdMSyHQlqx2B5v4Nzo3mJvlA5O+RclvF8dSsf2SJ8fnm3ZM1i3QzX3XjNjYdl2fLtrZ0Xrcj26T3EesmwJrJZKKLZLEUiYoeTm9h47kzfX16GDU7UGeO5sHuuY7z8PIA8PtXlcX82vzoqsiDYiOVHHziMNs3OnJvt3Gx3LF96QQA2B9uGKm5/Iotvg5dwx3HzJDTrPKx4qoN8izzia74VxHTzpiLGo4oDrYCmbK6b2G2w6hvy6NI2BbUkEZw4CrTDHPLNQamazI3XCpnvIgxouk82fvPF5cDfwUWVlTiqtHbIbGfCZsu5kccvjLgcqhvX6NI1A4bB8n4Hh2Om/Y6ZmsyN116QppvYcbDqvb/o8mfm6PJTKjqxatphFtkVCHO1Ui2mphYXYPNDmpkUZRbdQm5i58Hwx9yEzLe4Zp2UaBZwOGjaUzQosbypiLnx2gvQHRvLLJqF3MQe34Fcjtnvzt2J5Y/r4Dqp7DGcFI+ZN5RmnuIx84YiZjsXYhqqQRm6Q+PiigYl98mu49A7lgd4hPlFLbq8rcDh9NJ+ocl5ubZicvlikCuqkq8auok9vuy5nPSPlAqmLlVRilXTrpvTw2iq+QiaAzcz3lzm8Yx74u/KSK1ZBCiKHFz+4QnFwyzSnQbKToJY3qFYOzOc7UxY5CtaD5ydStUvopvJTWw81p/KDx4izL+MILeUJjixyMGJ/aEdppseVTSmWTu3xKbD2ammAshLlrbKNj7sOFUfdpDDcNk5GlW2rKDmgsCQu5uoyZZhxycOp+IzEWR2vO657gZsriOWrmxQ+acJ4Bq7fHFhchwuP3fA4ah5g447vRPfskYlKqddM0ct4uUVhiQ4nKiwdjiqWWG1yQjiYqhNBrC5bmKnwYq3myMTPaBdd+H+QkCUov2Cy1dYs2rc7F6A6uwmvOyO8RnRiV/BjkpWL8j8UyIY11TI0k+wIlO1I4jFy5uews1VHk98Z/ilUsLlL+xAPJPh5MTxSI5YfhNCzI6HaajzEWBz3cTG88+5eoiMLn9pC5037s7x8l7RrJnhupVEw2DpWRGUahWCmKtoKXDFmomWIjex8Zx0Lr5Rj0xcIbVr7ERHadc9xdHE8aMd1iQ/pAJTLUUQs1WtArC57pPdxrPhuXgGjiwvMjg3mjc3l9nOhd1znQPmrYyLYcb7S/3ywd7++vWJ+LOWOUwOggo2zzGLfZC75GApAvK4selLx+Om381UdF9W737fcH7n63MFPdY81qtxVdcA5KGT60wB+3O5AxUtRs6FnEzeioGpFkP4glzo3Zxu9AuochbvJwrHTKwJOYbn7/BYhYyXlMpxKmr5EPpzXE6I8+MoOdfHU4BqpwJsnnNZMa7oCHIm5AmKdqBK2ytSzFCt8uPPc1wu/LZGLk+iOgGKM0wHfM4mSHWx0pBH3l4fuRdQbQKAPEfV3RpWU7zlLfE+XHVsB6YyBmgGbEVAsZ/JMczbJjCxn8G58ZodsEpFbfzxBx4uyQNw89jTwAA5oGqbAMUqkzMhT1CsHjkeV68e0HX1Hn/140p/2V5BsSzBqWUJMK8iTdB1xcBiXcjZAaux04egxy8s/mD0hw+UTD5GrWAZM7/uRpe3BMXr6PKlRldkom5KHj+69yGzawYykbIOtxfj5ocTOhPyBEWtwbnxmh2wSkWcTY/f2vwBjYMxUrmzIKjrmj1JhKLHgstbVmfsVqYqjOptqPXMzoPR6zyVc+M1N+DaXxhx55gnc+z4jW/hOOfpMoAuv/4A42HDF1sVs50Jl+mNq+2xfHiNMH+yAOeyNY8ARWHImdAujOnceM0OWKUijpnx55eup+pBODrxBAKQ4+VbGJ05rhmvFwmLrokwX2Z0fshl640/dHU90W9Xy51aPYDNc8zU6mnYCqiWRUOeoVg9cjyuXmagxTLPX0LO8DRg9RAQnVqXAEWDkTNhQ5b3F+RrO7eAz6tHte5eLvc99duf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nome = _t, id = _t, #"Artigos/Incisos" = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Nome", type text}, {"id", Int64.Type}, {"Artigos/Incisos", type text}})
in
#"Tipo Alterado"

################################ Table 2 ################################

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ld0xjyO5EYbh/zLxBSN1a7oV7jq4xMAeDDg6LAaGA6MSH2Abgn6+R829nWazvrc+RQ78oIpkVZNsSTv3++8vX75++/r++nqaX355OS3n6eN//vGf/8W//ji9vse//xn//eP99vL9Fw+GL8O2cbclw+tPVyXfSRMWa7T+DFjAJYebmE/Xa1UehOHLeMa69F7Ia15ygPVAr+6Cqu5A6Q6T3fqsK4e4po8EZS5DLu6sVQs/en0+v76WLUzwOEy2TFW7ASwGqjpDwyKg6AztyvXRFR+oXchFLfmlyVO9GYE8VIckQ/mED1Js1wBdVyRWD5mGP5f8t8d58qjJ6bxx8YyVzA0XbmJkyU5eQN95eUkNfVAwVEs6g8uyueXMJUPmhrMTIxMlA+g7Ly+ptGTESLklW6yhLe7C5Q/3ZW2O79OGJHjN1kWrKq9oAXJeXlLjYZi74XyrmBnOi1YtnWiCa3NzsV0Qc8OFnZih6hYNfeflJSW6BRypvKeAcdK0p0DZ4fburR1VS3FrQOaGCzcxO9FSAH3n5SWV3QdAFUMT1WW4c6fm3qoHd4RqlTX0nZeXVHrSAzOD8dDyBxdctXSiaO1OsBa3QmRuuODEqgk0tAOSyqsLjJOKummHWVV1Ee5c25jX4hBH5oYLNzEy1QQaug7nmzfBwIaiffvrh3t9lGGat+u1WuUahi/Dts8EZZoVpqJRzD6pYS3dYbIbCy5g8jxXso942dwVvu8wYPgybOvDQure0PQZ6WZnJyupZR/xrbnP7WBSTw/Kni6N0pW4hn7IJ9JXcniDr1ydXHYHSTc7u9UNuLirPnzEWsNijMkh3+S1ybVsIoJ+yGN1BqtLrukz0s3OTp0BGtoBqyEO7/jCqR4i2MnT1kSX10+pNi2WPV0bhS+JDeiHfCJ9AfM9C1ydWzUwSjc7O9HAAO2A1RDFdjlC2Zha1muuNsLz6yZPn19fqW5n2dNTo/SKXEM/5BPpCyi7GOgz0s3OLr9tnc/NTWOBwnUNfv3bY8d8/P+n+aKPyJqZ4X58jVw7Ht9QkdoRO+4VFdtP4m1Tb/DtuuHClGFmLgJerYmIRdbuRup4kWtsbUy/ANYsXBem7BaZHAcUy0fOYrgueSdrtp/qtSn9eVjJwgzXrwhCcvnKDSybbPthwPymXy1rFq4LU4Y5QD8guv0JaLkib9aho0qrcWoq/XbEZOG6MGWYAywCZnstKJxuuodut4UPln7EGa4zoZv3Ti5tgbZH5b+WMFm4LkwZ5gD9gOjEg6YdLky6ym1zhB8u1CxcF6YMc4BFQLF65CyG65Ket8B2Uz23rZG+yTdcmDLMzEXA/MwAZ8cj5S1fujue20V0Te/dJgvXhSm7YpDjgNnRAsqNZjJcvfS5mFrJ1uLSRWy/cuj2k2BILmu8UQ0f41Sue3imdsyvxeUHmRsu3MTdOnNEMyDD49eiJXPDIcu7VLP9orRbzVpckpCF68KUYQ6wCChWmZzFcF3yWmiGUxVPpHZ9H7dPftbi8zBkbrhwE3e1JccBRW3JWQzXJa+tZvupzk3pr1NqFq4LU4Y7QDsgw+ygBxW4MOk1abps7Kr/XVjNwnVhym6ZyXHAvJUHljepZvvBtfvPtbhkIgvXhSnDHGARML/xg3PjuWlJecXIO35pLP3q6G66bg4IyaWzGJQ407TrC9Y+H4CfWtXMDNetHjken2gocsTyx1uz3STmdpOH3yGVLMxw3VxHmB0EoDhY+lgQM8N1fTe32/S1eLtB5oYLN3FXDXIcMO9QdBbDdUn7GNh+qu3+ey3eWZCF68KUYQ7QD4hTOfzIRag172XtcBLHHyqVbD+DdktOf2vWLR25Li9Cz93R5e0+sGztLtvZvf/lSdqhyMJ1YcowB+iG47z5pgIO1yXdLYDtp7o2VdwYkIXnXBYI85sqOJzs+PugCjLLK3FtrPg+Blm4LkzZ9Ts5k3Fe0e/kLIbLZxYjeyzav/i7nOqvY0iGGdENZ+dFly3eqNJVOTVVXPOQhevClGEO0A3HefNGRmcxXL60kYHtV+TcVHEbRBauC1OGOUA3nJ0XXXZigCqyppUdVP7aBa479bafP3244mNTZG64cBN3xSVnJzbjMUw/NyXmhvMYJs2fb832K7x9dHk5Fx/pIgvXhSnDHKCf2IznQ9PhEmZvmaTMYDy4vPE0w6T5GzA4LGz6Bry0vexcHEDI3HDhJt43KDoOmNcMncVwXbJaLNvnw5dJ/wONmoXnwkzrhgsbksvOblB91vYWNxWvwMjMcN3ikXPTunl9SC5/9wYXuH5uOfLHB0ZHKn182jvhVLygIwvPhZnWDRc2JJc+PloVWfN6abZb4rW9h07FlzDE9iuMzmSBMJ0rMJxEeg8Etp9pexucildzZOG5MNO64QJh3nToLIaLkhdCM1yR/B0PXLe7ru0CMBX3J2RmuK6w5Hh8omLkLIazzSsGjJOmlR2UqKx2fWWn5oofoBELjpdt66BubjhmYo0Hl34TRswM1y9ye4ueio9IiHWPBTk3b2Di7K0SFA4ur9jA8tfA0aUveGu7Zs/FuwcyM1xXCXIm42mIfYwcsXyH0mw/13aLnYs3FGThuTDT9mtCEAeYNvug8qUDxoMT7a4drsnxr+E01m67c/FGgcwM11WMXGBA0e3kLIazzQsLjJOmBRuUOFC069rk2q7jc/GGgswMt68sOpO502CXNwo6i+HiDZX9+wd7/fGHNOgCVbqwYbg0eJDJApbQczzp5CnK4Xgxq5wbsIv3OPPmNz6tahhPUJTjh1aVswOajvOuT7JqeOtx+8iZaAZwRd5kW93gQ0xX/KfhpbMDhp27f5gJolNdQ9BzvDjjCVu4js3bkzyXGyy5sGG4tK8JQXSqJgQ9x4sjHilwRd58fwXWx9v++ufl8vlF3iQSa3gs3iDVtqThzWRVQHvWoizgioDiRAF3RyfPsrcmy62TnB0w7Nz9dAiiU48pQc/x4uSdODDRNuCK4amGbX+d8kKvlKWzA4adu6/yAFX1CHqOJy0OPu3ccMXwVJm3v1e65P99NtfZAf3cffUIuq5InZdvYKJ84JCJqmx/uOoD0oeIlbv5EQuZnwPgbn7EG7p8tbd/OvDhqnMFXdgwXNovD0F0YmdC6DleHHexVc9q2MU7N1YdF+jChuFSdrK1tXwCmqmRieoNTlVPwy7e3Njnt8NTXpTBpRsiMU5rjm78Zqhwd3Z5MTQzo0UxPNErgxOPErkqtXhCyN3t3HvYzsZz9U6Pzg54mDXQfjYE0alSE/QcL07+FkCuyJs/eMC6eO1vTC8T/Sy8dGGH7Io3QlGUEaaXcWB2PGb57kWOxyeK3P5Q9zJV7wDo7IB+7r56AO8cUZWZoOd4dfKzDJwbrhieXWb1kE4N0i8nSmcHDDt33w8E0al2IOg5XhzRDtoxU1W+NFjdr9HZAf3cffEIolPFI+g5Xpy8KAMTOzG4YnjqEV0bpF9fls4OGHbuvsoE0akqE/QcL44oH7gib3pbItbFm9qOPVcHMzo7YNi5uyojdJ2d+s5QNA5Cz/F653v76ETjADx0xGNnmNs+Mbx/FzLdwoAFOzWVAeZLCMwO+AREJ54qclVq8QxgyD28NFi92qOzAx4mA7R/8gmiUxUk6DlenPwRGNjxGPjLl9++vP/5A6IT/KcJHBi+DNv+qIuUw4I70oTF1IfXFimPv1itYVWfYY8ycttjfGI6vdw2+9OiT3UH+iGPAwV7aKNByjYiacJi6sdjqYZ2wGqIsoumJj+/8Rj+m3aW7OncqD6dHOiHfCZ9RccjvKZRTEq2HEl3mOxUy2loB6yGKFtOy3rN5YZ0aVL/WyQH+iGPAx2s7iNNn5FudnaqPTS0Ax6GuP0U57TqD80d6Id8Jr1PK6mLrmkU05fdQdIdJjtRdO3+HOKv33799v7jPwQxny/wFY0hbwz95IfpSHd91lWJhxJKOVxjDHl7IurNly/fv/8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nome = _t, id = _t, #"Artigos/Incisos" = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Nome", type text}, {"id", Int64.Type}, {"Artigos/Incisos", type text}})
in
#"Tipo Alterado"

My view

These tables are intended to check when selecting a CAV_INT Name:

• How many articles and items are similar in the CAV_SIM Name table, - OK
• How many articles and items are dissimilar, - OK
• % Similarity - OK
• KPI Dissimilar (Best) - Pending

Only the latter (KPI) is pending, managing to assemble the first rule but the other ones I still haven't found a way to solve.

These are the rules
Rule 1: A best B best C best D
Rule 2: B best C best D
Rule 3: C best D
Rule 4 😄

Example: When I choose ABOB_0014, table 1 shows all of its articles/items, and table 2 shows the comparison of how many articles/items are equal and different between them. In the case ABOB_0014 compared with ABOB_0023 are:

• How many articles and items are similar: 7
• How many articles and items are dissimilar: 10
• % Similarity 70%
• KPI Dissimilar (Best): 3

Rule 1: Article 8, Item III (Best of all)
Rule 2: Article 8, Item V (Because Article 7, Item V, which is better, is not in table 1)
Rule 2: Article 9 Item VIII (Because Article 7 Item XVII, which is better, is not in table 1)

For rule 2 and 3 not being able to make this comparison is it possible to check dynamically even without having the relationship?

[amitchandak]

@TFernandes_2022 , You can create common dimension and remove join between both tables and join with common dimension

Similar articles 

M1 = Countrows(T1)

M2 = Countrows(T2)

Similar = Countx(Values(Article[Article]), if(not(isblank([M1])) && not(isblank([M2])) , [Article], blank())

Not Similar =  countrows(Article[Article) - [Similar]

The same way you can calculate others

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...