cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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]
 
table.png
 
 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Help with table not displaying '0' value

@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

 

 image.png

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!

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

10 REPLIES 10
Highlighted
Super User II
Super User II

Re: Help with table not displaying '0' value

Hi @leejoel 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Helper I
Helper I

Re: Help with table not displaying '0' value

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

Highlighted
Post Prodigy
Post Prodigy

Re: Help with table not displaying '0' value

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!  

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Highlighted
Super User II
Super User II

Re: Help with table not displaying '0' value

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Helper I
Helper I

Re: Help with table not displaying '0' value

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.

Highlighted
Helper I
Helper I

Re: Help with table not displaying '0' value

@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

 

pic.png

Highlighted
Post Prodigy
Post Prodigy

Re: Help with table not displaying '0' value

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 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Highlighted
Super User IV
Super User IV

Re: Help with table not displaying '0' value

@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

 

 image.png

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!

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper I
Helper I

Re: Help with table not displaying '0' value

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors