Have you noticed the Number of Records field in Tableau has disappeared? If not you’re using a version of Tableau pre-2020.2.
The release of Tableau 2020.2 was the end of the [Number of Records] field. Although not a field I used regularly, the Number of Records field was useful for doing what it suggests…counting the number of records.
Where has the Tableau Number of Records field gone?
Tableau has introduced a replacement for the Number of Records field in 2020.2, with full details in this article. For each table in the data source Tableau has added a new field [TableName (Count)]. The use of italics is deliberate as the new field appears in italics in Tableau Desktop.
To save you having to read the Tableau article to find the Number of Records text (it’s a long article), Tableau state the following:
COUNT of table = SUM of Number of Records per table. You can’t build calculations on top of a table’s Count field. Count is aggregate-only.
Using the new Count of records field
The new field offers similar flexibility as the decommissioned [Number of Records]. However, the only aggregation allowed is COUNT. Other aggregations will cause an error.
Although Tableau state “You can’t build calculations on top of a table’s Count field”, it’s not clear to me what is meant by that.
Upon testing the new field can be further aggregated, for example, to use in a table calculation:
[Total Records in View]: WINDOW_SUM(COUNT([Orders]))
It also works in LOD calculations, for example, to fix the number of records in the data set:
[Total Records in Data Set]: {COUNT([Orders])}
It also sums in the Grand Total of a table, as demonstrated in the below table.
Setting up an example using the Sample – EU Superstore.xls data source, the results are below. Only 1 year of 4 in the data source appears in the view, to prove the FIXED calculation works. The entire data source has 10,000 records. Also the year is split into quarters to prove the WINDOW_SUM is working.
How to count the Number of Records in Tableau
If you still want the [Number of Records] field, it is very simple to recreate. The formula is 1. Yes, that’s it: 1.
[Number of Records]: 1
Sum the recreated [Number of Records] field to count the number of records in the Tableau data source.
Number of Records using Tableau Relationships
If the data source is using Tableau Relationships, using 1 in a calculated field doesn’t work. Things become a bit more complex using relationships, however, there are workarounds.
One way is to just count the most granular item – for example COUNT([ID]) instead of SUM(1).
Another option is to create a calculated field to mirror the level of granularity. Using a boolean calculation, it’s possible to create a calculation returning 1 or 0, which can sum to return the number of records. For example, to count the number of records within a Sales table, a calculation such as this should work, assuming the SalesID is the most granular level.
INT([SalesID]=[SalesID])
Wrapping this boolean calculation in INT converts it to a 1 or 0.
The disappearance of [Number of Records] may cause some questions, so now you have the answers. Replacing the Number of Records is very simple, unless using Tableau Relationships, in which case, think about it a bit!
awesome thank you. It’s rarely that easy!
This worked! Thank you for mentioning this. I’ve share it.
Glad you found it useful and thanks for sharing!
Where do you enter the formula? I’m new to Tableau and have enrolled myself in a Tableau visualization specialization on coursera. How do I recreate it?
Create a new calculated field. Call it whatever you want. Only enter the number 1 in the formula window.
We’re together
Wow, that saved me from having to do a hasty server update. Why the heck does just putting a 1 in the calculated field give you the record count for the dataset?
Thanks for the tip, now I can do a proper server update without rushing it.
It’ll just add a field with a 1 on each row, regardless of the value. Then when you sum those, you get a row count because each row is adding its 1.
You are awesome thank you so much
I am not able to use number of records =1. When I am pulling it on label, it is giving me the result =1
The formula isn’t =1. It should be only the number 1. No equals sign.
Hi Andrew thanks for the blog post. I am potentially having the same issue as Neha – I have the formula as 1 but it won’t aggregate so when I pull it in to my view it is always 1 and shows a different result to the table(Count). Any help would be much appreciated. Thanks
Hi Will, just to confirm, you’re replacement num of records field is an integer data type? There are no “” or other characters? If it is an integer, are you sure it is SUMming?
It is not working for me,what to do
Creating a calculated field with the value 1 is the solution to recreating the number of records field in Tableau. Can you be more specific about what’s not working? i.e. error message, etc
Hi Andrew, I have the same issue. I created a calculated field called Num of Records and it is 1 (1 is the only thing that I have in the formula). I am working with the superstore dataset. Just testing the formula, drag region to columns and sum(num of records) to rows. It says that the number of records is 1 for the 3 regions, Americas, APAC and EMEA. Not sure what I am doing wrong. Thanks so much!
That doesn’t make sense. You’re definitely SUMming the field? You have no “” anywhere in the formula? Tableau thinks the field is definitely a numeric field? I can’t replicate this issue, it works for me, so it’s difficult to make any suggestions.
It’s the latest version of Tableau, does not allow the solution shared in this post. In past version I always had my Customers calculated field as 1; but in this new version, it won’t work. You have to specifically do a COUNT([Field Name])
Very annoying, don’t know why they did that.
I wonder if this is a data source thing. Using 1 in a calculated field works in version 2020.4.8. I’m using a data extract to test.
Thanks for sharing this, Andrew – I found it extremely helpful.
I’m new to Tableau, and am not finding it easy to learn! Many things I try to do don’t work, and many of the screenshots and videos out there show old versions, or simply don’t show what I’m seeing, and/or use obscure workarounds for Tableau’s quirks, all of which is very confusing for newbies like me.
For instance, I don’t have a table_name(Count) field in the measures list. Mind you, my data isn’t shown as being split into named tables, either. Do you think that’s because I’m using Tableau Server as a data source, and the data is a single set of records (which came from SQL Server)? (I’m using version 2020.2.6)
Also, please can you explain the difference between the “Using the new Count of records field” and the “HOW TO COUNT THE NUMBER OF RECORDS IN TABLEAU” sections of your post. In my case, it looks like I need to use the latter, as I don’t see the xxxx(Count) field.
BTW, this link takes you to the relevant part of the What’s Changed page.
In 2020.2 you should have the Name(Count) field in the list of measures. It doesn’t matter if you’re connecting to a server extract, nor should it matter you’re connecting to a single set of records, the field should still be there. But you can always create the calculated field with 1, this will work if connecting to a single set of records.
Thank you Andrew. I just started to use Tableau. Its amazing explanation.
Merci beaucoup Mr.Watson
First right click on the Data section to View data, note the number of rows i.e. 250 then close the window.
Right click on creating a Calculated Field and put the number of rows/1 and rename the field.
I am still starting out but this seems to be the best way to have the tota number of records noted with no issue while making other calculations.
Hope this helps!