Filtered custom measure of type list generates lookml error

I'm trying to create a filtered custom measure to display a single value. The view being referenced contains many values related to a "product" and I'm trying to create a column for specific field values. 

jredl_0-1716558829371.png

When I attempt to use this custom field, looker returns an error:


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

I changed to using a custom filter and looker generates the query correctly. I worked around the above error but looker still doesn't display any results. The weird part is that Looker is generating the SQL correctly and is exactly what I want for my column. 

 

STRING_AGG(DISTINCT CAST(CASE WHEN product_custom_field.external_field_id = 'station-code' THEN product_custom_field.field_value ELSE NULL END AS STRING), '|RECORD|') AS station_code

 

jredl_1-1716559032649.png

Anyone have any tips or pointers to achieve this? 

For reference here is the view itself:

 

view: product_custom_field {
  sql_table_name: `@{project}.@{partition_id}.ProductCustomField` ;;

  dimension_group: created {
    type: time
    description: "The time at which the custom field was created."
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}.created ;;
  }
  dimension: external_field_id {
    type: string
    description: "The unique identifier for the custom field set by the partner."
    sql: ${TABLE}.external_field_id ;;
  }
  dimension: field_name {
    type: string
    description: "The name of the custom field."
    sql: ${TABLE}.field_name ;;
  }
  dimension: field_value {
    type: string
    description: "The value of the custom field."
    sql: ${TABLE}.field_value ;;
  }
  dimension: internal_field_id {
    type: string
    description: "The unique identifier for the custom field set by the system."
    sql: ${TABLE}.internal_field_id ;;
  }
  dimension: product_id {
    type: string
    description: "The unique identifier for the product."
    sql: ${TABLE}.product_id ;;
  }
  dimension_group: updated {
    type: time
    description: "The time at which the custom field was last updated."
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}.updated ;;
  }
  measure: count {
    type: count
    drill_fields: [field_name]
  }
  measure: list {
    list_field: field_value
    type: list
  }
  measure: sum {
    type: sum
    sql: SAFE_CAST(${TABLE}.field_value as integer);;
  }
}

 

Solved Solved
0 4 1,180
1 ACCEPTED SOLUTION

Yeah you probably could use parameters for this: https://cloud.google.com/looker/docs/reference/param-field-parameter#parameters_of_type_string 

Then whoever is exploring just has to choose the field they want in the filter value 

 

View solution in original post

4 REPLIES 4

This is currently a built-in limitation of custom fields not based on numeric types. I would recommend checking out https://www.googlecloudcommunity.com/gc/The-Kitchen-Table/Feature-Requests-How-your-feedback-makes-L... to file feedback for this to be supported. 

In the meantime the easiest way to achieve what you want is likely just going to be to make a LookML measure of type string that has that SQL that Looker generated above. So something like: 

 

measure: station_code
type: string
sql: STRING_AGG(DISTINCT CAST(CASE WHEN product_custom_field.external_field_id = 'station-code' THEN product_custom_field.field_value ELSE NULL END AS STRING), '|RECORD|') AS station_code ;;

 

Thanks for the reply, and yes I will log a feature request for this.

The problem I face is that these fields are dynamic by customer meaning I
can't set the filter field to be station-code. Any suggestions perhaps on
how to do this with liquid syntax or another approach entirely?

Yeah you probably could use parameters for this: https://cloud.google.com/looker/docs/reference/param-field-parameter#parameters_of_type_string 

Then whoever is exploring just has to choose the field they want in the filter value 

 

Thanks, have it working for a filtered field. Now, to update to use parameters to allow multiple filters be specified. 

Its truly unfortunate that string measures aren't support as a custom dimension as that would make this so much easier. 

  measure: filtered_field_value {
    type: string
    sql:
    {% if product_custom_field.external_field_id._is_filtered %}
    STRING_AGG(
      DISTINCT CAST(
        CASE
          WHEN {% condition product_custom_field.external_field_id %} product_custom_field.external_field_id {% endcondition %} THEN product_custom_field.field_value ELSE NULL
        END
      AS STRING), '|RECORD|')
    {% else %}
      ""
    {% endif %} ;;    
  }

 

Top Labels in this Space
Top Solution Authors