Filtered Distinct Counts with LookML Measures

What LookML Measures and Substitution Operators Are

In LookML, a measure is a field that computes an aggregate calculation across multiple rows of data, such as counts, sums, averages, or distinct counts. A dimension represents a row-level attribute or fact. When building data models, LookML uses the substitution operator (${...}) to reference database columns or other LookML fields in a modular, maintainable hierarchy.

How it Meets the Business Requirements

• Aggregation requirement (✓): Setting type: count_distinct directs Looker to generate the appropriate SQL aggregation (such as COUNT(DISTINCT ...)) across rows.
• Date-scoped filtering (✓): The filters parameter applies a conditional filter directly to the calculation (e.g., filters: [created_date: "30 days"]), ensuring only qualifying rows are aggregated.
• LookML maintainability (✓): Referencing ${user_id} instead of ${TABLE}.user_id ensures that if the underlying database column name ever changes in the future, developers only need to update the base dimension's sql parameter in one location.

Key Advantages and Capabilities

• Single point of truth: Using ${field_name} creates a single reference point for the physical column in the data model.
• Automatic SQL generation: Looker translates type: count_distinct into dialect-specific SQL functions without manual SQL string manipulation.
• Reusable business logic: End users querying the Explore can select this measure to see the 30-day active customer count across any slice or dimension without writing custom filters.
• Consistency across reports: Predefined filtered measures enforce uniform business definitions across dashboards and Explores.

Why This is Optimal

Declaring an aggregate metric as a measure and referencing the base dimension with ${field_name} follows core LookML modeling standards. It separates row-level dimensions from aggregated metrics while keeping the code maintainable and DRY (Don't Repeat Yourself).