SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsquery-design

query-design

Data & analytics

View repository

Updated 10-16 06:47

About this skill

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)
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

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 — 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 two grains (e.g., bars + grand-total reference line, region detail + total row, monthly trend + YTD).
  • Filter strategy — 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 — 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 exploration; discover tables, columns, 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.