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

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