{"id":3817,"date":"2025-06-23T08:15:04","date_gmt":"2025-06-23T08:15:04","guid":{"rendered":"https:\/\/techtrendfeed.com\/?p=3817"},"modified":"2025-06-23T08:15:04","modified_gmt":"2025-06-23T08:15:04","slug":"why-you-ought-to-not-exchange-blanks-with-0-in-energy-bi","status":"publish","type":"post","link":"https:\/\/techtrendfeed.com\/?p=3817","title":{"rendered":"Why You Ought to Not Exchange Blanks with 0 in Energy BI"},"content":{"rendered":"

\n<\/p>\n

\n

watching\u00a0Jeffrey Wang as a stay stream visitor with Reid Havens<\/a>, and one of many dozen fantastic issues that Jeffrey shared with the viewers was the checklist of optimizations that the DAX engine performs when creating an optimum question plan for our measures.<\/p>\n

And, the one which caught my consideration was concerning the so-called \u201cSparse measures\u201d:<\/p>\n

\"\" — Screenshot from the stay stream on YouTube<\/figcaption><\/figure>\n
To make it easy, when you outline the measure,\u00a0 Components Engine in VertiPaq<\/a>\u00a0will add an implicit NonEmpty filter to the question, which ought to allow the optimizer to keep away from full cross-join of dimension tables and scan solely these rows the place data for the mixture of your dimension attributes actually exist. For people coming from the MDX world, the NonEmpty operate might look acquainted, however let\u2019s see the way it works in DAX.<\/p>\n
The factor that almost all resonated with me was when Jeffrey suggested towards changing BLANKs with zeroes (or no matter express values) in Energy BI calculations. I\u2019ve already written\u00a0 how one can deal with BLANKs and change them with zeroes<\/a>, however on this article, I wish to give attention to the potential efficiency implications of this resolution.<\/p>\n
Setting the stage<\/h2>\n
Earlier than we begin, one necessary disclaimer: the advice to not change BLANK with 0 is simply that \u2014 a suggestion. If the enterprise request is to show 0 as a substitute of BLANK, it doesn\u2019t essentially imply that you must refuse to do it. In most situations, you’ll most likely not even discover a efficiency lower, however it’s going to depend upon a number of various factors\u2026<\/p>\n
Let\u2019s begin by writing our easy DAX measure:<\/p>\n
Gross sales Amt 364 Merchandise =\nCALCULATE (\n [Sales Amt],\n FILTER ( ALL ( 'Product'[ProductKey] ), 'Product'[ProductKey] = 364 )\n)<\/code><\/pre>\nUtilizing this measure, I wish to calculate the full gross sales quantity for the product with ProductKey = 364. And, if I put the worth of this measure within the Card visible, and activate Efficiency Analyzer to test the occasions for dealing with this question, I get the next outcomes:<\/p>\n Picture by creator<\/figcaption><\/figure>\nDAX question took solely 11ms to execute, and as soon as I switched to DAX Studio, the xmSQL generated by the Components Engine was fairly easy:<\/p>\n Picture by creator<\/figcaption><\/figure>\nAnd, if I check out the Question plan (bodily), I can see that the Storage Engine discovered just one present mixture of values to return our information:<\/p>\n Picture by creator<\/figcaption><\/figure>\nIncluding extra elements\u2026<\/h2>\nNevertheless, let\u2019s say that the enterprise request is to investigate information for Product Key 364 on a day by day stage. Let\u2019s go and add dates to our report:<\/p>\n Picture by creator<\/figcaption><\/figure>\nThis was once more very quick! I’ll now test the metrics inside the DAX Studio:<\/p>\n Picture by creator<\/figcaption><\/figure>\nThis time, the question was expanded to incorporate a Dates desk, which affected the work Storage Engine wanted to do, as as a substitute of discovering only one row, this time, the quantity is totally different:<\/p>\n Picture by creator<\/figcaption><\/figure>\nAfter all, you’ll not discover any distinction in efficiency between these two situations, because the distinction is only some milliseconds.<\/p>\n However that is only the start; we’re simply warming up our DAX engine. In each of those instances, as you might even see, we see solely \u201cstuffed\u201d values \u2014 that mixture of rows the place each of our necessities are glad \u2014 product secret is 364 and solely these dates the place we had gross sales for this product \u2014 for those who look completely within the illustration above, dates should not contiguous and a few are lacking, comparable to January twelfth, January 14th to January twenty first and so forth.<\/p>\n It’s because Components Engine was good sufficient to eradicate the dates the place product 364 had no gross sales utilizing the NonEmpty filter, and that\u2019s why the variety of data is 58: now we have 58 distinct dates the place gross sales of product 364 weren’t clean:<\/p>\n Picture by creator<\/figcaption><\/figure>\nNow, let\u2019s say that enterprise customers additionally wish to see these dates in-between, the place product 364 hadn\u2019t made any gross sales. So, the concept is to show 0$ quantity for all these dates. As already described within the earlier article, there are a number of other ways to exchange the BLANKs with zeroes, and I’ll use the\u00a0COALESCE<\/code>()<\/em>\u00a0operate:<\/p>\n Gross sales Amt 364 Merchandise with 0 = COALESCE([Sales Amt 364 Products],0)<\/code><\/pre>\nMainly, the COALESCE<\/code> operate will test all of the arguments supplied (in my case, there is just one argument) and change the primary BLANK worth with the worth you specified. Merely mentioned, it’s going to test if the worth of the Gross sales Amt 364 Merchandise is BLANK. If not, it’s going to show the calculated worth; in any other case, it’s going to change BLANK with 0.<\/p>\n Picture by creator<\/figcaption><\/figure>\nWait, what?! Why am I seeing all of the merchandise, after I filtered every thing out, besides product 364? Not to mention that, my desk now took greater than 2 seconds to render! Let\u2019s test what occurred within the background.<\/p>\n Picture by creator<\/figcaption><\/figure>\nAs a substitute of producing one single question, now now we have 3 of them. The primary one is precisely the identical as within the earlier case (58 rows). Nevertheless, the remaining queries goal the Product and Dates tables, pulling all of the rows from each tables (The product desk incorporates 2517 rows, whereas the Dates desk has 1826). Not simply that, check out the question plan:<\/p>\n Picture by creator<\/figcaption><\/figure>\n4.6 million data?! Why on Earth does it occur?! Let me do the mathematics for you:\u00a02.517 * 1.826 = 4.596.042<\/strong>\u2026So, right here we had a full cross-join between Product and Dates tables, forcing each single tuple (mixture of date-product) to be checked! That occurred as a result of we pressured the engine to return 0 for each single tuple that may in any other case return clean (and consequentially be excluded from scanning)!<\/p>\n It is a simplistic overview of what occurred:<\/p>\n Picture by creator<\/figcaption><\/figure>\nImagine it or not, there may be a chic answer to point out clean values out-of-the-box (however, not with 0 as a substitute of BLANK). You possibly can simply merely click on on the Date area and select to\u00a0Present gadgets with no information<\/em>:<\/p>\n Picture by creator<\/figcaption><\/figure>\nThis may show the clean cells too, however with out performing a full cross-join between the Product and Dates tables:<\/p>\n Picture by creator<\/figcaption><\/figure>\nWe are able to now see all of the cells (even blanks) and this question took half the time of the earlier one! Let\u2019s test the question plan generated by the Components Engine:<\/p>\n Picture by creator<\/figcaption><\/figure>\nNot all situations are catastrophic!<\/h2>\nReality to be mentioned, we may\u2019ve rewritten our measure to exclude some undesirable data, however it could nonetheless not be an optimum method for the engine to eradicate empty data.<\/p>\n Moreover, there are specific situations wherein changing BLANKs with zero won’t trigger a major efficiency lower.<\/p>\n Let\u2019s look at the next state of affairs: we’re displaying information concerning the whole gross sales quantity for each single model. And I’ll add my gross sales quantity measure for product 364:<\/p>\n Picture by creator<\/figcaption><\/figure>\nAs you may anticipate, that was fairly quick. However, what is going to occur after I add my measure that replaces BLANKs with 0, which brought on havoc within the earlier state of affairs:<\/p>\n Picture by creator<\/figcaption><\/figure>\nHm, seems to be like we didn\u2019t should pay any penalty when it comes to efficiency. Let\u2019s test the question plan for this DAX question:<\/p>\n Picture by creator<\/figcaption><\/figure>\nConclusion<\/h2>\nAs Jeffrey Wang prompt, you must keep away from changing blanks with zeroes (or with another express values), as it will considerably have an effect on the question optimizer\u2019s capability to eradicate pointless information scanning. Nevertheless, if for any purpose it’s essential to substitute a clean with some significant worth, watch out when and learn how to do it.<\/p>\n As regular, it relies on many alternative points \u2014 for columns with low cardinality, or if you\u2019re not displaying information from a number of totally different tables (like in our instance, once we wanted to mix information from Product and Dates tables), or visible sorts that don\u2019t have to show numerous distinct values (i.e. card visible) \u2014 you may get away with out paying the efficiency value. However, for those who use tables\/matrices\/bar charts that present a whole lot of distinct values, make certain to test the metrics and question plans earlier than you deploy that report back to a manufacturing surroundings.<\/p>\n Thanks for studying!<\/p>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":" watching\u00a0Jeffrey Wang as a stay stream visitor with Reid Havens, and one of many dozen fantastic issues that Jeffrey shared with the viewers was the checklist of optimizations that the DAX engine performs when creating an optimum question plan for our measures. And, the one which caught my consideration was concerning the so-called \u201cSparse measures\u201d: […]<\/p>\n","protected":false},"author":2,"featured_media":3819,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55],"tags":[3523,763,3522],"class_list":["post-3817","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-machine-learning","tag-blanks","tag-power","tag-replace"],"_links":{"self":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/3817","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3817"}],"version-history":[{"count":1,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/3817\/revisions"}],"predecessor-version":[{"id":3818,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/3817\/revisions\/3818"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/media\/3819"}],"wp:attachment":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

\"\" — Screenshot from the stay stream on YouTube<\/figcaption><\/figure>\n
To make it easy, when you outline the measure,\u00a0 Components Engine in VertiPaq<\/a>\u00a0will add an implicit NonEmpty filter to the question, which ought to allow the optimizer to keep away from full cross-join of dimension tables and scan solely these rows the place data for the mixture of your dimension attributes actually exist. For people coming from the MDX world, the NonEmpty operate might look acquainted, however let\u2019s see the way it works in DAX.<\/p>\n
The factor that almost all resonated with me was when Jeffrey suggested towards changing BLANKs with zeroes (or no matter express values) in Energy BI calculations. I\u2019ve already written\u00a0 how one can deal with BLANKs and change them with zeroes<\/a>, however on this article, I wish to give attention to the potential efficiency implications of this resolution.<\/p>\n
Setting the stage<\/h2>\n
Earlier than we begin, one necessary disclaimer: the advice to not change BLANK with 0 is simply that \u2014 a suggestion. If the enterprise request is to show 0 as a substitute of BLANK, it doesn\u2019t essentially imply that you must refuse to do it. In most situations, you’ll most likely not even discover a efficiency lower, however it’s going to depend upon a number of various factors\u2026<\/p>\n
Let\u2019s begin by writing our easy DAX measure:<\/p>\n
Gross sales Amt 364 Merchandise =\nCALCULATE (\n [Sales Amt],\n FILTER ( ALL ( 'Product'[ProductKey] ), 'Product'[ProductKey] = 364 )\n)<\/code><\/pre>\nUtilizing this measure, I wish to calculate the full gross sales quantity for the product with ProductKey = 364. And, if I put the worth of this measure within the Card visible, and activate Efficiency Analyzer to test the occasions for dealing with this question, I get the next outcomes:<\/p>\n Picture by creator<\/figcaption><\/figure>\nDAX question took solely 11ms to execute, and as soon as I switched to DAX Studio, the xmSQL generated by the Components Engine was fairly easy:<\/p>\n Picture by creator<\/figcaption><\/figure>\nAnd, if I check out the Question plan (bodily), I can see that the Storage Engine discovered just one present mixture of values to return our information:<\/p>\n Picture by creator<\/figcaption><\/figure>\nIncluding extra elements\u2026<\/h2>\nNevertheless, let\u2019s say that the enterprise request is to investigate information for Product Key 364 on a day by day stage. Let\u2019s go and add dates to our report:<\/p>\n Picture by creator<\/figcaption><\/figure>\nThis was once more very quick! I’ll now test the metrics inside the DAX Studio:<\/p>\n Picture by creator<\/figcaption><\/figure>\nThis time, the question was expanded to incorporate a Dates desk, which affected the work Storage Engine wanted to do, as as a substitute of discovering only one row, this time, the quantity is totally different:<\/p>\n Picture by creator<\/figcaption><\/figure>\nAfter all, you’ll not discover any distinction in efficiency between these two situations, because the distinction is only some milliseconds.<\/p>\n However that is only the start; we’re simply warming up our DAX engine. In each of those instances, as you might even see, we see solely \u201cstuffed\u201d values \u2014 that mixture of rows the place each of our necessities are glad \u2014 product secret is 364 and solely these dates the place we had gross sales for this product \u2014 for those who look completely within the illustration above, dates should not contiguous and a few are lacking, comparable to January twelfth, January 14th to January twenty first and so forth.<\/p>\n It’s because Components Engine was good sufficient to eradicate the dates the place product 364 had no gross sales utilizing the NonEmpty filter, and that\u2019s why the variety of data is 58: now we have 58 distinct dates the place gross sales of product 364 weren’t clean:<\/p>\n Picture by creator<\/figcaption><\/figure>\nNow, let\u2019s say that enterprise customers additionally wish to see these dates in-between, the place product 364 hadn\u2019t made any gross sales. So, the concept is to show 0$ quantity for all these dates. As already described within the earlier article, there are a number of other ways to exchange the BLANKs with zeroes, and I’ll use the\u00a0COALESCE<\/code>()<\/em>\u00a0operate:<\/p>\n Gross sales Amt 364 Merchandise with 0 = COALESCE([Sales Amt 364 Products],0)<\/code><\/pre>\nMainly, the COALESCE<\/code> operate will test all of the arguments supplied (in my case, there is just one argument) and change the primary BLANK worth with the worth you specified. Merely mentioned, it’s going to test if the worth of the Gross sales Amt 364 Merchandise is BLANK. If not, it’s going to show the calculated worth; in any other case, it’s going to change BLANK with 0.<\/p>\n Picture by creator<\/figcaption><\/figure>\nWait, what?! Why am I seeing all of the merchandise, after I filtered every thing out, besides product 364? Not to mention that, my desk now took greater than 2 seconds to render! Let\u2019s test what occurred within the background.<\/p>\n Picture by creator<\/figcaption><\/figure>\nAs a substitute of producing one single question, now now we have 3 of them. The primary one is precisely the identical as within the earlier case (58 rows). Nevertheless, the remaining queries goal the Product and Dates tables, pulling all of the rows from each tables (The product desk incorporates 2517 rows, whereas the Dates desk has 1826). Not simply that, check out the question plan:<\/p>\n Picture by creator<\/figcaption><\/figure>\n4.6 million data?! Why on Earth does it occur?! Let me do the mathematics for you:\u00a02.517 * 1.826 = 4.596.042<\/strong>\u2026So, right here we had a full cross-join between Product and Dates tables, forcing each single tuple (mixture of date-product) to be checked! That occurred as a result of we pressured the engine to return 0 for each single tuple that may in any other case return clean (and consequentially be excluded from scanning)!<\/p>\n It is a simplistic overview of what occurred:<\/p>\n Picture by creator<\/figcaption><\/figure>\nImagine it or not, there may be a chic answer to point out clean values out-of-the-box (however, not with 0 as a substitute of BLANK). You possibly can simply merely click on on the Date area and select to\u00a0Present gadgets with no information<\/em>:<\/p>\n Picture by creator<\/figcaption><\/figure>\nThis may show the clean cells too, however with out performing a full cross-join between the Product and Dates tables:<\/p>\n Picture by creator<\/figcaption><\/figure>\nWe are able to now see all of the cells (even blanks) and this question took half the time of the earlier one! Let\u2019s test the question plan generated by the Components Engine:<\/p>\n Picture by creator<\/figcaption><\/figure>\nNot all situations are catastrophic!<\/h2>\nReality to be mentioned, we may\u2019ve rewritten our measure to exclude some undesirable data, however it could nonetheless not be an optimum method for the engine to eradicate empty data.<\/p>\n Moreover, there are specific situations wherein changing BLANKs with zero won’t trigger a major efficiency lower.<\/p>\n Let\u2019s look at the next state of affairs: we’re displaying information concerning the whole gross sales quantity for each single model. And I’ll add my gross sales quantity measure for product 364:<\/p>\n Picture by creator<\/figcaption><\/figure>\nAs you may anticipate, that was fairly quick. However, what is going to occur after I add my measure that replaces BLANKs with 0, which brought on havoc within the earlier state of affairs:<\/p>\n Picture by creator<\/figcaption><\/figure>\nHm, seems to be like we didn\u2019t should pay any penalty when it comes to efficiency. Let\u2019s test the question plan for this DAX question:<\/p>\n Picture by creator<\/figcaption><\/figure>\nConclusion<\/h2>\nAs Jeffrey Wang prompt, you must keep away from changing blanks with zeroes (or with another express values), as it will considerably have an effect on the question optimizer\u2019s capability to eradicate pointless information scanning. Nevertheless, if for any purpose it’s essential to substitute a clean with some significant worth, watch out when and learn how to do it.<\/p>\n As regular, it relies on many alternative points \u2014 for columns with low cardinality, or if you\u2019re not displaying information from a number of totally different tables (like in our instance, once we wanted to mix information from Product and Dates tables), or visible sorts that don\u2019t have to show numerous distinct values (i.e. card visible) \u2014 you may get away with out paying the efficiency value. However, for those who use tables\/matrices\/bar charts that present a whole lot of distinct values, make certain to test the metrics and question plans earlier than you deploy that report back to a manufacturing surroundings.<\/p>\n Thanks for studying!<\/p>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":" watching\u00a0Jeffrey Wang as a stay stream visitor with Reid Havens, and one of many dozen fantastic issues that Jeffrey shared with the viewers was the checklist of optimizations that the DAX engine performs when creating an optimum question plan for our measures. And, the one which caught my consideration was concerning the so-called \u201cSparse measures\u201d: […]<\/p>\n","protected":false},"author":2,"featured_media":3819,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55],"tags":[3523,763,3522],"class_list":["post-3817","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-machine-learning","tag-blanks","tag-power","tag-replace"],"_links":{"self":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/3817","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3817"}],"version-history":[{"count":1,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/3817\/revisions"}],"predecessor-version":[{"id":3818,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/3817\/revisions\/3818"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/media\/3819"}],"wp:attachment":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}