cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Replace (blank) with 0 with variables in formula

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
1 ACCEPTED SOLUTION
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

3 REPLIES 3
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Frequent Visitor

Easy as that! 🙈Thanks.

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors