Record count default to 0 if Blank

[Anonymous]

I am working on a dashboard that returns a number of metrics

Each metric is some verison of "count of records" after applying filters

One of my metrics has no records (at the moment) that meet the requirements, so the count(ID) returns "BLANK".  I need it to return a value of 0

I searched these forums, found what looked like the solution, and created the below custom field ("Record Count") and am doing a SUM by this field.  But it still returns BLANK

RecordCount = IF(ISBLANK(**datasource**[ID]),0,1)

Is there a way in DAX to have the default value be 0 instead of BLANK when I return no records?

Thanks in advance for any help or feedback, I am grateful for this forum. 

[SteveCampbell]

You can try adding

+ 0

at the end of your measure.

Be aware, if you are using records that have been filtered out, then this will still show 0.

Be sure to use a STAR schema, and use DIMENSION tables as your filters and axis, not from the FACT table.

Appreciate your Kudos
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

[Anonymous]

Appreciate the suggestion but that did not work.  I still return a value of BLANK.

My custom calculated field now looks like the below, after your suggestion: 

ID Count = IF(ISBLANK(**datasource** [ID]),0,1) + 0

The metric is SUM of ID Count

The datasource I am using is a very complex SQL query joining a number of tables. It is not a "STAR schema with DIMENSION and FACT tables" setup.  

The output of this query has been validated by the relevant SMEs, so I am comfortable with the data source.  The filters being applied are what you would define as dimensions.

After applying these filters, the calculated field still returns 0 records

[SteveCampbell]

What visual are you using? Is it by chance a matrix with columns and rows?

It's a bit hard to say more without more information. If you could provide screenshot this may help.

[v-lili6-msft]

hi, @Anonymous 

For your case, please create the dim metrics table and create the relationship with basic table.

and drag metrics filed from dim table in visual and add '+0' in the formula, then it should work well.

Best Regards,

Lin