These are the docs for Metabase v0.46. Check out the docs for the latest version, Metabase v0.54.
Case
case checks if a value matches a list of conditions, and returns some output based on the first condition that’s met. Basically, case works the same way as “if… then” logic, but it’s much nicer to write.
You can optionally tell case to return a default output if none of the conditions are met. If you don’t set a default output, case will return null after checking all of your conditions (null values are displayed as blank values in Metabase).
Use the case expression whenever you need to:
- bucket a range of values,
- label the rows in your dataset, or
- aggregate rows based on conditional logic.
| Syntax |
|---|
case(condition1, output1, condition2, output2, ..., default_output) |
| Returns the output from the first condition that’s met. |
| Example |
|---|
case(isempty("glass half full"), "empty glass", isnull("glass half full"), "missing glass", "glass half full") |
| “glass half full” |
Bucketing data for frequency tables or histograms
| Amount | Bucket |
|---|---|
| 6 | 0-9 |
| 18 | 10-19 |
| 31 | 30-39 |
| 57 | 50+ |
where Bucket is a custom column with the expression:
case([Amount] >= 0 AND [Amount] <= 9, "0-9",
[Amount] >= 10 AND [Amount] <= 19, "10-19",
[Amount] >= 20 AND [Amount] <= 29, "20-29",
[Amount] >= 30 AND [Amount] <= 39, "30-39",
[Amount] >= 40 AND [Amount] <= 49, "40-49", "50+")
Labeling a row based on conditions from multiple columns
| Sighting ID | Has Wings | Has Face | Sighting Type |
|---|---|---|---|
| 1 | True | True | Bird |
| 2 | True | False | Plane |
| 3 | False | False | Superman |
| 4 | False | True | Unknown |
where Sighting Type is a custom column with the expression:
case([Has Wings] = TRUE AND [Has Face] = TRUE, "Bird",
[Has Wings] = TRUE AND [Has Face] = FALSE, "Plane",
[Has Wings] = FALSE AND [Has Face] = TRUE, "Superman"), "Unknown")
You can use the columns holding your “labels” to:
- Apply business definitions or business logic to your datasets.
- Power a filter.
- Segment data for data sandboxing.
Aggregating data based on conditions from multiple columns
You can combine case with aggregate functions to only aggregate rows that meet your conditions.
For example, if we want to count the unique number of orders for each order date, but only those with a “Shipped” status:
| Order ID | Order Date | Status |
|---|---|---|
| 1 | 2022-04-01 | Paid |
| 1 | 2022-04-03 | Shipped |
| 2 | 2022-05-12 | Paid |
| 2 | 2022-05-12 | Cancelled |
- Create the custom expression
distinct(case([Status] = "Shipped", [Order ID]))and name it “Total Orders Shipped”. - Choose Order Date as the group by column.
- Click Visualize to return the result:
| Order Date | Total Orders Shipped |
|---|---|
| 2022-04-01 | 1 |
| 2022-05-01 | 0 |
Accepted data types
| Data type | Works with case |
|---|---|
| String | ✅ |
| Number | ✅ |
| Timestamp | ✅ |
| Boolean | ✅ |
| JSON | ❌ |
Limitations
All of the outputs must have the same data type.
Avoid::
case(condition1, "string", condition2, TRUE, condition3, 1)
Do::
case(condition1, "string", condition2, "TRUE", condition3, "1")
Related functions
This section covers functions and formulas that can be used interchangeably with the Metabase case expression, with notes on how to choose the best option for your use case.
Other tools
Using the table from the Coalesce: Consolidating values example:
| Notes | Comments | coalesce([Notes], [Comments] "No notes or comments.") |
|---|---|---|
| I have a note. | I have a comment. | I have a note. |
| I have a comment. | I have a comment. | |
| I have a note. | I have a note. | |
| No notes or comments. |
The Metabase coalesce expression
coalesce([Notes], [Comments] "No notes or comments.")
is equivalent to the case expression
case(ISBLANK([Notes]) = FALSE AND ISBLANK([Comments]) = FALSE, [Notes],
ISBLANK([Notes]) = TRUE AND ISBLANK([Comments]) = False, [Comments],
ISBLANK([Notes]) = FALSE AND ISBLANK([Comments]) = TRUE, [Notes],
ISBLANK([Notes]) = TRUE AND ISBLANK([Comments]) = TRUE, "No notes or comments")
coalesce is much nicer to write if you don’t mind taking the first value when both of your columns are non-blank. Use case if you want to define a specific output for this case (such as, “I have a note and a comment”).
Using the table from the Aggregating data example:
| Order ID | Order Date | Status |
|---|---|---|
| 1 | 2022-04-01 | Paid |
| 1 | 2022-04-03 | Shipped |
| 2 | 2022-05-12 | Paid |
| 2 | 2022-05-12 | Cancelled |
The Metabase countif expression
countif(case([Status] = "Shipped"))
is equivalent to the case expression:
count(case([Status] = "Shipped", [Row ID]))
countif is equivalent to case when you are counting all rows in the table that meet your conditions. It is not equivalent if you want to count unique rows that meet your conditions.
Using an expanded version of the table from the Aggregating data example:
| Row ID | Order ID | Order Date | Status | Amount |
|---|---|---|---|---|
| 1 | 1 | 2022-04-01 | Paid | $20 |
| 2 | 1 | 2022-04-03 | Shipped | $20 |
| 3 | 2 | 2022-05-12 | Paid | $80 |
| 4 | 2 | 2022-05-12 | Cancelled | $80 |
sumif([Amount], [Status] = "Shipped")
is equivalent to the case expression:
sum(case([Status] = "Shipped", [Amount]))
sumif is equivalent to case when you sum a single column for single condition.
You should use case if you want to sum a second column under a second, separate condition. For example, if you want to sum the Amount column when Status = “Shipped” and another (hypothetical) column like Refunded Amount when Status = “Refunded”.
In most cases (unless you’re using a NoSQL database), questions created from the notebook editor are converted into SQL queries that run against your database or data warehouse. Metabase case expressions are converted into SQL CASE WHEN statements.
Using the table from the Labeling rows example:
| Sighting ID | Has Wings | Has Face | Sighting Type |
|---|---|---|---|
| 1 | True | True | Bird |
| 2 | True | False | Plane |
| 3 | False | False | Superman |
| 4 | False | True | Unknown |
The SQL CASE WHEN statement:
SELECT
CASE WHEN "Has Wings" = TRUE AND "Has Face" = TRUE THEN "Bird"
WHEN "Has Wings" = TRUE AND "Has Face" = FALSE THEN "Plane"
WHEN "Has Wings" = FALSE AND "Has Face" = TRUE THEN "Superman"
ELSE "Unknown" END
FROM mystery_sightings
is equivalent to the case expression used for Sighting Type:
case([Has Wings] = TRUE AND [Has Face] = TRUE, "Bird",
[Has Wings] = TRUE AND [Has Face] = FALSE, "Plane",
[Has Wings] = FALSE AND [Has Face] = TRUE, "Superman", "Unknown")
For example, this SQL trick to order bar charts could be written using a Metabase case expression instead.
Spreadsheets
Using the table from the Labeling rows example:
| Sighting ID | Has Wings | Has Face | Sighting Type |
|---|---|---|---|
| 1 | True | True | Bird |
| 2 | True | False | Plane |
| 3 | False | False | Superman |
| 4 | False | True | Unknown |
The spreadsheet formula
=IF(AND(B2 = TRUE, C2 = TRUE), "Bird",
IF(AND(B2 = TRUE, C2 = FALSE), "Plane",
IF(AND(B2 = FALSE, C2 = TRUE), "Superman", "Unknown")
)
)
is equivalent to the case expression used for Sighting Type:
case([Has Wings] = TRUE AND [Has Face] = TRUE, "Bird",
[Has Wings] = TRUE AND [Has Face] = FALSE, "Plane",
[Has Wings] = FALSE AND [Has Face] = TRUE, "Superman", "Unknown")
There are many ways to implement conditional logic using Python. We’ll cover the approaches that make sense to convert into Metabase case expressions.
Using the table from the Labeling rows example (and assuming it’s in a dataframe called df):
| Sighting ID | Has Wings | Has Face | Sighting Type |
|---|---|---|---|
| 1 | True | True | Bird |
| 2 | True | False | Plane |
| 3 | False | False | Superman |
| 4 | False | True | Unknown |
numpy select()
conditions = [
(df["has_wings"] == True) & (df["has_face"] == True),
(df["has_wings"] == True) & (df["has_face"] == False),
(df["has_wings"] == False) & (df["has_face"] == True)]
outputs = ["Bird", "Plane", "Superman"]
df["Sighting Type"] = np.select(conditions, outputs, default="Unknown")
Helper function with pandas apply()
def Identify(df):
if ((df["has_wings"] == True) & (df["has_face"] == True)):
return "Bird"
elif ((df["has_wings"] == True) & (df["has_face"] == False)):
return "Plane"
elif ((df["has_wings"] == False) & (df["has_face"] == True)):
return "Superman"
else:
return "Unknown"
df["Sighting Type"]= df.apply(Identify, axis=1)
The approaches above are equivalent to the case expression used for Sighting Type:
case([Has Wings] = TRUE AND [Has Face] = TRUE, "Bird",
[Has Wings] = TRUE AND [Has Face] = FALSE, "Plane",
[Has Wings] = FALSE AND [Has Face] = TRUE, "Superman", "Unknown")
Further reading
Read docs for other versions of Metabase.
