A 45-Second Dashboard and a Filter That Does Not Reach

Rank by cost driver, not by familiarity

1. Marks count first - 300,000 marks is the dominant cost. Query time is usually the smaller half of a slow dashboard; rendering is the larger. Aggregate before plotting: replace the line-item-level view with SUM(Line Amount) at the grain the chart actually needs (day, store, or region). If a detail table genuinely needs row-level data, move it to a separate drill-down tab or a "view underlying data" affordance. Measure: marks count on the dashboard (Performance Recorder shows the render phase), and time to interactive.

2. Extract and filter design second. Confirm the Region context filter is a context filter, and that it sits before any top-N or table calculation - otherwise it is doing nothing useful. Add a data source filter for data the workbook never shows (e.g. exclude cancelled orders, restrict to the last 24 months) so the extract is smaller, not just the query. Measure: extract row count and query duration in Performance Recorder.

3. Then the slow calculations. Look for row-level calculated fields, string manipulation, and blending in a calculated field. Push stable logic into the extract; move row-level calcs to aggregate calcs where semantics allow. Measure: compare execution time of the affected worksheet with the calculation removed.

4. Then layout. Six worksheets on one tab means six queries on every interaction. Consolidate widgets that share a query; avoid sheet-swapping containers that force hidden sheets to execute.

Discipline: record a baseline with the Performance Recorder before touching anything, change one thing at a time, and re-record. Otherwise you will "optimise" for a week and not know which change bought the 30 seconds.

The 45 seconds may be mostly the first load - a cold extract query. Check whether subsequent interactions are also slow before committing to a rendering fix.

Why

Two mechanisms stack here:

• Blending. A filter on Orders restricts the primary query only. The secondary (Quota) is filtered solely by the link dimensions present in the view. Order Date does not exist in the quota file, so there is nothing to push down - attainment keeps its full denominator while the numerator shrinks, which is exactly the behaviour observed.
• Filter scope. "All Using This Data Source" applies to all worksheets on the same data source. Any worksheet built on the secondary source, or on a blend, is outside that scope.

Options

1. Rename the KPI honestly. If attainment is month-to-date plan vs month-to-date actuals, a user-driven date range may not belong on it at all. Label it "MTD attainment (full period)" and fix the expectation. Cheapest, and sometimes correct - but it will be read as a bug until the label is explicit.
2. Move the filter to the blend's shared dimension. Add the date to the quota source (quota period) and filter both sources on a common period field. Works, but requires quota to carry a period at the same grain as the date filter.
3. Join instead of blend. A physical join on Rep ID plus a period field lets a single filter reach both, and the aggregation is computed once. This is the durable fix, and it also resolves the intermittent 0% in (c).
4. Parameter-driven period. Expose a period parameter used in both a calculated field on Orders and one on Quota. Full control, but the filter no longer appears as a native quick filter and must be maintained.

Recommendation: option 3 if the workbook is long-lived; option 1 with an explicit label as an immediate fix so the 250 users stop filing the same ticket.

Diagnosis - three candidate mechanisms

1. Refresh schedule mismatch. Orders refreshes nightly; quota refreshes monthly on a different schedule (or manually). If a region's quota rows land after the Orders extract refreshed, the current view has actuals with no matching denominator for a window. 0% is what a missing denominator looks like when a calculation is wrapped defensively (IFNULL returning 0, or a ZN() on the quota side).
2. Inner join dropping rows. If Orders is joined to Quota and the join is inner, regions with no quota row vanish entirely - they do not show 0%, they disappear. If they show 0%, the join is a left join and the NULL quota is being coerced. Check the join type before anything else; it determines which bug you have.
3. Period misalignment. Quota stored as a month label, Orders as a date, matched through a date dimension that did not include the late-loaded month. The rows exist but never match.

Verification: pull a region known to be broken, run the quota source alone for that region and period, and confirm the rows exist. Then run the attainment worksheet unfiltered. If the region appears with a blank quota, it is a join/coercion issue; if the quota rows are absent from the source, it is a load-order issue.

Governance changes

• Load quota before Orders, or (better) put both behind one scheduled flow so ordering is guaranteed. Two independent schedules with a shared key is a standing invitation to this bug.
• Fail loudly. Add a validation step that compares the count of distinct regions in quota against the master region list and blocks the refresh if any are missing. A refresh that publishes wrong numbers is worse than one that fails.
• Never silently coerce. Replace IFNULL([Quota], 0) with an explicit "no plan loaded" state so the dashboard shows an amber gap rather than a confident 0%.
• Certify the source. Publish the joined Orders+Quota model as a certified data source with a documented refresh contract, so 250 users stop consuming whatever version happens to have run last.