Creating a Dynamic Filtered measure

Hi all,

I’m trying to implement an event analysis in my explore based on Google Analytics data.

At the moment, I have 3 dimensions and 1 measure:

dimension: event_info_action {
view_label: “Events”
label: “Action”
type: string
sql: ${TABLE}.“EVENT_INFO_ACTION” ;;
}

dimension: event_info_category {
view_label: “Events”
label: “Category”
type: string
sql: ${TABLE}.“EVENT_INFO_CATEGORY” ;;
}

dimension: event_info_label {
view_label: “Events”
label: “Label”
type: string
sql: ${TABLE}.“EVENT_INFO_LABEL” ;;
}

measure: Totals_events {
type: count
filters: {
field: type
value: “EVENT”
}

My users would like to analyse particular types of events, meaning specific values of action or category and compare these metrics with others like visits, transactions and so on…, . So I could end up creating hundreds of filtered measures like this :

measure: Totals_events_for_site1_user1_click_on_a_button {
type: count
filters: {
field: hit.type
value: “EVENT”
}
filters: {
field: event_info_category
value: “VALUE”
}

The problem with that is my explore will become cluttered with so many measures that are not relevant for all parties.

So I would like to know if there would be a way to make the filter in my measure in LookML dynamic based on a selection in my explore.

At the moment, I can run this, it just gives the breakdown of all events. I can put a filter in my explore but in that case, it’s filtering my complete query to my other measures are also filtered which is not what I want to achieve.

That’s why I’m looking for a solution to embed the filter in my measure in LookML in a dynamic way.

Thanks a lot for your help !
Anthony

Hey Anthony,

This can be done by using a couple yesno dimensions with templated filters and a set of parameters.

In our example we will be using an ecommerce dataset and counting orders with different statuses. We can go from this:

dimension: status {
    sql: ${TABLE}.status ;;
  }
  
  measure: count_complete {
    type: count_distinct
    sql: ${id} ;;
    drill_fields: [detail*]
    filters: {
      field: status
      value: "Complete"
    }
  }

  measure: count_returned {
    type: count_distinct
    sql: ${id} ;;
    drill_fields: [detail*]
    filters: {
      field: status
      value: "Returned"
    }
  }

etc...
  


To nicely consolidating this down to one measure and a filter selector:

dimension: status {
    sql: ${TABLE}.status ;;
  }
  
  parameter: status_selector {
    suggest_dimension: status
  }
  
  dimension: selected_status {
    type: yesno
    hidden: yes
    sql: {% condition status_selector %} ${status} {% endcondition %} ;;
  }
  
  measure: count_filtered {
    type: count_distinct
    description: "To be used with the Status Selector"
    sql: ${id} ;;
    drill_fields: [detail*]
    filters: {
      field: selected_status
      value: "Yes"
    }
  }
1 Like

Hello Chris,

Thanks a lot for your help.

I’ve just tried implementing it and it worked beautifully :wink:

Have a nice day!
Antho