Include Zero on a Table

[bhartsell]

I'm making a table visual with two columns, Name and Count. This works perfectly well at counting data, but I also want to include "Names" for which there is no data (thus the Count would be 0). How can I do this?

[sraj]

What if we have this as a column and not a calculated measure as mentioned above?

[CahabaData]

I will assume that the data table in which you are working - if a name has no data/record then it does not appear in this table.  Let's call this Table1

So you need a data table that has all names - possible you need to create that, which is a straight forward task using the New Table feature.  In any case call that Name Table.

Have a join line between these 2 data tables

Then in your Visual table use Name table, and a Measure =Count(Related(Table1)) 

Just air code but that's the idea I think will work.

[OwenAuger]

@bhartsell,

The simplest way I know of is to add +0 to the end of your measure.

This will convert blanks to zero and leave nonblanks unchanged.

[bhartsell]

I'm not using a measure, though. It's a table where "Name" is a text value and "Count" is a count of the data. 

[OwenAuger · Accepted solution]

Oh right, so you're using the 'Count' aggregation from the dropdown on the field.

I think you'll need to define your own measure instead to stop the blank row removal.

e.g.

Count Measure = COUNT( Table[Column] ) + 0

[jalapeno]

I came across your solution to someone with a similar problem, but when I tried it, the results included all rows, even those that had been filtered out previously by a parameter...

I have a table with users & applications that they have purchased; a second table with usage of applications by all users.  I have a drop-down filter to select which application to view data on - which should show the users who have the application and the count of hits in the usage table.

When I use the built in count drop-down in the table field settings, only rows of users with usage appears.  I created a measure as you recommended to include users with no usage, but the table then stopped responding to the application name filter - regardless of which value I select, all applications are now displayed in the table.

Can you help me get the count, including rows with no usage, for only the applications that have been selected in the filter?

[OwenAuger]

@jalapeno 

My opinion on how to solve this has changed since the original post - I would now not recommend the "add zero" approach as it can adversely impact performance and may give unwanted results as in your case.

I would instead recommend:

1. Ensure all columns to be used for filter/grouping are in separate dimension tables related to your fact table(s).
   In your case this would include User & Application.
2. Ensure the User & Application columns used on your visuals are those from the dimension tables.
3. Once these fields have been added to particular visuals, click the dropdown on the field in the field well and select "Show items with no data"

Regards,

Owen

[bhartsell]

That worked perfectly. Thank you!