Number of Records is missing in Tableau

//

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.

Image of the new count field in italics
Count of records in a table called Orders

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:

You will no longer see the Number of Records field in data sources that contain logical tables. Every table includes a count of its records, as a field named TableName(Count), at the level of detail for that table. In the previous example you can see Addresses(Count), Customers(Count), and LineItems(Count).

 

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.

Table showing the Tableau Count field can aggregate further
The calculated fields above are all included in the table alongside the Tableau Count of Orders field

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

tableau calculated field with the formula 1 to create the Number of Records field
This formula recreates the Number of Records

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!

21 thoughts on “Number of Records is missing in Tableau”

  1. 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?

    Reply
  2. 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.

    Reply
    • 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.

      Reply
  3. I am not able to use number of records =1. When I am pulling it on label, it is giving me the result =1

    Reply
      • 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

        Reply
        • 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?

          Reply
    • 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

      Reply
      • 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!

        Reply
        • 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.

          Reply
          • 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.

  4. 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.

    Reply
    • 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.

      Reply

Leave a Comment