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.

Bucketing data for frequency tables or histograms

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

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:

1. Create the custom expression distinct(case([Status] = "Shipped", [Order ID])) and name it “Total Orders Shipped”.
2. Choose Order Date as the group by column.
3. Click Visualize to return the result:

Accepted data types

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.

Metabase expressions

• Coalesce
• Countif
• Sumif

Other tools

• SQL
• Spreadsheets
• Python

Coalesce

Using the table from the Coalesce: Consolidating values example:

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”).

Countif

Using the table from the Aggregating data example:

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.

Sumif

Using an expanded version of the table from the Aggregating data example:

The Metabase sumif expression

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”.

SQL

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:

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:

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")

Python

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):

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

• Custom expressions documentation
• Custom expressions tutorial