3 ways to use CASE WHEN in Google Data Studio

Mallory Stein

Mallory Stein

Max min read

Using formulas like CASE WHEN in Google Data Studio is an easy way to create dynamic text, filter metrics, and even password protect your reports. Read on to discover 3 unique ways to use CASE WHEN to capture the most value in your Data Studio reports.

There are those that use Data Studio.

Then there are those that use formulas in Data Studio.

Using custom formulas to manipulate data, create new metrics (or even custom calculations) can help take your Data Studio reports to the next level. This article will dig in to 5 ways you can use simple Data Studio formulas like CASE WHEN to take your skills from beginner to intermediate. But first an important question: what exactly is the CASE WHEN formula?

What is CASE WHEN in Data Studio?

CASE WHEN is a custom formula that evaluates a list of conditions (or cases), before returning a desired result. In essence, if you want to show a value conditionally based on different criteria, CASE WHEN is the perfect formula for you. Here’s an example.

Let’s say you want to group different sizes of orders into three categories, Small, Medium and Large. Using CASE WHEN in Data Studio, you can set up a custom dimension that looks like this:

CASE

WHEN Order Size < 100 THEN “Small”

WHEN Order Size < 200 THEN “Medium”

WHEN Order Size >= 200 THEN “Large”

END

Using CASE WHEN for conditional text

Let’s say you want to create a Google Data Studio report that adapts to the way it’s being used. For example, you could create a text box that compliments a user when it sees an Ad Data metric that looks like it’s risen above a certain threshold.

In this case we’ll have a positive cheer if impressions are above 50,000, and a “Needs Improvement” callout if it falls under.

1) Create a custom field

The first step is to create a custom field by navigating to the bottom right of your “Data” column and selecting Add a Field.

Create A Custom Field For Case When

After that, you can insert your CASE WHEN formula, where as you type in a metric or dimension that’s already in your data source, you should see it prepopulate, like the screenshot below:

Conditional Text Case When Example

2) Insert your custom dimension and stylize

Now that you have your dimension set, which will display a line of text based on a conditional value, it’s time to insert the dimension into your report.

To do that, add a “Chart” from the Insert tab, and drag your dimension into the chart. It might look a little funky at first, so let’s stylize it.

Make the chart headers transparent, remove the header row, remove pagination and row numbers, and boom. Just like that, you have conditional text that adjusts depending on another value.

Conditional Text Case When Showcase

Using CASE WHEN to filter data

Another use case for CASE WHEN is to filter particular data sets depending on a conditional value. For example, if let’s say there’s a marketer dispensing a paid ad data report to different stakeholders across different departments.

While this report may contain all digital marketing, inbound and sales data, you want to only show data relevant to each department.

Fortunately, with CASE WHEN, this is a simple 4 step process.

1) Create a new parameter

The first step is to create a new parameter that will be used to fuel a data control. To do this, click the “Add a parameter” in the bottom right of your report. There, you’ll select permitted values as List of values, and enter each value accordingly:

Create New Parameter

2) Create a data control with that new parameter

The next step is to create a new data control, which will be represented by the parameter we’ve just created.

Create New Data Control

Now add the parameter as the control field:

Set Parameter Data Control

3) Create a new conditional field that shows data, well, conditionally

Now that we have our parameter and control set up, it’s time to create new conditional fields that are connected to our current data set, but that are now formulated to only show data based on specific criteria.

Here is where we’ll use the CASE WHEN method to create a new field. In this case, I want to set up a conditional field that returns one of either Marketing, Sales or Organic/Inbound based off the different platforms we have in our data set.

CASE

WHEN Platform = “Paid Search” AND Conditional Data = “Marketing” THEN “Marketing”

WHEN Platform = “Paid Search Partners” THEN “Sales”

ELSE “Inbound/Organic”

END

With this field configured, you can now add it to your any charts and graphs, and use our data parameter above (Conditional Data) to filter out values accordingly.

Using CASE WHEN to password protect reports

While we cover this topic in more detail in this article about how to create password protected Data Studio reports, here’s a quick run down of the process for those keen on using CASE WHEN for password protection.

Users may want to password protect their Data Studio reports if they’re sharing their report publicly and only want certain users to have access. This could be achieved with email sharing, but when you’re trying to share en masse, email sharing runs into limitations.

Instead, CASE WHEN provides a quick and nimble solution to protecting your reports with a password.

1) Create a new parameter, “password”

We’ll start by creating that parameter password, which will then be used for an input box data control that you can put on the same page, or a separate page. Keep the type as “text”.

New Parameter Settings

2) Create a conditional field, “Include”

Next, we’ll create a conditional field called “Include”. In essence this field will use CASE WHEN to evaluate the value of the password input box (controlled by the password parameter), and return a 1 or an 0 depending on the value you’ve set. For example:

Case When Formula

3) Add a filter to the charts you’d like to hide, dependent on the value of “Include”

The final step is to create a filter on the charts you’d like to hide. This filter will essentially run as follows:

If Include = 1, then include all the data

If Include = 1, then exclude all the data.

And that’s it! In three simple steps, you’ve password protected your data studio report.

Create incredible Data Studio reports in seconds

Fuel your ad data reports with clean, accurate Joinr data and make your Data Studio reports shine.