Advanced Filtering Help

Chris_CF · 03-14-2025 10:25 AM

Hi,

I have an Explore that links "Student" records with N-number of "Questions" records and associated "Question Answers" records. In the Explore, I can view each student's answer to the different questions by using the "Name Label" field on the Question as a pivot and then I have a measure that I've defined for the Question Answer that gives me the answer value.

Here's an example of what the results look like in the Explore:

What I'm trying to do is create a filter that returns all the student records where they, for example, answered "Yes" to one question AND "Current Student" to the other question.

When I try to do this in the Explore I run into the following limitation:

Is there a good way to do what I'm trying to do?

Any questions, insights, or thoughts are very welcome!

Thanks,

Chris

sam8

I think this should be possible, as long as Looker doesn't try and filter on dimensions and measures simultaneously.

Right now you have four conditions, all connected by AND operators. However, the nesting is bothering Looker. Right now, you're doing (condition1 AND condition2) AND (condition3 AND condition4). Can you change this to (condition1 AND condition2 AND condition3 AND condition4)? If each condition is separate, and none of them are "grouped" together, Looker should be happy.

Chris_CF

Hi @sam8 ,

Thanks for the reply!

The underlying issue seems to be that since my "questions" and "answers" that I'm trying to filter on are all repeated fields that reference entries in the same table, the filter I'm trying to do is effectively like trying to filter for any users where they answered "yes" and also "no": these both can't be true at the same time so in the best case I get an empty result set.

Are you aware of any best practices for how to perform a filter operation like this on a repeated field?

Thanks!

sam8

ooh, that's tricky.

I believe I've seen some customers get around this by concatenating or merging the repeated fields together. For example, create a list type measure that collects all the "yes" and "no" answers. Then you can filter on that one field containing both yes and no.