tmdl-mastery

What this skill does

# TMDL (Tabular Model Definition Language) Mastery

## Overview

TMDL reference for language syntax, folder structure, object types, expressions, serialization API, CI/CD, and deployment. TMDL is the human-readable, source-control-friendly format for Power BI and Analysis Services semantic models at compatibility level 1200+.

## 2026 Status Snapshot

| Aspect | Status (as of April 2026) |
|--------|---------------------------|
| TMDL language GA | GA since August 2024 -- no longer preview |
| TMDL view in Power BI Desktop | GA -- semantic highlighting, autocomplete, code actions, diff preview, compatibility prompts |
| TMDL as default PBIP semantic-model format | Default -- `model.bim` is the legacy BIM format; new PBIP projects write `definition/*.tmdl` files |
| Fabric Git integration | Exports semantic models as **TMDL** (not TMSL/BIM) |
| TMSL / BIM | **Not deprecated** -- still supported for XMLA scripting commands and tools that require JSON. Use TMDL for source control, TMSL for XMLA `createOrReplace` command scripting |
| Compatibility level | 1550+ recommended; 1601+ required for some newer properties (e.g., `formatStringDefinition`, calculation group multi/empty selection expressions) |
| Tabular Editor 2 (free) | TMDL read/write support (2.17+) |
| Tabular Editor 3 (paid) | Full TMDL IDE with DAX debugger and diagram view |
| VS Code TMDL extensions | Microsoft `analysis-services.TMDL` and community `CPIM.TMDL-language-support` (DAX + M highlighting, code actions, formatting, breadcrumbs) |
| Report Server | **No TMDL support** -- continues to use legacy PBIX binary format |

**Bottom line:** Use TMDL for new Power BI / Fabric semantic models under source control. Retain TMSL for XMLA scripting or tools that require `model.bim`.

## TMDL vs TMSL vs BIM

| Aspect | TMDL (.tmdl folder) | TMSL / BIM (model.bim) |
|--------|---------------------|------------------------|
| Format | YAML-like text, indentation-based | Single JSON file |
| Files | One file per table, role, culture, perspective | One monolithic file |
| Git friendliness | Excellent -- granular diffs, minimal merge conflicts | Poor -- entire model in one diff |
| Human readability | High -- minimal delimiters, DAX/M inline | Low -- escaped JSON strings |
| Tooling | VS Code extension, TMDL view in Desktop, Tabular Editor 3 | Any JSON editor, Tabular Editor 2/3 |
| API | TmdlSerializer (.NET) | JsonSerializer (.NET), TMSL commands |
| Migration | Can convert from BIM via Tabular Editor or Desktop | Default legacy format |

**When to use TMDL:** Any new source-controlled, CI/CD, or team PBIP project.

**When to use TMSL/BIM:** Legacy projects, Report Server (no TMDL support), or tools that only accept BIM.

## Object Declaration Syntax

Declare objects by specifying the TOM object type followed by its name:

```tmdl
model Model
    culture: en-US

table Sales

    measure 'Sales Amount' = SUM(Sales[Amount])
        formatString: $ #,##0

    column 'Product Key'
        dataType: int64
        sourceColumn: ProductKey
        summarizeBy: none
```

**Key rules:**
- Enclose names in single quotes if they contain dot, equals, colon, single quote, or whitespace
- Escape single quotes within names by doubling them: `'My ''Special'' Table'`
- Child objects are implicitly nested under their parent via indentation (no explicit collections)
- Child objects need not be contiguous -- columns and measures can interleave freely

## Property Syntax

Properties use colon delimiter; expressions use equals delimiter:

```tmdl
column Category
    dataType: string          /// colon for non-expression properties
    sortByColumn: 'Cat Order' /// colon for object references
    isHidden                  /// boolean shortcut (true implied)
    isAvailableInMdx: false   /// explicit boolean

measure Total = SUM(Sales[Amount])   /// equals for default expression
    formatString: $ #,##0            /// colon for properties after expression
```

**Text property values:** Leading/trailing double-quotes optional and auto-stripped. Required if value has leading/trailing whitespace. Escape internal double-quotes by doubling them.

## Default Properties by Object Type

| Object Type | Default Property | Language |
|-------------|-----------------|----------|
| measure | Expression | DAX |
| calculatedColumn | Expression | DAX |
| calculationItem | Expression | DAX |
| partition (M) | Expression | M |
| partition (calculated) | Expression | DAX |
| tablePermission | FilterExpression | DAX |
| namedExpression | Expression | M |
| annotation | Value | Text |
| jsonExtendedProperty | Value | JSON |

Default properties use equals (`=`) on the same line or as multi-line expression on the following lines.

## Expressions -- Single-Line and Multi-Line

````tmdl
/// Single-line expression
measure 'Sales Amount' = SUM(Sales[Amount])

/// Multi-line expression (indented one level deeper than parent properties)
measure 'YoY Growth %' =
        VAR CurrentSales = [Sales Amount]
        VAR PYSales = CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Date'[Date]))
        RETURN DIVIDE(CurrentSales - PYSales, PYSales)
    formatString: 0.00%

/// Triple-backtick block for verbatim content (preserves whitespace exactly)
partition 'Sales-Part' = m
    mode: import
    source = ```
        let
            Source = Sql.Database("server", "db"),
            Sales = Source{[Schema="dbo",Item="Sales"]}[Data]
        in
            Sales
        ```
````

**Expression rules:**
- Multi-line expressions indent one level deeper than parent properties
- Trailing blanks are stripped unless using triple-backtick blocks
- Triple-backtick blocks preserve whitespace; end delimiter sets left boundary

## Descriptions (/// Syntax)

```tmdl
/// This table contains all sales transactions
/// Updated daily via incremental refresh
table Sales

    /// Total revenue across all product lines
    measure 'Sales Amount' = SUM(Sales[Amount])
```

Triple-slash comments above an object become its TOM `Description` property. No whitespace between description block and object type keyword.

## Ref Keyword

Reference another TMDL object or define collection ordering:

```tmdl
/// In model.tmdl -- defines table ordering for deterministic roundtrips
model Model
    culture: en-US

ref table Calendar
ref table Sales
ref table Product
ref table Customer

ref culture en-US
ref culture pt-PT

ref role 'Regional Manager'
```

**Rules:** Objects referenced but missing their file are ignored on deserialization. Objects with files but no ref are appended to collection end.

## TMDL Folder Structure

```text
definition/
  database.tmdl           # Database properties (compatibilityLevel, etc.)
  model.tmdl              # Model properties, ref declarations
  relationships.tmdl      # All relationships
  expressions.tmdl        # Shared/named expressions (Power Query parameters)
  functions.tmdl          # DAX user-defined functions
  dataSources.tmdl        # Legacy data sources
  tables/
    Sales.tmdl            # Table + all its columns, measures, partitions, hierarchies
    Product.tmdl
    Calendar.tmdl
  roles/
    RegionalManager.tmdl  # Role definition with permissions and members
    Admin.tmdl
  cultures/
    en-US.tmdl            # Translations for all objects in this culture
    pt-PT.tmdl
  perspectives/
    SalesView.tmdl        # Perspective definition
```

One file per table, role, culture, and perspective. All inner metadata (columns, measures, partitions, hierarchies) lives inside the parent table file.

## TMDL Scripts (createOrReplace)

Apply changes to a live semantic model using the TMDL view in Power BI Desktop:

```tmdl
createOrReplace

    table 'Time Intelligence'
        calculationGroup
            precedence: 1

        calculationItem Current = SELECTEDMEASURE()

        calculationItem YTD =
                CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[Date]))

        calculationItem PY =
                CALCULATE(SELECTEDMEASURE(), S