Avoiding Blanks in your Power BI Reports

One of the things that really frustrates me as a report designer and builder is when I have a blank coming through onto my report. Whilst the blank isn't incorrect, seeing the '(Blank)' in the middle of the visual isn't very pleasing to the eye, and more often than not requires explaining as to why something is in fact blank.

There are several ways that you could overcome this as an issue, using an 'if' statement in the DAX to change the value to something more pleasing if there is a blank coming through. But in reality, if you're new creating Power BI reports and new to DAX as a formula language the thought of building an 'if' statement that works efficiently can be quite daunting.

This is why I have boiled this down to one simple fix... adding a zero.

If we consider a formula which is working out the sales for last year:

SALES REVENUE YA = 
CALCULATE(
    'Sales Data'[SALES REVENUE TY],
    SAMEPERIODLASTYEAR(Dates[DATE])
)

All you need to do is add +0 to the end of the formula and the blank will go away. This doesn't fundamentally change the calculation as adding zero to any value won't change the value and is very straight forward to implement.

SALES REVENUE YA = 
CALCULATE(
    'Sales Data'[SALES REVENUE TY],
    SAMEPERIODLASTYEAR(Dates[DATE])
)+0

This simple fix then turns our '(Blank)' into a much more pleasing and much more meaningful '0'.

Ah... Much better.