Replace (blank) with 0 with variables in formula

[drewmcspy]

Hi

I have a formula with variables and I'm struggling to find where to add the IF(ISBLANK(. Do I add this adter the var_max row? I'm quite new to Power BI so any help is appreciated.

Vol.LeaversLTM =

var _min = EDATE(EOMONTH(today(),-1),-12)+1

var _max = EOMONTH(today(),-1)

return COUNTROWS(CALCULATETABLE('Staff Data',FILTER('Staff Data',[Date Left] <= _max && 'Staff Data'[Date Left] >=_min),FILTER('Staff Data','Staff Data'[Vol. Leaver]= "yes")))

Thanks

mcspy

[BA_Pete · Accepted solution]

Hi @drewmcspy ,

If you want to make your measure display in a card or visual as zero instead of (BLANK), then just add '+ 0' right at the end of your measure, like this:

Vol.LeaversLTM =
VAR _min =
    EDATE ( EOMONTH ( TODAY (), -1 ), -12 ) + 1
VAR _max =
    EOMONTH ( TODAY (), -1 )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            'Staff Data',
            FILTER ( 'Staff Data', [Date Left] <= _max && 'Staff Data'[Date Left] >= _min ),
            FILTER ( 'Staff Data', 'Staff Data'[Vol. Leaver] = "yes" )
        )
    ) + 0

Pete

[drewmcspy]

Easy as that! 🙈Thanks.  

[BA_Pete]

Lol, yes, as easy as that!

A couple of things to look out for:

1) If you use a measure with the '+ 0' in a table or other detailed visual, you will find that it populates EVERY dimension combination with a value of zero - this technique is only really suitable for Card and KPI visuals, or similar 'single-value' visuals.

2) Based on the above, you may find that you start to double the amount of measures that you write and hold in your reports. You may have a detail-visual measure, like yours, then a '+ 0' version, like:

_yourMeasureName0 = [_yourOriginalMeasure] + 0

If you find this becomes a tedious issue, you can create a calculation group in Tabular Editor that is just 

SELECTEDMEASURE() + 0

Then you can add this calc group to the visual filter pane for cards etc. to get the output you want without creating new measures each time.

Pete