Looker doesn’t allow count_distinct measures in aggregate tables (unless the table is an exact match to the query) which is understandable given this would give inaccurate results. I was wondering, is there a way to tell Looker to sum the aggregated count_distinct results based on which dimensions are selected?
In my specific example, I have a database table that can have multiple records per day. I’d like to be able to create an aggregate table to optimize queries that is aggregated to each day and includes several dimensions and a count_distinct measure.
From this aggregate table, I’d like users to be able to choose only a subset of the dimensions but Looker will choose not to use the aggregate table since it’s not an exact match. However, taking the sum of the aggregate table’s distinct counts within each selected dimension should produce accurate results. Is there a way to do this in my explore?