SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsdax-authoring

dax-authoring

Testing & quality

View repository

Updated 10-16 06:47

About this skill

DAX Authoring

Table of Contents

Task Reference Notes
Must/Prefer/Avoid SKILL.md: Must/Prefer/Avoid Guardrails for DAX query generation
Generating DAX Queries SKILL.md: Generating DAX Queries Core rules, inline examples, EVALUATE/DEFINE patterns
DAX Query Structure & Syntax dax-query-patterns.md: Query Structure DEFINE / EVALUATE / ORDER BY / START AT
DAX Query Key Components dax-query-patterns.md: Key Components DEFINE VAR, DEFINE MEASURE, table expressions
DAX Query Worked Examples dax-query-patterns.md: Common Patterns 11 annotated examples from simple aggregation to cross-table joins
DAX Query Anti-Patterns dax-query-patterns.md: Anti-Patterns What to avoid in DAX queries
CALCULATE & CALCULATETABLE dax-core-reference.md: CALCULATE & CALCULATETABLE Context transition, filter types, boolean restrictions, common patterns
SUMMARIZECOLUMNS dax-core-reference.md: SUMMARIZECOLUMNS Argument order, auto-blank elimination, filter args, vs SUMMARIZE
ALL & ALLEXCEPT dax-core-reference.md: ALL & ALLEXCEPT CALCULATE modifier vs table function; percentage patterns
TREATAS dax-core-reference.md: TREATAS Virtual relationships, multi-column filtering
DAX Syntax Rules dax-core-reference.md: DAX Syntax Rules EVALUATE, CALCULATE, naming, SQL keywords, DEFINE rules
Common Mistakes dax-core-reference.md: Common Mistakes Variable naming, quoting, escaping, scalar EVALUATE, multi-table SUMMARIZECOLUMNS
BLANK Semantics dax-core-reference.md: BLANK Semantics BLANK vs NULL, propagation, equality, ISBLANK, DIVIDE, non-empty semantics
Time Intelligence Patterns SKILL.md: Time Intelligence When to consult TI reference
Date Table Prerequisites dax-time-intelligence.md: Prerequisites Date table requirements, mark as date table
YTD / QTD / MTD dax-time-intelligence.md: Period-to-Date TOTALYTD, DATESYTD, DATESINPERIOD patterns
Year-over-Year / Period Comparisons dax-time-intelligence.md: Period Comparisons SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD
Rolling Windows dax-time-intelligence.md: Rolling Windows DATESINPERIOD rolling 12-month patterns
Opening/Closing Balances dax-time-intelligence.md: Balances Semi-additive measures, LASTDATE, LASTNONBLANK
TI in DAX Queries (Critical Rules) dax-time-intelligence.md: Critical Rules for TI in Queries CALCULATETABLE + TREATAS pattern for query context
TI Common Mistakes dax-time-intelligence.md: Common Mistakes Missing date table, wrong granularity, fiscal calendar
Testing & Iteration SKILL.md: Testing & Iteration Execute → inspect → fix → re-test workflow

Must / Prefer / Avoid

Must

  • Always test generated DAX via npx fabric-app-data query <alias> --query '<DAX>' before using in app code
  • Use fully-qualified 'Table'[Column] for column references
  • Use simple [Measure] for measure references
  • Use DEFINE for VAR and local MEASURE declarations (single DEFINE block, no commas)
  • Prefer existing model measures over re-aggregating raw data

Prefer

  • SUMMARIZECOLUMNS as the primary grouping function for queries
  • TREATAS for filter arguments in SUMMARIZECOLUMNS
  • Variables (VAR) to improve readability and avoid repeated calculations

Avoid

  • SQL keywords (SELECT, WHERE, HAVING, etc.) within DAX expressions
  • EVALUATE with scalar functions directly (wrap in ROW or table function)

Generating DAX Queries

DAX Syntax Rules

  • Measures are named objects in the semantic model. They specify how to aggregate data. Measures should be used first to answer user requests before a new DAX formula is used to aggregate data.
  • EVALUATE Statement: Not a function but a statement. It must always precede a table expression. Avoid pairing EVALUATE with scalar functions like CONCATENATEX, SUMX, DISTINCTCOUNT, etc.
  • Use the fully-qualified name 'Table'[Column] for column references, and the simple name [Measure] for measure references.
  • CALCULATE takes a scalar expression as its first argument. CALCULATETABLE takes a table expression as its first argument.
  • SUMMARIZECOLUMNS requires a specific order: groupby columns, then filters, then aggregations/measures.
  • Do not use SUMMARIZECOLUMNS when there is no aggregation and the groupby columns belong to more than one table; use VALUES, SUMMARIZE, or SELECTCOLUMNS instead.
  • When using SELECTCOLUMNS or CALCULATETABLE, include any columns needed downstream (ORDER BY, FILTER).
  • Filters propagate across relationships based on unidirectional or bidirectional settings.
  • INTERSECT, UNION, EXCEPT require identical column counts in both inputs.
  • For current date/time, use TODAY() or NOW().

Inline Examples

Simple filtered aggregation:

// Total sales for red products
EVALUATE
  ROW("Total Sales Amount", CALCULATE([Total Amount], 'Product'[Color] == "Red"))

Multi-filter grouping with SUMMARIZECOLUMNS:

DEFINE
  VAR _Filter1 = TREATAS({"Consumer Electronics"}, 'Product'[Category])
  VAR _Filter2 = FILTER(ALL('Calendar'[Year]), 'Calendar'[Year] >= 2022 && 'Calendar'[Year] <= 2023)

EVALUATE
  SUMMARIZECOLUMNS(
    'Calendar'[Year],
    'Calendar'[Month],
    _Filter1,
    _Filter2,
    "Total Quantity", SUM('Sales'[Order Quantity]),
    "Discount", [Total Discount]
  )

TopN with filtering:

DEFINE
  VAR _Filter = TREATAS({"Red", "Black"}, 'Product'[Color])
  VAR _Core = SUMMARIZECOLUMNS('Product'[Name], _Filter, "Total Sales", [Total Amount])

EVALUATE
  TOPN(10, _Core, [Total Sales], DESC)

For full syntax reference, worked examples, and anti-patterns, see dax-query-patterns.md. For function details, see dax-core-reference.md.

Time Intelligence

Time intelligence functions enable period-based analysis (YTD, YoY, rolling windows, etc.). They require a properly configured Date table.

Consult dax-time-intelligence.md whenever the user's request involves:

  • Period-to-date calculations (YTD, QTD, MTD)
  • Period comparisons (Year-over-Year, Month-over-Month)
  • Rolling windows (last 12 months, last 30 days)
  • Opening/closing balances
  • Custom date ranges

Testing & Iteration

  1. Generate the DAX query expression
  2. Execute via npx fabric-app-data query <alias> --query '<DAX>'
  3. Inspect results: check column names, data types, row counts, and actual data values
  4. If error: consult dax-core-reference.md, fix, and re-test
  5. Iterate until the query returns expected results

Query Execution

Use npx fabric-app-data query <alias> --query '<DAX>' to run queries. This uses the same SDK pipeline as the running app, so results are identical to what the app produces at runtime. To re-test an existing .dax file without copying the query text, use --file: npx fabric-app-data query <alias> --file src/queries/revenue.dax. For full CLI options (profiles, result limits), see the fabric-cli skill.

Result trimming: The CLI returns at most 1000 rows by default. When the result is trimmed, the output includes a _cliWarning field (e.g., "Result trimmed to first 1000 of 5000 rows"). This is a CLI-only limitation — the full dataset is available in the running app. If you need to see more data, refine your DAX with filters or aggregations.

Troubleshooting

Problem Solution
CLI query fails with "not signed in" Run az login to sign in to Azure CLI
CLI query fails with "Azure CLI is not installed" Install from https://aka.ms/install-azure-cli
CLI query fails with "alias not found" Run npx fabric-app-data list to check available aliases, then npx fabric-app-data add to register
DAX syntax errors Consult dax-core-reference.md — check reserved keywords, quoting rules, EVALUATE/scalar mistakes
Unexpected query results Check filter context, relationship direction, BLANK handling in dax-core-reference.md
Time intelligence returns wrong values Check date table prerequisites and critical rules in dax-time-intelligence.md