authoring-dax-measures

name	authoring-dax-measures
description	Use when the user asks to "write a DAX measure", "create DAX calculations", "add time intelligence", "calculate YTD", "year over year", "running total", "semi-additive measure", "parent-child hierarchy", "ABC classification", "new and returning customers", "dynamic segmentation", "currency conversion", "ranking", "cumulative total", "budget allocation", or any DAX formula authoring for a Microsoft Fabric or Power BI semantic model. This skill provides DAX patterns specifically formatted as TMDL for Fabric semantic models.
version	0.1.0

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