Drilling into the Details: Multiple Drill-downs in Looker

When reporting on key metrics in a business, one of the most common questions that clients ask us is: how can we gain further insight into a specific metric? How can we answer follow-up questions to a metric without diving into the business logic? Looker makes it easy to answer these questions by providing the tools with no coding necessary. So, if you are interested, keep reading!

Drill-down capabilities are a key feature of any good BI platform. A drill-down allows you to drill into the details of a metric by giving you a view of row-level information of the records that comprise that metric. For example, suppose you are looking at a single metric of the total revenue of your business this quarter, but you are interested in seeing which countries brought in the most revenue. In such a case, it would be useful to break the revenue metric down by country (see Figure 1); this is exactly what a drill-down allows you to do.

In Looker, the simplest way to enable a drill-down on a measure is to add the drill_fields parameter in the definition of that measure (see Figure 2). 

However, the approach of Figure 2 only allows you to enable single drill-down, which is demonstrated in Figure 3. Not only does this approach not allow you to have more than one drill-down, it also does not give you the option to specify what you want to label the actual drill-down. These two drawbacks ultimately worsen the user experience. 

An approach that addresses both of these shortcomings is to use the link parameter (instead of the drill_fields parameter) in the definition of the measure. The link parameter is usually used to direct a user to another URL such as that of an explore, a Look, or even another dashboard. An example of how the link parameter is usually used is also shown in Figure 2. However, using a clever trick, the link parameter can also be used to add drill-downs: see Figure 4.

 

The trick here is to reference a different measure (we give it the generic name drill_fields_country in Figure 4) which itself does contain the drill_fields parameter. In our example, we are only interested in breaking down the revenue by country (see Figure 5).

Importantly, this measure is used solely to define the drill-fields for other measures; it is not being used in any explore. For this reason, it is hidden. The measure simply exists to specify which fields to drill down another metric by. Note that it can be used in different measures. The value given to the type parameter is irrelevant for the same reason as above. Finally, the sql parameter has a value of zero simply to fill in a value. Again, this measure is used solely to define drill-down fields for another measure. 

The result of using the approach in Figure 6 is exemplified in Figure 7 below. 

To conclude, it is possible to add multiple drill-down options for a measure in Looker, and this is achieved by using the link parameter rather than the drill_fields parameter. 

As a leading EMEA partner with a world-class client net promoter score (+75), our in-house Looker team specialise in: Looker jumpstarts, enterprise-level deployments, bespoke model builds, embeddings and integrations. To find out more about our Data + Analytics services or to book a jumpstart, get in touch and keep up to date by following us on LinkedIn.