Filter out Values of Attribute Filters

You can restrict particular values of an attribute filter (see Filter for Attributes) so that they do not appear in the list of values and a user cannot see or select them.

To do so, add one or more metrics to the attribute filter. Based on the added metrics, the filter drop-down shows only the values for which at least one of the metrics returns a non-zero value.

This feature is also known as the useAvailable feature.

Contents:

Overview

You have a report with a number of sold products sliced by city.

     

The report has a filter applied at the report level to show only the products sold in Italy.

     

The report is placed on a dashboard that has an attribute filter for cities. By default, the filter shows all cities from the dimension, not only Italian cities.

     

Goal:
You want the city filter to show only those Italian cities that have at least one sale (that is, a non-zero value).

Solution:
Add a metric to the city filter's configuration to limit the filter values. This metric returns the number of orders in Italian cities. If the number of orders for the city is greater than 0 (zero), the city appears as a value in the attribute filter drop-down.

SELECT COUNT(OrderID) WHERE Country=IT

Results:

     

Let's compare side by side the original version of the filter values to the version with the metric applied:

   

Additional Considerations

Clicking Select all in an attribute filter with the added metrics overrides all limitations that the added metrics bring. In this case, all the filter values are displayed in the report, including those values that were hidden as irrelevant (even if they are not visible in the filter dropdown). The whole filter then behaves as if it was not applied. The same behavior is observed when All is the default value of the filter (for the filters where multiple values are allowed to be selected).

   <-->  

In the context of the example provided in this topic, clicking Select all in the city filter allows the report on the dashboard to show all cities, not only those with non-zero values for sales and not only Italian cities.

This behavior allows you to display, for example, top 50 cities in the filter dropdown when you need to only display the relevant cities, and then click Select all to switch to selecting all cities in the filter with no limitations applied.

When all visible values are selected (regardless whether filtered by a metric or not), the filter behaves the same way as if you have clicked Select all. That is, the filter is not applied. This is intended behavior.

Configuration

  1. Open your dashboard for editing.
  2. Select the filter for which you want to filter out values based on metrics. 
    For information about creating attribute filters, see Filter for Attributes.
  3. Click the Gear icon that appears above the filter.
    The filter's configuration opens.
  4. Click Available Values, then click Add metric.
  5. Select the metric from the list.
  6. Add more metrics if needed.
  7. Click Apply.
  8. Click Save.