Bug in Looker custom measure when using List of unique values

[igor-okuyama]

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:

Show More

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?

[sam8]

Hey there! This isn't a bug, this is a known limitation. 

Non aggregate measure types cannot be filtered on. From our Measure types documentation:

• Non-aggregate measures: Non-aggregate measures are, as the name suggests, measure types that do not perform aggregations, such as number and yesno. These measure types perform simple transformations, and since they do not perform aggregations, can reference only aggregate measures or previously-aggregated dimensions. You cannot use the filters parameter with these measure types.

As you've figured out, the way to get around this is to use an aggregate type of measure, such as a count distinct or a list.

[igor-okuyama]

Hi, @sam8 ! Thanks for your response! Actually I am using an aggregate measure as far as I know. Isn't the "List of unique values" an aggregation over values?

The problem is that this does not work and raises the error pointed out above:

But if I try to do the same thing using the Custom filter, it works:

Both images above were expected to generate the same result in my opinion.

[sam8]

Oh yeah, I totally agree! Both images should show the same thing. I think I misunderstood your original question.

When I tried, both failed with the following error message:

A LookML model issue occurred.

'filter_expression:' is not supported for measures of non-aggregate type 'list'

In your second image, where you don't see an image, do you see the SQL actually insert the filters? It seems to me like it shouldn't be working in either case.

[igor-okuyama]

Hi, @sam8 !

This is how the field gets compiled:

    STRING_AGG(DISTINCT CAST(CASE WHEN ( subscriptions.bl_has_platform_access   AND ( subscriptions.nm_type   = 'paid')) AND (NOT (( COALESCE(subscriptions.dt_canceled_at < subscriptions.dt_expires_in OR subscriptions.nm_canceled_by_email IS NOT NULL, FALSE)  ))) THEN subscriptions.nm_plan_label ELSE NULL END AS STRING), '|RECORD|') AS xxx

[sam8 · 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:

1. It is possible to generate SQL in the case of filtered list-agg measures.
2. 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.
3. 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.
4. 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.

[igor-okuyama]

Thanks @sam8 ! Good to know that there is already a feature request for this! I'll upvote it!