Looker - How to have 1 dashboard filter for Name that will dynamically change multiple visuals that have a different Name column?

Question

In Looker I have a dashboard that shows details about Tickets.

The 1st visual shows the NAME of the user who Opened the ticket and a count, then there is a 2nd visual showing the NAME of the user who is currently assigned the ticket and a count, and finally there is a 3rd visual showing the NAME of the user who closed the ticket and a count.

With these 3 visuals, the column/field for each NAME is in a different LookML view.

What I want to achieve, is have 1 dashboard filter to select a NAME that will filter all 3 visuals. But because each NAME field is in its separate LookML view - it is problematic choosing 1 of these 3. For instance, if I choose to use the NAME for who Opened the ticket as my dashboard filter, just because the NAME I select in my dashboard filter opened the ticket does not mean they are currently assigned or closed the ticket - meaning this filter would ONLY be useful for the visual showing who Opened the ticket and a count.

I want 1 filter that will dynamically filter all 3 visuals showing just 1 name and the same name which is provided in that dashboard filter.

Table Data:

• OpenedByTable -id, name
• AssignedTable -id, name
• ClosedTable -id, name

I am able to re-use a 'user' LookML view in case I need a 4th LookML view. Perhaps this 4th view could be used to join the above 3 onto?Then I use this desired/new column from the 4th LookML view

Answer 1

It does not matter that the name fields are in different views. When creating a filter, the view you are pointing is just used to populate the filter alternatives.

Let's say you have a filter based on OpenedByTable.Name. When you click on the filter Looker will issue a query something like this to generate the alternatives:

SELECT Name 
FROM OpenedByTable.Name
GROUP BY 1

If one of the alternatives is Karl and you select that, looker will then issue a query like this to AssignedTable.

SELECT *
FROM AssignedTable
WHERE AssignedTable.Name IN ('Karl')

I guess your explore looks something like this:

explore: tickets {}

view: tickets {
  derived_table: {
    sql:
      SELECT 'A' AS ticket_id, 'Mike' AS opened_by, 'Carl' AS closed_by
      UNION ALL
      SELECT 'B' AS ticket_id, 'Mike' AS opened_by, 'Mike' AS closed_by
      UNION ALL
      SELECT 'C' AS ticket_id, 'Carl' AS opened_by, 'Carl' AS closed_by
    ;;
  }

  dimension: ticket_id {}
  dimension: opened_by {}
  dimension: closed_by {}

  measure: count_tickets {
    type: count_distinct
    sql: ${ticket_id} ;;
  }

}

You can then build a .lookml-dashboard like this to have one visual for closed and one for opened, with a single name filter to control both.

- dashboard: tickets
  title: tickets
  layout: newspaper
  preferred_viewer: dashboards-next

  filters:
  - name: name
    type: field_filter
    ui_config:
      type: button_group
      display: inline
    explore: tickets
    # just to populate the filter, if there are people who have not closed tickets
    # but have opened, you should build a name/dimension-explore 
    field: tickets.closed_by 
  
  elements:
  - title: Closed
    name: Closed
    explore: tickets
    type: table
    fields: [tickets.closed_by, tickets.count_tickets]
    listen:
      name: tickets.closed_by # filter the opened_by field with whatever name you have selected in the filter
    row: 0
    col: 0
    width: 3
    height: 3
  
  - title: Opened
    name: Opened
    explore: tickets
    type: table
    fields: [tickets.count_tickets, tickets.opened_by]
    listen:
      name: tickets.opened_by # filter the opened_by field with whatever name you have selected in the filter
    row: 0
    col: 3
    width: 3
    height: 3
  

Or if you build dashboards visually set the filter like this: