query-design

# Query Design — Separation of Data and Presentation **DAX computes and fetches data. TypeScript shapes it for display. VegaVisual and DataGrid render it.** Aggregate in DAX to the visual's grain — never fetch lower-grain rows to roll up client-side. Once at the visual's grain, TypeScript can derive simple totals (SUM, COUNT, MIN, MAX) from the already-fetched detail rows. When a visual layout changes, only the TypeScript or spec layer should change — not the DAX query. ## Responsibility Matrix | Concern | Owner | |---|---| | Semantic measures (SUM, DISTINCTCOUNT, etc.) | DAX | | Filters and slicers | DAX or TypeScript ([see Filter Strategy](references/filter-strategy.md)) | | Grouping grain (SUMMARIZECOLUMNS) | DAX | | Time intelligence (YTD, YoY) | DAX | | TopN / payload reduction | DAX | | Deterministic row ordering (ORDER BY) | DAX (for debugging — not presentation sort) | | Merging multiple result sets | TypeScript | | Totals derivable from detail rows (SUM, COUNT, MIN, MAX) | TypeScript (roll up from already-fetched detail) | | Totals NOT derivable from detail (DISTINCTCOUNT, ratios, AVERAGEX, complex measures) | DAX (separate summary query) | | Filling dimension gaps | TypeScript (stitch dimension list into sparse results) | | Reshaping (pivot, unpivot) | TypeScript | | Column display names | `columnMetadata` in factory file | | Number/date formatting | `columnMetadata.format` / Vega-Lite spec | | User-facing sort order | TypeScript / Vega-Lite `sort` / DataGrid `sort` | | Decorative labels, icons | DataGrid `cellRenderer` or Vega-Lite condition | | Axis titles, legends, color encoding | Vega-Lite spec | ## Rules ### Must - Aggregate in DAX to the visual's grain — never fetch lower-grain rows just to roll them up to that grain in TypeScript - One grain per `.dax` file (one `EVALUATE` per file); separate grains → separate files + separate `useSemanticModelQuery` calls - `ORDER BY` in DAX for stable, diffable results — not presentation sort - Same filters/measures across related split-grain queries to prevent drift - For totals from already-fetched detail rows: if the rollup is safe (SUM, COUNT, MIN, MAX), compute in TypeScript; otherwise (DISTINCTCOUNT, ratios, AVERAGEX, complex measures) issue a separate DAX summary query ### Prefer - `SUMMARIZECOLUMNS` for grouped aggregation — it also drops BLANK-measure rows, keeping payloads small - DAX's natural column names (`'Table'[Column]`, `[Measure]`) mapped via `columnMetadata.displayName` - Raw typed values from DAX — format via `columnMetadata.format` or Vega-Lite, never `FORMAT()` - Model-defined format strings (from `INFO.VIEW.MEASURES()`) over invented ones - Multiple lightweight queries over one monolithic query - User-facing sort in TypeScript / Vega-Lite / DataGrid — never re-query for sort ### Avoid - `SELECTCOLUMNS` solely for renaming — use `columnMetadata.displayName` instead - `UNION` to mix different grains (detail + total) — use separate queries - `FORMAT()` in DAX — converts to text, breaks sorting and charting - Converting BLANK to `0` / `""` / `"N/A"` in DAX — causes result-set explosion - `CONCATENATEX`, `UNICHAR`, emoji prefixes — decorative text belongs in `cellRenderer` or Vega-Lite - Fetching all members of high-cardinality dimensions just to fill gaps ## Decision Flowchart ```text Need to add something to the query result? |-- Calculation / aggregation / filter? | -> DAX (measures, CALCULATE, SUMMARIZECOLUMNS) |-- Interactive filter the user controls? | -> Low-cardinality: widen grain, filter in TypeScript or Vega-Lite transform | -> High-cardinality: push filter to DAX, re-query |-- Merging datasets or adding synthetic rows? | -> Charts: pass multiple DataTables to VegaVisual, layer in spec | -> Grids: append rows in TypeScript, style via cellRenderer |-- Renaming a column for display? | -> columnMetadata in the factory file (displayName) |-- Formatting, labeling, or encoding? | -> Vega-Lite spec or DataGrid cellRenderer |-- Decorating values (icons, status badges, null placeholders)? | -> DataGrid cellRenderer or Vega-Lite condition encoding |-- Not sure? -> Does it change what the data *means* (filter, measure, grain)? -> DAX Does it change only how data is *rendered* (labels, icons, layout)? -> TypeScript / Vega-Lite spec / DataGrid cellRenderer Still unclear? -> Read the relevant reference above ``` ## Reference Materials Read these when working on a specific topic: - **[Anti-patterns and corrections](references/anti-patterns.md)** — Open when reviewing a query that uses `UNION` for totals, `FORMAT()`, `SELECTCOLUMNS` for renaming, `CONCATENATEX`/emoji decoration, BLANK-to-`0` conversion, or `GENERATE`/`CROSSJOIN` for gap-filling. - **[Multi-grain patterns](references/multi-grain-patterns.md)** — Open when a single visualization needs data at two grains (e.g., bars + grand-total reference line, region detail + total row, monthly trend + YTD). - **[Filter strategy](references/filter-strategy.md)** — Open when adding a user-controlled filter and deciding whether to widen the grain (filter client-side) or push the filter into DAX (re-query on each change). - **[Format strings](references/format-strings.md)** — Open when picking a `columnMetadata.format` value, when a measure has a dynamic format string, or when formatting needs to flow into a Vega-Lite axis. ## Integration with Sibling Skills - **[schema-discovery](../schema-discovery/SKILL.md)** — Schema exploration; discover tables, columns, and relationships before writing queries. - **[dax-authoring](../dax-authoring/SKILL.md)** — DAX syntax, query patterns, and testing workflow. Apply this skill's principles when deciding what DAX should compute. - **[visuals](../visuals/SKILL.md)** — Vega-Lite specs and DataGrid configuration. Push formatting and labels into specs, not DAX.