blog

Drilling into the Details: Multiple Drill-downs in Looker

Author: Daniel Luo, Data Scientist

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.

Figure 1: Example of a drill-down. The total product revenue is broken down by country. 

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

Figure 2: The LookML code for creating a single drill-down for a measure.

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. 

Figure 3: If using the approach to drill-downs described in Figure 2, clicking on a metric on a dashboard displays the drill-down functionality as “Show All $806,984”, which does not provide any information about what the metric is being drilled down by.

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.

Figure 4: An alternative way to enable a drill-down on a measure, using the link parameter instead of the drill_fields parameter. This approach not only allows you to create multiple drill-downs, but it also lets you label the descriptions of each drill-down.

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

Figure 5: A separate measure can be used to define the fields which we want to drill another measure down by. This measure can be defined in the same view or in a different view to the measure whose drill-down fields it specifies.

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. 

Figure 6: Specifying multiple (two in this case) drill-down options for a measure using link parameters. Using this method, there is no limit to the number of drill-downs that can be added to a metric.

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

Figure 7: A metric with two drill-down options, each with a specified label.

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 Modern BI & Analytics services or to book a jumpstart, get in touch with our team.

Up next
Case Studies
View now