Forum Discussion
Record count default to 0 if Blank
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.
4 Replies
- SteveCampbell
Memorable Member
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!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) + 0The 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
v-lili6-msftCommunity Support
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
