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. When a visual layout changes, only the TypeScript or spec layer should change — not the DAX query.

Responsibility Matrix

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 EVALUATE per .dax file; use ROLLUPADDISSUBTOTAL when DataGrid body and grand-total grains share one result table
• ORDER BY in DAX for stable, diffable results — not presentation sort
• Same filters/measures across related split-grain queries to prevent drift

Prefer

• SUMMARIZECOLUMNS for grouped aggregation — it also drops BLANK-measure rows, keeping payloads small
• DAX's natural column names ('Table'[Column], [Measure]) as columnMetadata keys, mapped to a bracket-free name and a human-readable 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 for independently shaped datasets; one flagged rollup query for a DataGrid body and grand total
• User-facing sort in TypeScript / Vega-Lite / DataGrid — never re-query for sort

Avoid

• SELECTCOLUMNS solely for renaming — use columnMetadata (name + displayName) instead
• Reusing the raw DAX column name as columnMetadata.name — [ ] . make the visual read undefined
• UNION to mix body and total grains — use ROLLUPADDISSUBTOTAL and partition its flagged rows
• 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

Need to add something to the query result?
  |-- Calculation / aggregation / fixed scope filter?
  |     -> DAX (measures, CALCULATE, SUMMARIZECOLUMNS)
  |-- Interactive filter the user controls?
  |     -> Use the visual-interactivity skill
  |-- Adding a DataGrid grand total?
  |     -> DAX rollup query: split body and total rows with toRollupDataTables
  |        and always pass the returned grandTotalTable via grandTotals.data
  |     -> DataGrid-computed sum/count: pass grandTotals without data and set defaultAggregation
  |-- Merging datasets or adding other 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 — 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 — Open when a single visualization needs data at multiple grains (e.g., bars + reference line, region detail + total row, monthly trend + YTD), to choose between one flagged rollup result and separate queries while keeping related factories, hooks, and DataTables aligned.
• Format strings — 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-query — Query exact tables, columns, measures, and relationships before writing queries.
• dax-authoring — DAX syntax, query patterns, and testing workflow. Apply this skill's principles when deciding what DAX should compute.
• visuals — Vega-Lite specs and DataGrid configuration. Push formatting and labels into specs, not DAX.
• visual-interactivity — Visual selections, cross-filtering, cross-highlighting, filter-aware grid totals, and runtime query parameters.