Solved: Bug in Looker custom measure when using List of un... - Page 2 - Google Cloud Community

Log in to ask a question

Log in to ask a question

When I try to create a custom measure based on a string with the measure type "List of unique values" and I try to set filters, I get the following error:

A LookML model issue prevented this query from running.

Cannot filter on custom measure "xxx" based on a measure of non-aggregate type "string"

A LookML model issue prevented this query from running.Cannot filter on custom measure "xxx" based on a measure of non-aggregate type "string"

If I only change the measure type to "Count distinct", then it works. Also, when I use the "List of unique values" type, and I transform the filters to be custom filters, then it works.

Has anyone experienced this problem?

Solved

Solved

0 6 1,057

1 ACCEPTED SOLUTION

Thanks for sharing! That does look like valid SQL, and the correct SQL for what we want to compute.

Based on the error messages, my testing, and your testing, my hypothesis is as follows:

It is possible to generate SQL in the case of filtered list-agg measures.
However, Looker generally disallows this case and throws an error message. I'm not sure why, perhaps this is a failsafe against an edge case I'm not thinking of right now.
Somehow, you managed to slip past the error and get Looker to correctly generate the SQL for this use case. So far, I can't replicate this behavior.
In conclusion, I would say that supporting filtered list measures is a feature request. It seems that Looker intentionally doesn't allow this behavior, and you're only able to get it to work as desired in specific situations. As it happens, I was able to locate such a feature request! (support filtering list type measure #42566) I'll upvote it, and I recommend you do as well.

View solution in original post

6 REPLIES 6

never-displayed

Top Solution Authors

User

Count

1