Forum Discussion

marri's avatar
marri
Frequent Visitor
8 years ago
Solved

Measure displaying blank value

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

Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    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's avatar
    Ashish_Mathur
    8 years ago

    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?

5 Replies

  • san_jois's avatar
    san_jois
    Resolver I
    8 years ago

    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's avatar
      marri
      Frequent Visitor
      8 years ago
      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's avatar
        parry2k
        Super User
        8 years ago

        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's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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?