Tableau does a great job of connecting to a variety of databases, but it sometimes needs help on the database end to get the results we need to make hot visualizations. One such example showed itself when connecting to Google BigQuery.

The issue? Count distincts. BigQuery doesn’t handle count distincts like one would expect. It gives a statistical approximation (inaccurate) result. Fortunately, there’s a way to fix this. You can pass the logic directly to BigQuery to force it to CountD.

Just follow these steps:

Step 1

In Tableau, create a new calculated field using the dimension you are wanting BigQuery to count distinct:

Step 2

Create a SQL Pass Through calculation:

Step 3

Create a calculation using the syntax in the image below. In this example, we are getting a count of unique employees based on the Employee ID field.

Note: You may now use your newly created calculated field as you would any other; however, given that this is an aggregate measure, it may only be used with other aggregate measures. Also, be mindful that this solution hurts performance (see our Tableau Performance Checklist for best practices), so only use this when you must.