Forum Discussion

leejoel's avatar
leejoel
Icon for Helper I rankHelper I
6 years ago
Solved

Help with table not displaying '0' value

I have a table displaying customer claims (OPEN and CLOSED). Currently for the year, there are no OPEN claims and 27 CLOSED claims. The table is only displaying the 27 CLOSED claims because there are technically no values to show for OPEN. I'd like the table to show a count of both OPEN (0) and CLOSED (27).

 

The calculation I am using right now to get the numbers is simple, I'm guessing there is a more robust way that will show '0' when it applies, but I can't seem to figure out how:

ACTUAL = COUNT('CLAIMS'[INCIDENT_ID]
 
 
 
 

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    leejoel you need to create separate table for status and then it will work, see attached,  I created a sample pbix file, change it as per your need

     

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

     

10 Replies

    • leejoel's avatar
      leejoel
      Icon for Helper I rankHelper I
      6 years ago

      Thanks. I did try this but the table still does not display any value for OPEN.

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        leejoel 

        it depends on data model, maybe your counct calculation is wrong.

        maybe you should try smth like

        ACTUAL = 
var _cnt = COUNT('CLAIMS'[INCIDENT_ID]) 

RETURN
if(ISBLANK(_cnt), 0, _cnt)
  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi, 

    Your formula is counting all the rows in the table through 'INCIDENT_ID', so it is not taking into consideration the rows that contains OPEN and the rows that contain CLOSE. And since in the table there is no OPEN rows so you don't see the count. Therefore you need to add conditions to your formula and maybe use CALCULATE or IF functions.

     

    Can you show me the structure of your table so i can be more helpfull!  

    • leejoel's avatar
      leejoel
      Icon for Helper I rankHelper I
      6 years ago

      Thanks, you are exactly right and I'm glad you understand the issue. Give me 20 mins and I will remake the data without any proprietary info and submit it here for you guys to look at.

  • leejoel's avatar
    leejoel
    Icon for Helper I rankHelper I
    6 years ago

    az38and aj1973 Attached is a sample report around what I am trying to do. Even though there are no Open claims I would still like to display the 'Open' field and '0' value. This is pulling directly from a database so it does work when values are present, but when no value is present then it is completely omit from the report and I don't like that.

     

    I tried to attach the source file but I don't know how to do that...

    DATE   CUSTOMER_CLAIMS   TARGET #   ACTUAL #   WEEK   INCIDENT_ID

    5/15/2020 12:49:49 PM

    Closed

    0

    183

    20

    21528

    5/12/2020 12:49:49 PM

    Closed

    0

    183

    20

    21494

    5/12/2020 12:49:49 PM

    Closed

    0

    183

    20

    21493

    5/1/2020 12:49:49 PM

    Closed

    0

    183

    18

    21491

    4/22/2020 12:49:49 PM

    Closed

    0

    183

    17

    21431

    4/1/2020 12:49:49 PM

    Closed

    0

    183

    14

    21287

    4/1/2020 12:49:49 PM

    Closed

    0

    183

    14

    21291

    3/25/2020 12:49:49 PM

    Closed

    0

    183

    13

    21225

    3/5/2020 12:49:49 PM

    Closed

    0

    183

    10

    21031

    3/5/2020 12:49:49 PM

    Closed

    0

    183

    10

    21021

    3/5/2020 12:49:49 PM

    Closed

    0

    183

    10

    21028

    3/5/2020 12:49:49 PM

    Closed

    0

    183

    10

    21018

     

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      leejoel you need to create separate table for status and then it will work, see attached,  I created a sample pbix file, change it as per your need

       

       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

       

      • leejoel's avatar
        leejoel
        Icon for Helper I rankHelper I
        6 years ago

        parry2kThat works! You have no idea how much time I've spent messing around with this. You are a life saver.

    • aj1973's avatar
      aj1973
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi leejoel 

       

      Try using COUNTAX instead with the expression when it is CLOSE and OPEN.

      https://dax.guide/countax/

       

      As i became not sure what exactly you want to achieve, it would be better if you provide me with a dummy PBIX file! you need to provide it through Dropbox or Google Drive...there is no way to attach it in here

       

      Thanks