Skip to Content

Metrics

Join, filter, sort and group a metric

Use the Query toolbar to pull in other tables, keep only some rows, order them and roll them up.

Use the Query toolbar to pull in other tables, keep only some rows, order them and roll them up.

Before you start

  • The metric is open in the builder on the Schema tab. Create a metric
  • The metric has at least one field in Dimensions or Measures. The toolbar buttons stay off until then.
Query toolbar in the metric builder with Join, Filters, Sort, Group By and Dynamic Property
The Query toolbar holds Join, Filters, Sort, Group By and Dynamic Property.

Join a table

  1. Select Join.

    Query toolbar in the metric builder with the Join button highlighted before Filters, Sort and Group By.
    Join is the first button in the Query toolbar.
  2. Select Add join.

    Join panel with the text No joins yet and the Add join button highlighted.
    With no joins yet, the Join panel shows Add join.
  3. In Secondary Table, pick the table to join.

    Join card with the Secondary Table field highlighted and set to the dbn-customers table.
    The primary table is fixed; pick the Secondary Table.
  4. Select the join type icon and choose Inner Join, Left Join, Right Join or Full Join.

    Join card with the join type icon highlighted between Primary Table and Secondary Table.
    The join type icon sits between the two tables.
  5. Pick a column from each table, for example orders.customer_id and customers.id.

    Join card with the ON row highlighted: customer id from dbn-demo_sales equal to customer_id from dbn-customers.
    Under ON, pick the matching column of each table.
  6. Keep the operator Equal to (=), or pick another one.

  7. Select Apply join.

    Join panel with the Apply join button highlighted next to Clear all.
    Apply join is at the bottom right of the panel.

You can now add columns from the joined table. To join on more than one pair, select Add columns. A second join starts from Previous Join Data.

Joins are not available on the SQL tab, in Python mode, or on Elasticsearch and OpenSearch.

Filter rows

  1. Select Filters.

  2. Select Add Filter, then Simple Filter.

    Filters panel with the Add Filter menu open and Simple Filter highlighted above Complex Filter and Client Filter.
    Add Filter offers Simple Filter, Complex Filter and Client Filter.
  3. In Select a column, pick a column, for example status.

    Filters panel with the column field highlighted and set to sales channel.
    Pick the column the filter checks.
  4. Pick an operator, for example Equal to (=).

    Filters panel with the operator field highlighted and set to Equal to (=).
    The operator is under the column.
  5. Enter or pick the value, for example paid.

    Filters panel with the value field highlighted and set to Online.
    Pick or type the value next to the operator.
  6. Select Done.

To add another filter, finish the current one first. Then choose AND or OR in Join with previous filter.

Column typeOperators
TextEqual to (=), Not equal to (!=), In, Not in, Like (case sensitive), Is not null, Is null
NumberEqual to (=), Not equal to (!=), Less than (<), Less or equal (<=), Greater than (>), Greater or equal (>=), In, Not in, Is not null, Is null
DateThe number operators, and Between

For a date, pick a preset such as Last Year, or pick dates with Select Date or Select Range.

The Add Filter menu also has Complex Filter, for a SQL condition, and Client Filter. Add a complex filter

Sort rows

  1. Select Sort.

  2. Select Add Sort, then Simple Sort.

    Sort panel with the Add Sort menu open and Simple Sort highlighted above Complex Sort.
    Add Sort offers Simple Sort and Complex Sort.
  3. Pick the column.

  4. Choose Ascending or Descending.

    Sort panel with sum of profit picked and the order field highlighted and set to Descending, and the Apply sort button.
    Set the order under the column.
  5. Select Apply sort.

Sorts apply in order, top to bottom. Use the arrows to move a sort earlier or later. To sort by a SQL expression, choose Complex Sort, fill in Sort expression and Sort name, and select Add.

Group rows

  1. Select Group By.

  2. Select the dimension columns to roll the rows up by.

    Group By panel listing the table columns with store name ticked and highlighted, and the Apply button.
    Tick the columns to roll up by.
  3. Select Apply.

This Group By changes the query. The Group By item in Add filter above the chart is different: it lets viewers switch the series. Group By on the chart

If something goes wrong

  • Joins will be enabled after dragging and dropping a column. Add a field to Dimensions or Measures first.
  • No groupable columns. Add a column to Dimensions, then open Group By again.
  • You don’t have permission to add joins. Ask an Admin for the Editor role.