Nested values and filtering in looker studio

[attwoliver]

Hi!

I have wide big query table on users with one field being a nested field. Now this specific nested field, let's call it conditions, is from a multi-select survey. (see nested table example)

table 1Wishfully, I want to use nested tables in Looker studio because big query encourage them. What Iwant to do is use conditions to filter my reports to people with specific conditions. However, when I create a drop-down multi-select filter using condition, it seems to always flatten the data AND repeat user rows. This is an issue when I want to look at all users with X, Y OR Z conditions because aggregations count duplicate users. For instance a basic record count with filter (Y and Z) = 3 (counting user 2 once, and user 3 twice) when the wanted count is 2. (see table 2)table 2

Now obviously, I could use count distinct to solve this. But this is not possible for more complex calculations where the user table is used in a blend where I join to more user data. 

So, what are my options for being able to filter users by multiple conditions but making sure I don't duplicate user records in the process.

[ArkadyZagdan]

@attwoliver I guess the easiest way to solve is :

- Unnest in BigQuery 
- join with other data and transform to your "ideal" view
- connect as materialized view or custom query to Looker Studio and start visualizing

Regards
Arkady

[attwoliver]

I might not be understanding your suggestion @ArkadyZagdan . I think it does not solve the issue because unnesting in big query does the same repeating of records again creating a duplicate issue if records aren't pre-filtered in the query – which is not what I want because I want users to be able to select condition theirselves.