dax-authoring

DAX Authoring

Table of Contents

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