{"id":106,"date":"2023-11-09T20:25:00","date_gmt":"2023-11-09T19:25:00","guid":{"rendered":"https:\/\/bitwise.exposed\/?p=106"},"modified":"2023-11-16T18:11:24","modified_gmt":"2023-11-16T17:11:24","slug":"cardinality","status":"publish","type":"post","link":"https:\/\/bitwise.exposed\/index.php\/2023\/11\/09\/cardinality\/","title":{"rendered":"CallBackID issues: Cardinality"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Abstract, from a SQLBI article. Link to the original article on SQLBI.com click here<a href=\"https:\/\/sql.bi\/816722\">https:\/\/sql.bi\/816722<\/a>. I just could recommend to read every single article from SQLBI, Alberto Ferrari XOR Marco Russo in full length. Below some key pattern and notes from my side as my personal abstract.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Optimizing callbacks in a SUMX iterator<\/h4>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Optimizing callbacks in a SUMX iterator\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/jR0Hhn3X0M8?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">What was the reason for this article from SQLBI, Alberto Ferrari. The function SUMX is iterating over Sales in the contoso database.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);--cbp-line-highlight-color:rgba(0, 0, 0, 0.2);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\" Sales[Sales Amount] =\n        SUMX (\n            Sales,\n            Sales[Quantity] * Sales[Net Price]\n        )\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Sales Amount]<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">SUMX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Quantity]<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">*<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Net Price]<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        )<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Marco Russo would say <em><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-variant-text-accent-color\">&#8222;so far so good&#8220;<\/mark><\/em>, but happens when &#8218;Sales[Net Price]&#8216; will be wrapped by the ROUND function.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);--cbp-line-highlight-color:rgba(0, 0, 0, 0.2);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"Sales[Sales Amount] =\n        SUMX (\n            Sales,\n            Sales[Quantity] * ROUND ( Sales[Net Price], 1 )\n        )\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Sales Amount]<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">SUMX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Quantity]<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">*<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">ROUND<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Net Price]<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        )<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Now, the intervention of the formula engine is required and we generate a callback. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If there&#8217;s no possibility to avoid callbacks between SE &amp; FE, this approach aims at reducing the number of rows by taking advantage of the cardinality from Sales[Net Price] and so reducing the number of neccecary iterations responsible for the callbacks. The sales table in that case has around 200mio rows, but Sales[Net Price] only around 25k unique values. <\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);--cbp-line-highlight-color:rgba(0, 0, 0, 0.2);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"Sales[Sales Amount] =\n        SUMX (\n            SUMMARIZE (\n                Sales,\n                Sales[Net Price]\n            ),\n            CALCULATE ( SUM ( Sales[Quantity] ) ) * ROUND ( Sales[Net Price], 1 )\n        )\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Sales Amount]<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #0000FF\">SUMX<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">SUMMARIZE<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">                <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">                <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Net Price]<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">            ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0000FF\">SUM<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Quantity]<\/span><span style=\"color: #000000\"> ) ) <\/span><span style=\"color: #0000FF\">*<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">ROUND<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Net Price]<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        )<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Therefore, instead of iterating over the sales table, the Sales table will be grouped by Sales[Net Price].<\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h4 class=\"wp-block-heading\">Variation<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Introducing variables in that case, will make the code more readable but hurts performance. <\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);--cbp-line-highlight-color:rgba(0, 0, 0, 0.2);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">DAX<\/span><span role=\"button\" tabindex=\"0\" data-code=\"Sales[Sales Amount] =\n        VAR RoundedNetPrices =\n            ADDCOLUMNS (\n                SUMMARIZE ( Sales, Sales[Net Price] ),\n                &quot;@Rounded Net Price&quot;, ROUND ( Sales[Net Price], 1 ),\n                &quot;@Sum Of Quantity&quot;, CALCULATE ( SUM ( Sales[Quantity] ) )\n            )\n        VAR Result =\n            SUMX ( RoundedNetPrices, [@Rounded Net Price] * [@Sum Of Quantity] )\n        RETURN\n            Result\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Sales Amount]<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">RoundedNetPrices<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">ADDCOLUMNS<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                <\/span><span style=\"color: #0000FF\">SUMMARIZE<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Net Price]<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                <\/span><span style=\"color: #A31515\">&quot;@Rounded Net Price&quot;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #0000FF\">ROUND<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Net Price]<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\"> ),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                <\/span><span style=\"color: #A31515\">&quot;@Sum Of Quantity&quot;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #0000FF\">CALCULATE<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0000FF\">SUM<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">Sales<\/span><span style=\"color: #795E26\">[Quantity]<\/span><span style=\"color: #000000\"> ) )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #AF00DB\">VAR<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0070C1\">Result<\/span><span style=\"color: #000000\"> =<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0000FF\">SUMX<\/span><span style=\"color: #000000\"> ( <\/span><span style=\"color: #0070C1\">RoundedNetPrices<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #795E26\">[@Rounded Net Price]<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">*<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #795E26\">[@Sum Of Quantity]<\/span><span style=\"color: #000000\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">        <\/span><span style=\"color: #AF00DB\">RETURN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">            <\/span><span style=\"color: #0070C1\">Result<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This pattern with ADDCOLUMNS will be slower, because it requires two scans of the sales table, details see <a href=\"https:\/\/sql.bi\/816722\"> https:\/\/sql.bi\/81672<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Abstract, from a SQLBI article. Link to the original article on SQLBI.com click herehttps:\/\/sql.bi\/816722. I just could recommend to read every single article from SQLBI, Alberto Ferrari XOR Marco Russo in full length. Below some key pattern and notes from my side as my personal abstract. Optimizing callbacks in a SUMX iterator What was the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":135,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"[]"},"categories":[11,7,16],"tags":[],"class_list":["post-106","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-callbacks","category-dax","category-pattern"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=106"}],"version-history":[{"count":15,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":235,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/106\/revisions\/235"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/135"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}