Custom expressions are like formulas in spreadsheet software like Excel, Google Sheets, and LibreOffice Calc. They are the power tools in the notebook editor of the query builder that allow you to ask more complicated questions.
To use custom expression, create a Custom Column (where the custom expression is used as a Field Formula to calculate values for the new column), or click on Filter or Summarize and select Custom Expression.
When using the query builder, you can use expressions to create new:
= contains([comment], "Metabase")
= share([Total] > 50)
= [Subtotal] / [Quantity]
This page covers the basics of expressions. You can check out a full list of expressions in Metabase, or walk through a tutorial that shows you how you can use custom expressions in the notebook editor.
There are two basic types of expressions, Aggregations and Functions. Check out a full list of expressions.
Aggregations take values from multiple rows to perform a calculation, such as finding the average value from all values in a column. Aggregations functions can only be used in the Summarize section of the notebook editor, because aggregations use values from all rows for that column. So while you could create a custom column with the formula [Subtotal] + [Tax], you could not write Sum([Subtotal] + [Tax]), unless you were creating a custom metric expression (that would add up all the subtotals and taxes together).
[Subtotal] + [Tax]
Sum([Subtotal] + [Tax])
Functions, by contrast, do something to each value in a column, like searching for a word in each value (contains), rounding each value up to the nearest integer (the ceil function), and so on.
Use +, -, * (multiply), / (divide) on numeric columns with numeric values, like integers, floats, and double. You can use parentheses, ( ad ), to group parts of your expression.
For example, you could create a new column that calculates the difference between the total and subtotal of a order: = [Total] - [Subtotal].
= [Total] - [Subtotal]
You can’t currently do math on timestamp columns (we’re working on adding new date functions soon, so stay tuned).
AND, OR, NOT, >, >= (greater than or equal to), <, <= (less than or equal to), =, != (not equal to).
For example, you could create a filter for customers from California or Vermont: = [State] = "CA" OR [State] = "VT".
= [State] = "CA" OR [State] = "VT"
You can refer to columns in the current table, or to columns that are linked via a foreign key relationship. Column names should be included inside of square brackets, like this: [Name of Column]. Columns in connected tables can be referred to like this: [ConnectedTableName.Column].
[Name of Column]
You can refer to saved Segments or Metrics that are present in the currently selected table. You write these out the same as with columns, like this: [Valid User Sessions].
[Valid User Sessions]
Some things to keep in mind about filter expressions and conditionals:
[Subtotal] + [Tax] < 100
countif( round([Subtotal]) > 100 OR floor([Tax]) < 10 )
If you want to work with dates in your filter expressions, the dates need to follow the format, "YYYY-MM-DD" — i.e., four characters for the year, two for the month, and two for the day, enclosed in quotes " and separated by dashes -.
between([Created At], "2020-01-01", "2020-03-31") OR [Received At] > "2019-12-25"
This expression would return rows where Created At is between January 1, 2020 and March 31, 2020, or where Received At is after December 25, 2019.
See a full list of expressions.
For a tutorial on expressions, see Custom expressions in the notebook editor.
Did this article help you?
Thanks for your feedback!