CODEWITHSUNDEEP
countgoogle-bigquerytableau-api
Fabio Fantoni
Fabio Fantoni

Reputation: 3167

Tableau Desktop: Count Distinct returns higher value than Count (if connected to Google Big Query)

I have a table with 2157 records, let's say with 3 columns (A,B,C) and I know that in column A there are 2154 different values.

Using Tableau Desktop (and its own fenctions) connected to BigQuery, I get these results:

If i run the same calculations using queries in Big Query console, I get:

How can this be possible?

By the way, if use a CSV file of the same table and I use it as datasource using Tableau Public (no Desktop), I get correct results as in Big Query.

Thanks in advance

Upvotes: 3

Views: 3735

Answers (2)

outlier123
outlier123

Reputation: 227

Try using a calculated field with this formula - RAWSQLAGG_INT("count(unique(%1))",['Your column'])

Slows down performance, so only use when you really need it.

Upvotes: 0

Pentium10
Pentium10

Reputation: 207912

If you use the DISTINCT keyword, the function returns the number of distinct values for the specified field. Note that the returned value for DISTINCT is a statistical approximation and is not guaranteed to be exact - the documentation is also clear about this.

If you require greater accuracy from COUNT(DISTINCT), you can specify a second parameter, n, which gives the threshold below which exact results are guaranteed. By default, n is 1000, but if you give a larger n, you will get exact results for COUNT(DISTINCT) up to that value of n. However, giving larger values of n will reduce scalability of this operator and may substantially increase query execution time or cause the query to fail.

To compute the exact number of distinct values, use EXACT_COUNT_DISTINCT. Or, for a more scalable approach, consider using GROUP BY on the relevant field(s) and then applying COUNT(*). The GROUP BY approach is more scalable but might incur a slight up-front performance penalty.

https://cloud.google.com/bigquery/query-reference#aggfunctions

Upvotes: 1

Related Questions