accelerate-data/authoring-dax-measures

Fabric DAX Patterns

Comprehensive DAX pattern library for Microsoft Fabric semantic model measures. All patterns use TMDL syntax and follow Fabric best practices.

General DAX Best Practices

1. VAR/RETURN: Use for any measure with more than one expression. Improves readability, debuggability, and avoids re-evaluation.
2. Fully qualified columns: Always use 'Table'[Column] for column references.
3. Unqualified measures: Always use [Measure Name] for measure references (no table prefix).
4. DIVIDE over /: Use DIVIDE(numerator, denominator) to handle division by zero gracefully.
5. CALCULATE explicitly: Make filter context modifications explicit with CALCULATE.
6. Avoid FILTER on large tables: Use CALCULATE('Table'[Column] = value) instead of CALCULATE(FILTER('Table', ...)) when possible.
7. ISBLANK checks: Return BLANK() instead of 0 when there's no data — this prevents misleading visuals.
8. Format strings: Always set formatString on every measure.
9. Display folders: Group related measures for discoverability.
10. Descriptions: Add /// descriptions to every measure.

Pattern Categories

Time Intelligence

Requires a proper date table with an isKey date column and continuous date range with no gaps.

Core time intelligence measures:

• Year-to-Date (YTD)
• Quarter-to-Date (QTD)
• Month-to-Date (MTD)
• Same Period Last Year (PY)
• Year-over-Year change (YoY, YoY %)
• Moving averages
• Rolling periods

See references/time-intelligence.md for complete patterns.

Semi-Additive

For measures that should not be summed across time (e.g., account balances, inventory levels, headcount).

See references/semi-additive.md for patterns.

Parent-Child Hierarchies

For ragged/unbalanced hierarchies like chart of accounts or organizational structures.

See references/parent-child.md for patterns.

Common KPIs and Business Measures

Standard business calculations: ratios, rankings, segmentation, cumulative totals, new/returning customer tracking, budget allocation.

See references/common-kpis.md for patterns.

Quick Reference: Format Strings

| Type | Format String | Example Output | |------|-------------|---------------| | Currency (USD) | $ #,##0.00 | $ 1,234.56 | | Currency (no decimals) | $ #,##0 | $ 1,235 | | Percentage | 0.00% | 12.34% | | Percentage (no decimals) | 0% | 12% | | Integer | #,##0 | 1,235 | | Decimal | #,##0.00 | 1,234.56 | | Large numbers | #,##0,,M | 1M |

Adding Measures to an Existing Model

When the user wants to add DAX measures to a TMDL semantic model, follow this workflow:

Step 1: Gather context (dbt-first)

1. Scan the working directory for a dbt project (dbt_project.yml). If found, read the schema YAML files under models/marts/ to understand available tables, columns, data types, and descriptions. This context helps generate accurate column references and appropriate measure logic.
2. Then locate the TMDL model: search for model.tmdl or database.tmdl. Read all table files from tables/ and relationships.tmdl.
3. Cross-reference: dbt column descriptions clarify business meaning, which helps write better measure descriptions and choose the right aggregation logic.

Step 2: Parse the request

The user may provide a natural language description ("year over year sales growth"), a pattern name ("time intelligence", "semi-additive"), or a specific measure definition. Match it to the appropriate pattern from the reference files.

Step 3: Identify the target table

Measures on facts go in the fact table file. If a dedicated measures table exists, use that. Use dbt model names (with fct_/dim_ prefix stripped) to identify the correct TMDL table.

Step 4: Generate the measure in TMDL

/// {Description of what this measure calculates}
measure '{Measure Name}' =
		{DAX expression using VAR/RETURN for complex logic}
	formatString: {appropriate format}
	displayFolder: {logical folder grouping}

Step 5: Handle time intelligence

If the measure involves time: verify a date table exists with isKey on the date column, use CALCULATE + time intelligence functions, and generate the full family of related measures (base, YTD, PY, YoY, YoY %).

Step 6: Write to the TMDL file

Read the target table's .tmdl file, append the new measure(s) after existing measures, maintain proper TMDL indentation (single tab).

Additional Resources

• references/time-intelligence.md — Complete time intelligence pattern library
• references/semi-additive.md — Balance sheet, inventory, and snapshot patterns
• references/parent-child.md — Chart of accounts, org hierarchy patterns
• references/common-kpis.md — Rankings, segmentation, cumulative, new/returning, budget, currency