🔝Dynamic Top N control in Looker Enterprise using LookML and Table Calculations

Ever needed a “Top N” filter on a Looker dashboard only to find it filtered the entire dashboard or wouldn’t let users change the limit dynamically? Here is the fix.

In enterprise dashboard design, flexibility is everything. It is a very common scenario: you are building an executive dashboard and need a specific chart — say, top categories by revenue — that allows the user to switch between viewing the Top 3, Top 5, or Top 10. However, you do not want this input control to alter the rest of your dashboard tiles, nor do you want to hardcode SQL LIMIT clauses into your model.

In Looker Enterprise, standard approaches often fall short: either the dashboard filter impacts all connected visualizations equally, or you end up duplicating Explores unnecessarily.

In this tutorial, you will learn how to solve this architectural challenge by combining a LookML parameter with Table Calculations. This pattern allows you to isolate filter behavior to a single tile while giving end users full interactive control without compromising your data model.

Tutorial objective: Implement a dynamic input control on a Looker Enterprise dashboard to limit visible rows for a single tile using LookML and post-SQL filtering.

For example forthe Top 2 and 5:

Example Top 2

Example Top 5

Step 1: Define the parameter and helper dimension in LookML

The first step takes place in your modeling layer. We need to capture the numeric value selected by the user in the dashboard and make it accessible to our logic.

1. Open your view file (in my example, sievo_info.view).
2. Define a numeric parameter that acts as the UI input control.
3. Create a helper dimension of type number that extracts the selected value using Liquid syntax ({% parameter %}).

# 1. Input selector for the dashboard Top N control
parameter: top_n_limit {
  type: number
  label: "Top N Limite"
  default_value: "5"
}

# 2. Helper dimension to read the selected value
dimension: top_n_selected {
  type: number
  sql: {% parameter top_n_limit %} ;;
}

View

Consultant Pro-Tip: Using LookML parameters instead of standard dimensions allows you to pass user-defined values dynamically into queries without enforcing rigid SQL WHERE clauses. It is the secret to breaking free from static queries.

Step 2: Configure the Explore query for the tile

Once your model is updated, navigate to the Explore layer to build the query for the target tile.

Get Nora Roser Tamarit’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Set up the following elements in the Explore interface:

• Primary dimension: Select your dimension (e.g., Category L2).
• Measure: Add your primary metric (e.g., Count) and make sure it is sorted descending (Count DESC).
• LookML dimension: Add the Top N Selected dimension you created in Step 1 to include it in the data table.
• Query filter: Add the Top N Limite parameter to the filters section with an initial test value (e.g., 5).

Step 3: Create the Table Calculation

Since we want to avoid hardcoding a SQL LIMIT clause that destroys flexibility, we apply the threshold logic at the client level using a Table Calculation.

1. In the Explore data section, click Add calculation.

2- Enter the following boolean expression:

row() <= ${sievo_info.top_n_selected}

This logic evaluates the row index of each returned row. If the current row index is less than or equal to the number entered by the user in the parameter, it returns Yes; otherwise, it returns No.

Consultant Pro-Tip: The row() function processes data that has already been aggregated and returned by your database. Combined with our helper dimension, the trimming process happens almost instantly in the browser without re-running heavy SQL queries.

Step 4: Apply visual filtering and hide helper columns

To keep the final visual clean and executive-ready, we must hide our intermediate helper logic from the visual layer.

1. Click the gear icon on the newly created Table Calculation column (green column) and select “Hide No’s from Visualization”.
2. Click the gear icon on the Top N Selected column and select "Hide from visualization".
3. Save the tile to your dashboard.

Step 5: Map the exclusive filter in the dashboard

The final step guarantees complete isolation of the filter’s behavior within the dashboard.

1. Open the dashboard in edit mode and edit (or add) the dashboard control mapped to the Top N Limite parameter.

2. Map the Top N Limite field only to the target tile (e.g., Category).

3. Make sure all other dashboard tiles are explicitly set to “Don’t filter”.

Conclusion

By implementing this technical pattern, you have elevated your dashboard from a static report to an interactive, enterprise-grade analytical tool.

Key technical takeaways:

• LookML parameters: Inject dynamic user variables without breaking model modularity.
• Table Calculations: Efficiently slice and trim row counts on the client side.
• Tile mapping: Ensures complete isolation of user inputs across dashboard elements.

Mastering post-processing logic alongside LookML turns you from a report builder into a data experience architect, capable of creating clean, performant, and business-first dashboards.

Cheat Sheet

Was this guide helpful?

If you enjoyed this technical guide and want to explore more Looker Enterprise and LookML strategies:

• 👏 Give a few claps on Medium to help this article reach more data professionals.
• 💬 Comment below: How do you handle dynamic row limits in your current Looker projects?
• 🔗 Follow me on Medium and on LinkedIn to never miss out on more tips, tricks, and strategies for SAP Analytics Cloud.

✨You might find this stories interesting:

• 🟦SAC Planning & Scripting: How to clean and load data in your SAC planning test models with a single click.
• 🧹Stop SAC Planning errors before they start: the pro-tip for to avoid imputation errors
• Scripting in SAC: Hide columns with a click and elevate the user experience
• SAC/APP — Create a⚠️Warning for unsaved data
• SAC Planning — Inverse formulas. 🔓Unlock Flexible Planning in SAC with the INVERSE Formula
• SAC/APP — Download excel API with value of an input parameter in the title
• SAC Scripting Tutorial: Building a Dynamic Multi-File Excel Exporter.