How to return 0 instead of BLANK in DAX

[@jensvonkracht6770]

Dear Alberto, even when this video is 2 years old, the topic is up to date. You made my day and helped me solving a problem, where I worked an entire day on. Thank you very much!

[@bravucod]

Every time I think I'm getting better at DAX and then watching your videos, it reminds me how much of a Gap there still is!!( You guys make me feel like a newbie).Thanks for such a great content

[@MrMalorian]

It surprised me after I started making dashboard just how much Blank bothered people and that they wanted to see the 0. I just started adding +0 at the end of my measures. I didn't know there were issues with that method. Thanks for the video :)

[@baskis69]

Just in a report I had this problem now. The video has been a gift to me. Grazie mille!

[@sarajvo2010]

Wow, so much learning content in demo. "business logic is in table" I like it

[@christofhermdelgado4267]

Really cool stuff, I hope you can answer my question because I'm getting crazy with a blank I don't know how to turn into zero, what instead of using product in rows I have Weeks instead? How can I show the zero in that case? how the ALLEXCEPT argument will change?

[@gruffalocrumble494]

Amazing. Thank you as always Alberto.

[@tonildg9822]

Thanks a lot Alberto great video! 👍 Wonderful solution. One question, with the second version of the calculated table, when you remove the filter from the products to avoid the circular dependency, doesn't that affect the row context you are in due to GENERATE?

[@samgrider3765]

I'm experiencing an issue where I need to replace blanks with 0 in a measure, but when I do, the measures stops obeying the the date slicer in the visual. How can I replace blanks with 0's while maintaining the functionality of slicers/filters? Thank you!

[@SQLBI]

It depends on the business logic - adding 0 could increase the number of visible cells affecting the performance - you should implement your logic that shows 0 only in intersections where it makes sense. It cannot be generalized, it depends on your model.

[@jeromelouvel732]

Another great and very well explained one! Thank you.

[@samuelmanseau467]

Very clear video & excellent advice !

[@rafaeldurbanoneto5751]

Hello Albert Thanks for the explanation ! However I notice that your ZeroGrain table is created using Sales table that it's work just for Sales Measures in your calculation group, right ? If I have more than one fact table in my model I would need to create more tables like ZeroGrain, right ? For this maybe the first or de second approach is better... what do you think ?

[@JoelNove]

Very helpful! Thanks Alberto

[@josephansah]

Hi SQL BI team. Can you please explain why a circular dependency arises within the second table used within GENERATE, which requires removing filters from the Product table? Can't understand why that prevents creating a relationship with the date table. Thanks for your very educational videos/work.

[@cognovo]

Hi Alberto, nice video, could you eloborate a bit more on why you removed the filter context of product when solving the circular dependency issue? I do not understand why removing the product filter context helped in solving this issue.

[@SQLBI]

Look at these articles/video: www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ www.sqlbi.com/articles/understanding-circular-dependencies/

[@guillermodebiase5803]

Hi Alberto, I need a different aproach of this scenario, I need to return zero for a near zreo value, in my scenario I create a waterfall chart that shows the Price, Volume and Mix variations by Year-Month, and si some cases the products has sales in value (let say 5 USD) and a volumen near to zero, like 0.00000001, so in the cases the division between sales and volumen will create a "exception number", the result is 500,000,000 USD, but this result is an exception, so my customer require to create a rule to mitigate this scenario and if the volumen is really near to zero then the division result shows zero as result

[@guillermodebiase5803]

One more comment here, the sales table is a table by day, customer and product, so the numbers that I put as example are the total sum af a particular month

[@giri41]

❤ video… sumx ignored when blanks in CY .. please help

[@odinismli]

@3:37, what magic button made the formula bar hidden?

[@sandeepbarge4699]

Why does Show Items with No data shows all the permutations and combinations of two dimension tables used along with measure that refers to fact table? It should return only those rows for which there is row in fact table even if measure returns blank. And not cartesian product of two dimension tables. Can you please help me understand how to resolve this issue? Please reply. Thanks in advannce!

[@sandeepbarge4699]

Hi Team - An I missing something? It would be great help if you can answer my query here.

[@ashishmohan4707]

Hi Alberto, Hope you are doing well and safe with your family very useful video and very practical need everyone for every project ......very big thanks to share with us. You are really a real DAX hero U r magician on DAX You can do everything on DAX If it can possible can you plzz provide us excel data file which you used. Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂

[@SQLBI]

You can find the link to the article in the description. The sample file can be downloaded from that page.

[@gianpaolo19]

I tried this in PowerPivot for Excel in a Pivot table and it doesn't work neither the "If IsBlank()" solution nor the [measure]+0....I don't understand why....It should be the same logic...As workaround I had to add one extra row in the Database with value = 0 to be able to represnt the right datas

[@giri41]

Sumx ( values (material), Calculate( if ( sales cy = 0 || sales PY =0 , sales variance, unit price* sales PY))) it’s not working

[@pabeader1941]

Doing the + 0 thing doesn't work. I even tried adding + 1 and that did nothing either. Any ideas?

[@SQLBI]

Check if you are hitting the autoexists behavior: www.sqlbi.com/articles/understanding-dax-auto-exist/

[@wisa7615]

Every time i think i know something suddenly i don't know anything

[@nikolagrkovic8769]

Iferror ( ...,0) 😂

[@stephanweaver1960]

The Babylonians would be proud - a half hour talking about nothing as zero. Not "Much to do about nothing"