Measure displaying blank value

[marri]

Hi,

I have a measure which displays blank value instead of 0.

My problem scenario is like this.

Ihave different tables with relations, in which one table acts like fact table and all other tables are dimension tables.

Suppose Table ITEM (fact) has fields ItemID,statusid, amount,dateid fields And Status(Dimension) table has statusid, status code fields and Calendar(Dimension) table has dateid,date

Table ITEM Joined with STATUS based on the statusid field 

And ITEM joined with calendar based dateid field

Now I created 2 measures in Table ITEM  based on the below formula:

Open Count:=CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open"))

Complete Count:=CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="complete"))

For the period from August5th-10th there are no records in ITEM table with status open, but ITEM has 4 records with status complete.

So measure Open Count should display count value as 0 and measure Complete should display as 1 for four records.

But the output displayed as below in which Open count i wanted to represent as 0

Please help me how to represent 0 instead of blank in the column Open Count

[parry2k · Accepted solution]

here is link to another post in which I provided the solution to show "NA" if measure is blank, you can use the approach to show 0.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=IF(ISBLANK(CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open"))),0,CALCULATE(COUNT('Item'[ID]), FILTER(STATUS,status[Status Code]="open")))

Does this work?

[san_jois]

Hi,

I think the issue here is that it is not displaying Open count rows..

For that, I suggest you can edit the table view and for Status id column in the table (if it exists), you can choose 'Show items with no data'

Sorry, I am not sure how to represent blank as '0' if you want it specifically that way..

However, you can easily count the no. of blanks or zeros in table using a SUMX() function

Hope this helps

[marri]

Hi,
I wanted to display open count as measure. In the table visual open count is represented as count of itemids along with the fields of other dimensions. So, cannot use calculated column for this scenario

[parry2k · Accepted solution]

here is link to another post in which I provided the solution to show "NA" if measure is blank, you can use the approach to show 0.

[emadrigal]

have you try a calculated column on your fact item table?