Skip to main content

Show all dimension values (including empty)

Introduction​

Missing data in reports is a common issue. Sometimes you need to see all possible values in your dimensions, even when there's no associated data. For example:

  • Show all users, including those who made no orders yet
  • Display all dates in a time series, even days with no sales

This guide covers two main scenarios and how to handle each one.

Use Case 1: Show All Users (Even with Empty Orders)​

The Problem​

You want a table showing order counts by user, but users who haven't placed any orders are missing from the results.

report with missing users

Why This Happens​

Let's say we have a dataset ecommerce like this

ecommerce.dataset.aml
Dataset ecommerce {
    ...
    models: [orders, users, date_dim]
    relationships: [
        relationship(orders.user_id > users.id, true),
        relationship(orders.created_date > date_dim.date_key)
    ]
}

When Holistics explores data with metrics from the many-side of a relationship (like orders), it performs a LEFT JOIN from many to one. This means:

  • Start from orders (many side) → LEFT JOIN to users (one side)
  • Only users who have orders will appear in results
  • Users with no orders are excluded because they don't exist in the starting table
Diagram showing that a join starting from the 'orders' model will exclude users who have no corresponding orders

For more details, visit our doc about How JOINS are constructed

High-Level Approach​

Force Holistics to include all users by adding a metric from the users model (for e.g., total users). When a model has a metric, Holistics ensures all its rows appear in results.

explore {
    dimensions {
        users.full_name
    }
    measures {
        count_orders: count(orders.id),    // Your original metric
        total_users: count(users.id)       // Forces all users to appear
    }
}

Hide the Helper Metric​

Since you don't want to show "Total Users" in your report, simply hide the column:

Click the column header → Hide Column. Your table now shows all users (including those with no orders) but keeps the helper metric hidden.

For more details, see Show/hide columns

What if you want to apply filters?​

Sometimes you'll need to filter your data - for instance, you might only want to count delivered orders (where orders.status == 'delivered').

Here's the catch: when you apply this filter, users without any orders disappear from your report again. Why? The filter travels from the orders model to the users model. Since some users don't have any delivered orders (or any orders at all), they get filtered out completely.

explore {
    dimensions {
        users.full_name
    }
    measures {
        count_orders: count(orders.id),    // Your original metric
        total_users: count(users.id)       // Forces all users to appear
    }
    filters {
        orders.status == "delivered"
    } 
}

The solution is to modify your helper metric so it stays isolated at the users level and isn't affected by filters on other models like orders. You do this by adding keep_grains(users) to your metric definition.

explore {
    dimensions {
        users.full_name
    }
    measures {
        count_orders: count(orders.id),    
        total_users: count(users.id) | keep_grains(users)
    }
    filters {
        orders.status == "delivered"
    } 
}

For more details, see the keep_grains documentation.

By keeping the metric at the users grain, filters on other models won't affect which users appear in your results. Users with no delivered orders will still show up with a count of zero.

This same principle applies to Use Case 2 below when working with date dimensions and filtered data.

Use Case 2: Running Totals Across All Dates​

The Problem​

You want to create a running total chart by date, but missing dates create awkward jumps in your line chart instead of smooth continuity.

A line chart showing a running total with awkward gaps for dates that have no sales data.

High-Level Approach​

Same principle as Use Case 1, but applied to dates: add a metric from the date dimension to force all dates to appear.

ecommerce.dataset.aml
Dataset ecommerce {
    ...
    metrics running_total_orders {
        label: 'Running Total Orders'
        type: 'number'
        definition: @aql window_sum(orders.total_orders, order: 'x_axis') ;;
    }

    metrics total_dates {
        label: 'Total Dates'
        type: 'number'
        definition: @aql count(date_dim.date_key);;
    }

}
explore {
    dimensions {
        date_dim.date_key
    }
    measures {
        running_total_orders: running_total_orders,
        total_dates: total_dates    // Forces all dates to appear
    }
}

Hide the Helper Metric​

Since charts work differently than tables, you have several options to hide the helper metric:

Option 1: Use tooltips​

Add the helper metric to tooltips instead of the main visualization:

This keeps your chart clean while ensuring all dates appear.

Option 2: The mathematical trick​

Combine the helper with your main metric to make it invisible:

measure running_total_all_dates {
    type: "number" 
    definition: @aql 
        running_total(count(orders.id)) + count(date_dim.date_key) - count(date_dim.date_key)
    ;;
}

This adds and subtracts the same value, forcing inclusion without changing results.

Option 3: Use conditions​

Add the helper metric to visualization conditions instead of displaying it:

Set the condition: count(date_dim.date_key) > -1

Since count is always 0 or positive, this condition is always true and forces all dates to appear without showing the metric.

When NOT to Use This​

  • Large dimension tables: Showing all 1 million users might affect the performance. Consider filtering first (e.g., users from last 3 months)
  • Irrelevant dimensions: Not every report needs every possible value
Let us know what you think about this document :)