Forum Discussion
count values in a table and include zero when ID doesn't exist
Seems like this ought to be simple and I've found solutions to similar sounding problems but can't make the right connection.
I have two tables represented by the simple examples below. The first table contains a list of unique IDs; the second contains values for some IDs and will be filtered by department.
I need a measure that counts the number of text values that exist for each ID in the second table. That part is easy using COUNTA(). The part I'm struggling with is how to produce a result that includes zero counts for IDs that don't exist in the second table. What I want is this
but what I'm getting is this
Thanks
Try adding a +0 to your exisitng COUNTA measure.
It will turn a blank into a 0. Alternatively if you click the down arrow for ID in the visual you can click Show Items with no data (won't get zero's jut blanks):
5 Replies
Try adding a +0 to your exisitng COUNTA measure.
It will turn a blank into a 0. Alternatively if you click the down arrow for ID in the visual you can click Show Items with no data (won't get zero's jut blanks):
I've tried +0 and there's no change but that could be the result of other formula elements in the real measure. The visual I'm using doesn't include a show items with no data.
Let me go back to the real data and see whether I can figure out something else.
Hello Scott_Visio
I agree with bcdobbs. It also might be you're pulling the department table ID instead of the ID from the ID table into the visual? I'd made that mistake when duplicating your data 🙂
Once I pulled the right ID column I got the correct resultsHope that helps!
James
Thanks to both bcdobbs and dudeyates. Turns out that I had an unrelated date/time issue in my real data (can't share that data here) and that error obscured the benefits of adding+0 to my measure. Now that I've fixed the underlying data issue, adding +0 allows me to include zero values in the result.
