How do I filter results in my visualization, without filtering out rows in the data tab?

[sarah_tayeri]

The Problem:

I have a use case where I need to filter out rows in my visualization, but need all the rows to remain in the data tab. For example, I have a calculation/aggregation that relies on all the rows being in the results, and when I use a regular filter, the results of the calculation/aggregation are skewed because certain rows are omitted. 

The Solution: 

To achieve this, we can use a filter-only field to grab the user’s input, and a yesno dimension to return “yes” when the field’s value is equal to the filter input. Then, including the yesno dimension in the results, we can use the ‘Hide “No”s from Visualization’ in order to only display the “yes” values in the visualization. 

Example of the Implementation: 

First, we define the filter-only field: 

filter: state_filter {
    suggest_explore: order_items
    suggest_dimension: users.states
  }

The suggest_explore and suggest_dimension parameters are used to generate filter suggestions for the filter. 

Then, we define a yesno dimension, designed to return “yes” when the state field matches the input of the state_filter, and no otherwise: 

dimension: yesno_field {
    type: yesno
    sql: {% condition state_filter %} ${states} {% endcondition %} ;;
  }

Then, we construct the explore, including the state_filter and yesno_field dimension. Remember to click the gear icon for the yesno_field in the data tab, so we can ‘Hide “No”s from Visualization’. 

Note that the state filter doesn’t actually filter the values in the results, allowing the “Global Avg” table calculation to calculate the average across all states, as expected.