COUNT DISTINCT returns invalid values

[maerlin9]

My datasource (coming from a partner connector I made) lists uniqueIDs per date (to summarize...). I would like, in a score card, to count distinct values of uniqueIDs, but the result is far too high. My guess is that Looker Studio does not properly handle the date field.

Trying to troubleshoot the score card, I ended up building a table and found an issue with COUNT DISTINCT by filtering on a particular uniqueID. When I show the underlying data, the result is fine. But in the table, it shows much more.

I think I have an issue with the date field. The table shows a warning: reaggregation of the data response is invalid.

[maerlin9]

Unless I miss something, there is clearly something wrong with COUNT DISTINCT...

Blending my Data with itself solves the problem, but Blend limitations makes it barely usable in my situation.

Without date range, everything is fine:

But When I select a range, CTD returns a direct count, not a count distinct.

The table "without blend" shows the warning explaining that "Response data is invalid". The suggested link is not very helpful.

With blended data, it goes back to normal.

My Datasource uses a partner connector we made, is there something I could improve in such connector?

Underlying data is the same for both tables, showing proper count distinct.

[maerlin9]

no clue?

[maerlin9]

this is driving me nuts. is there a way to make looker studio calculate a proper COUNT DISTINCT without blending data?

[MaxRev]

Hello mate, 

I'm facing the same issue as you were, did you find any solution about this? 

Have a good day

[maerlin9]

Yes I solved it, with a workaround, but I do not remember exactly how... I'll try to figure how in the next days or so

[MaxRev]

All right ! I'm really enthusiastic to know how haha, thank you.

[JoDev]

I’m having the same problem. COUNT_DISTINCT works fine until I apply any date related filter. Either with the Date Range Control, using the Date Range Dimension, or using a filter on the date value, or cross filtering with a date value.

Even when not filtering by date, if I add any date value to a chart, I get the Data Quality warning at the top of the chart, which displays “The data response has a bad reaggregation.” when I tap the warning, although the dates do work and display without any perceivable issue.

This issue only happens when connecting to data in Snowflake.

As a test, I defined the exact same data in both Snowflake and BigQuery, which was just a sequence of user_ids and dates as strings. Looker Studio has no issue with the exact same data from BigQuery.

I used Looker Studio with Snowflake quite extensively last year without this problem, it seems to be something new.

Any advice would be greatly received!