Help with table not displaying '0' value

[leejoel]

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]

[leejoel]

@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 · Accepted solution]

@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]

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

[parry2k]

@leejoel oh my pleasure! Glad I could help. All the best!

[aj1973]

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 

[aj1973]

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]

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.

[az38]

Hi @leejoel 

ACTUAL = COUNT('CLAIMS'[INCIDENT_ID]) + 0

[leejoel]

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

[az38]

@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)