Show blank as zero - Unplugged #20

Alberto shares his exploration of different techniques for an article about displaying blank as zero in a DAX measure. The article and its video will be much shorter without the trial and error process that you can see without editing in this longer video!
Read more about the "unplugged" format: www.sqlbi.com/blog/marco/2021/01/09/the-unplugged-video-series-on-sqlbi-youtube-channel/?aff=yt
#unplugged

[@adelina3589]

Thanks to your tutorial, I learned a new technique that I can now apply in my work. I really appreciated how clear and concise the instructions were. It made it easy for me to follow along.

[@cannanam]

These unplugged videos are great. We can spend 30 minutes watching how a real master thinks through various analytics solution options. At the end we learn a new technique, but more important we also learn how to think and approach a problem. Thanks for sharing your thinking!

[@hailin1354]

Excellent， following your mind is a great experience!

[@EricaDyson]

Excellent. I learn so much from you and you encourage me to expand my thinking!

[@rodrigochavez7]

Thanks for this video, really helped me a lot!!

[@judycalvo5127]

As always, thank you so much for the work you do! Awesome presentation!

[@bitips]

Marco, congrats, once more a very helpful material. I am a fan.

[@jaydee34de]

Thank you ! Awesome

[@hansmostafavi9280]

Loved it, unplugged!

[@mnowako]

Indeed, I like it a lot 👍 Except all good thinking I could see the advantage of using IF.EAGER instead of IF. Perfectly done 👏 Thank you!

[@VR-jf2py]

When user open a report and if the dataset refresh for that report is in progress then display message that report data refresh is still in progress, thanks

[@buncarter9774]

Nice article sir!.👍👍

[@webdozor]

Great thinking . Sorry for a dump question , but what if we could create a secondary table containing only first and last sale date for every product , and not all the dates , and than evaluate on top of that ?

[@SQLBI]

It could work - the definition wouldn't be dynamic - but it's probably not a requirement. Yes, it's a possible optimization.

[@webdozor]

​@@SQLBI Even if we will make something like this ? Product has to be filtered from CheckTable though, or both direction crossfilter has to be enabled. Not sure about the performance of course. VAR FIRST_DATE = MAX(CheckTable[@FirstDate]) VAR LAST_DATE = MAX(CheckTable[@LastDate]) VAR ZERO_CHECK = DIVIDE(0, COUNTROWS( FILTER( 'Date',' Date'[Date]>=FIRST_DATE && 'Date'[Date]=FIRST_DATE && 'Date'[Date]=FIRST_DATE, CURRENT_DATE

[@Milhouse77BS]

Hmm, REMOVEFILTERS still new to me. I wonder why it is necessary in this case. Would it have been written differently in an older version of DAX?

[@SQLBI]

You can use ALL instead of REMOVEFILTERS, they are synonyms.

[@alisahib8593]

Very informative video. Can you please make a video on " how to handle deletion in incremental refresh on power bi". I know in KZbin have some videos on it but I like your way of teaching please create a video on it ASAP. I will be very thankful to you. God bless you Regards.

[@yes-pg5dy]

In matrix visual can we change the column headers dynamically based on Slicer ? Suppose if I select May-2021 in slicer I need to see both May-2021 sales as well as May-2020 sales simultaneously but in another column.. is it possible sir ?

[@golodiassaid4879]

You are amazing teacher! the contents are also very helpful. I was wondering if it's possible to have a measure that ignores page level filters. I have Year field in filter panel, but I also want show bar chart for annual sales for each year. I used removefilters to ignore year but it shows either selected year total or sum of sales for all years. Thanks in advance!

[@dariuszspiewak5624]

It's not possible to override page level filters. This is what they have been designed for: to filter the page in such a way that it's not possible to reach outside the data they make visible.

[@jiawang1257]

well, there are also have another case. in our company we have a different category, we use the different date to measure Project Progress. However, the value is the date and not continuous. for example, "A" has those date values, 01.01.2020, 01.02.2020, 01.03.2020, 15.03.2020, 01.05.2020. The DAX is: count A = calculate(count(A), userelationship('Table'A, 'date'date)) This will only show the dates that A has. I wanna show the continuous trend from 01.01.2020 to 01.05.2020, even in my database I don't have dates for Feb 2020 and April 2020. for this case, I've used a way to fill in the missing dates ( add another column and named "Missing date for A") and force them continuously. My final DAX is CountA =calculate(count(A), userelationship('Table'A, 'date'date)) + calculate(count(A), userelationship('Table'Missing date for A, 'date'date))*0. well, that's works. But in my database I have 50 category and their values are date. Is there a best way to solve this problem? 'cause I don't wanna add another 50 columns just for missing dates.

[@The_Code_Father]

Great video Mr Alberto Excuse me sir but I'm having a problem which i fixed by creating a bridge table but I was wondering if there a better solution if i use dax I have different dimension tables including the date table but not all their values are mentioned in the fact table so I need to display records from all dimension tables only if they are mentioned or have values in the fact table

[@lanzinfo]

BEFORE watching a 49 minute video! I think why not use COALESCE function? Then I think it’s SQLBI I am sure I will learn some important DAX concepts. I am going to enjoy the video with my coffee. Gracias!

[@lanzinfo]

7 minutes into the video I already was given something to consider. Learned Something!

[@lanzinfo]

Gracie! As usual time well spent it actually helped with a similar problem I have to solve.

[@masgitz]

Marco, in my organization, we can't install external tools, is there another way to create calculation groups within powerbi?

[@SQLBI]

No. Please read this article: www.sqlbi.com/articles/development-tools-for-tabular-models-in-2021/

[@DIGITAL_COOKING]

Almost 30000 subscribers ! Enjoy DAX 🙂👍

[@alyssonmachadoborges3373]

My solution to this question: 1) Table Calendar_Year with calculate column Total_Sales = CALCULATE([SalesAmount])+0) 2) Table Sales In Matrix: 1) Use [Year] of Calendar_Year in columns. 2) Use [Region] of Sales in lines. 3) Create this measure for values: SalesAmoutZero = VAR VZero = CALCULATE(SUM(Calendar_Year[Total_Sales]) - SUM(Calendar_Year[Total_Sales]),Calendar_Year[Total_Sales]>0) RETURN CALCULATE([.SalesAmount]+VZero)

[@Micha3l28]

Ciao Alberto! Thanks for another well explained unplugged video! I was wondering if this approach also could be applied on dates (today() function). I’m calculating the open days a ticket is open by comparing the create date with the close date. However when a ticket is stil open there is a blank value that needs to be replaced with the date of today, so I can subtract the dates and generate an allocation tabel. Curbal did a wonderfull video on it as well (kzbin.info/www/bejne/hIe9h6FuZ7yrgqM), but this does not include replacing a blank value. I was able to fix it in Power query with a custom column and in Power BI with a calculated column, but would it be possible in the measure as well? I want to avoid creating columns on the fact table, because I think that isn’t beneficial from a performance point of view in this case. Regards, Michael