tmdl

# TMDL Authoring Expert guidance for authoring and editing TMDL (Tabular Model Definition Language) files directly in PBIP projects. > **This skill is a last resort.** Direct TMDL file editing lacks the validation, atomicity, and DAX query capabilities of the Tabular Editor CLI, Power BI MCP server, or the `connect-pbid` skill (TOM via PowerShell). Use those tools when available. TMDL editing is appropriate when: > > - Working with PBIP files in a Git repo without Power BI Desktop open > - No Tabular Editor CLI or MCP server is installed > - Making quick text-level fixes (descriptions, format strings, display folders) where a full tool chain is overkill > > Direct TMDL editing does not validate DAX syntax, check referential integrity, or verify that property values are valid. Errors will only surface when the model is next loaded in Power BI Desktop or deployed via XMLA. Use the **`pbip-validator`** agent to check TMDL files for syntax issues, indentation errors, and referential integrity before opening in PBI Desktop. ## When to Use This Skill Activate only when the Tabular Editor CLI, Power BI MCP server, or `connect-pbid` skill are not available, and tasks involve: - Editing `.tmdl` files directly (measures, columns, tables, relationships) - Adding or modifying measure definitions in TMDL - Adding descriptions to columns, measures, or tables - Fixing `summarizeBy` or `formatString` values - Understanding TMDL syntax rules (indentation, quoting, property ordering) - Writing multi-line DAX in TMDL format - Understanding the difference between `///` descriptions and `//` comments ## Critical - **`///` (triple-slash) sets the `Description` property** on the object that immediately follows it. A `///` line must be immediately followed by a declaration (`measure`, `column`, `table`, etc.); never by a blank line or another `///`. Use `//` for regular comments. - **Indentation is semantic.** TMDL uses whitespace indentation where depth equals nesting level ([TMDL spec — Indentation](https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-overview#indentation)). PBIP files use a single tab per level because Power BI Desktop and the TOM `TmdlSerializer` default to `IndentationMode.Tabs`. Spaces are also valid (`IndentationMode.Spaces`, default 4 per level), but be consistent within a file; mixed or incorrect indentation will break the model. Properties of a table are indented one level; properties of a column (which belongs to a table) are indented two levels. - **Name quoting rules:** Only quote names that contain spaces, special characters, or start with a digit. Simple names and underscore-prefixed names are unquoted. See the Name Quoting section for details. - **M expressions and tables share a namespace.** A name declared by `expression <name>` in `expressions.tmdl` and a name declared by `table <name>` in `tables/*.tmdl` collide; Power BI Desktop fails the load with `'duplicate member <name>'`. Pick distinct names; the conventional fix is to suffix the M expression with ` Query` or ` Source` and have partitions reference it via `source = #"<Name> Query"`. `validate_pbip.py` enforces this as an ERROR. ## TMDL File Types | File | Contents | Location | |------|----------|----------| | `model.tmdl` | Model configuration, `ref table` entries, query groups, annotations | `definition/` | | `database.tmdl` | Compatibility level, model ID | `definition/` | | `relationships.tmdl` | All relationships between tables | `definition/` | | `expressions.tmdl` | Shared M expressions and parameters | `definition/` | | `functions.tmdl` | DAX user-defined functions (reusable parameterized DAX) | `definition/` | | `roles/<RoleName>.tmdl` | One file per security role (RLS filters, role members, OLS) | `definition/roles/` | | `perspectives/<Name>.tmdl` | One file per perspective (object membership) | `definition/perspectives/` | | `dataSources.tmdl` | Legacy data source definitions (if present) | `definition/` | | `tables/<Name>.tmdl` | Table definition with columns, measures, hierarchies, partitions | `definition/tables/` | | `cultures/<locale>.tmdl` | Linguistic metadata and translations | `definition/cultures/` | ## Object Nesting Rules Objects must be nested inside their correct parent. The validator enforces these rules: | Object | Allowed Parent(s) | |--------|-------------------| | `column`, `measure`, `hierarchy`, `partition`, `calculationGroup` | `table` | | `level` | `hierarchy` | | `calculationItem` | `calculationGroup` | | `tablePermission` | `role` | | `columnPermission` | `tablePermission` | | `perspectiveTable` | `perspective` | | `perspectiveColumn`, `perspectiveMeasure`, `perspectiveHierarchy` | `perspectiveTable` | | `linguisticMetadata`, `translation` | `cultureInfo` | | `dataAccessOptions` | `model` | | `formatStringDefinition` | `measure`, `calculationItem` | | `detailRowsDefinition` | `measure`, `table` | | `alternateOf` | `column` | | `member` | `role` | | `annotation`, `extendedProperty` | any object (including `queryGroup`, `function`, `member`) | | `ref` | `model`, `table` | Root-level objects (indent 0 only): `model`, `database`, `table`, `relationship`, `role`, `cultureInfo`, `perspective`, `dataSource`, `expression`, `queryGroup`, `function`. ## Syntax Rules ### Indentation TMDL uses **whitespace indentation** where depth equals nesting level. PBIP files use a single tab per level (the TOM `TmdlSerializer` default), so all examples below use tabs: ```tmdl table Product // depth 0: top-level declaration lineageTag: abc-123 // depth 1: table property measure '# Products' = // depth 1: measure declaration COUNTROWS ( // depth 3: DAX expression body (one deeper than properties) VALUES ( Product[Name] ) // depth 3: continued ) // depth 3: continued formatString: #,##0 // depth 2: measure property displayFolder: Measures // depth 2: measure property lineageTag: def-456 // depth 2: measure property column 'Product Name' // depth 1: column declaration dataType: string // depth 2: column property lineageTag: ghi-789 // depth 2: column property summarizeBy: none // depth 2: column property sourceColumn: Product Name // depth 2: column property annotation SummarizationSetBy = Automatic // depth 2: column annotation ``` **Key rules:** - Use tabs in PBIP files (Power BI Desktop's default); see the Critical section above for the spaces alternative - Table-level objects (columns, measures, hierarchies, partitions) are at depth 1 - Properties of those objects are at depth 2 - Multi-line DAX expression bodies are always **2 levels deeper than the enclosing declaration** (depth 3 for measures/columns inside a table; depth 2 for top-level functions; depth 4 for calculationItems) - Annotations are at the same depth as properties of their parent object, separated by a blank line ### Descriptions (`///`) Triple-slash sets the `Description` property on the **next** declaration. This is native TMDL syntax (not a Tabular Editor extension); the TMDL spec treats `///` as first-class description support. ```tmdl /// Count of distinct products in the current filter context. measure '# Products' = COUNTROWS ( VALUES ( Product[Product Name] ) ) formatString: #,##0 lineageTag: abc-123 ``` **Rules:** - `///` must be immediately followed by a declaration on the next line - No blank line between `///` and the declaration - Multiple `///` lines concatenate into a single description - `///` applies to the next `measure`, `column`, `table`, `hierarchy`, or `level` **Common mistake:** ```tmdl // WRONG: blank line between /// and declaration /// This is a description. measure 'My Measure' = 1 // WRONG: /// used as a separator comment /// measure 'My Measure' = 1 // RIGHT: /// immediately before declaration /// This is a description. measure 'My Measure' = 1 // RIGHT: // used for regular comments // This is just a comment, not a description. measure 'My Measure' = 1 ``` ### Comments (`//`) Double-slash is a regular comment with no semantic effect: ```tmdl // This is a comment — it does not set any property measure 'My Measure' = 1 ``` ### Property Ordering Properties should follow a consistent order, though TMDL is not strict about it. The conventional order is: **For columns:** `dataType`, `isHidden`, `isKey`, `displayFolder`, `lineageTag`, `summarizeBy`, `isNameInferred`, `sourceColumn`, `sortByColumn`, then annotations. **For measures:** DAX expression (on the `=` line or multi-line), `formatString` or `formatStringDefinition`, `displayFolder`, `lineageTag`, then annotations. ## Name Quoting ### When to Quote Use single quotes around names that contain any of these characters: - Spaces: `'Product Name'` - Dots: `'Sales.Amount'` - Equals: `'Price = Target'` - Colons: `'Date:Key'` - Single quotes (escape by doubling): `'Customer''s Name'` - Other special characters: `'Sales ($)'`, `'OTD % (Value)'`, `'1) Selected Metric'` - Names starting with a digit: `'4) Selected Period'` ### When NOT to Quote Do not quote names that are simple identifiers: - `Product` (simple word) - `_Measures` (underscore prefix, no spaces) - `Date` (simple word) - `CgMetricQuantity` (PascalCase, no spaces) ### Examples ```tmdl table Product // unquoted: simple name table _Measures // unquoted: underscore prefix table 'Budget Rate' // quoted: contains space table 'Invoice Document Type' // quoted: contains spaces table '1) Selected Metric' // quoted: starts with digit table 'On-Time Delivery' // quoted: contains space ``` ## Column Definitions For complete column examples (basic, hidden, key, sortByColumn, description), see **`references/tmdl-file-examples.md`**. For full property reference, see **`references/column-properties.md`**. Key column pattern: ```tmdl column 'Product Name' dataType: string displayFolder: 1. Product Hierarchy lineageTag: abc-123 summarizeBy: none sourceColumn: Product Name annotation SummarizationSetBy = Automatic ``` ## Measure Definitions ### Single-Line DAX ```tmdl measure '# Products' = COUNTROWS ( VALUES ( Product[Product Name] ) ) formatString: #,##0 displayFolder: Measures lineageTag: abc-123 ``` ### Multi-Line DAX Two syntaxes for multi-line DAX: **1. Indented block** (most common) -- expression body indented two levels deeper than the declaration: **2. Triple-backtick block** -- DAX enclosed in `` ``` `` fences, useful for expressions with complex indentation: ```tmdl measure Percentage = ``` VAR _Total = CALCULATE( SUM ( 'Table'[Quantitative] ), REMOVEFILTERS ( ) ) RETURN DIVIDE ( SUM ( 'Table'[Quantitative] ), _Total ) ``` formatString: 0.0%;-0.0%;0.0% lineageTag: abc-123 ``` **Indented block syntax** (standard approach) -- indented two extra tabs from the measure's parent (table) level: ```tmdl measure 'Actuals MTD' = CALCULATE ( [Actuals], CALCULATETABLE ( DATESMTD ( 'Date'[Date] ), 'Date'[IsDateInScope] ) ) formatString: #,##0 displayFolder: 2. MTD\Actuals lineageTag: abc-123 ``` ### Measure with Description ```tmdl /// Number of workdays elapsed month-to-date, considering only dates in scope. measure '# Workdays MTD' = CALCULATE( MAX( 'Date'[Workdays MTD] ), 'Date'[IsDateInScope] = TRUE ) formatString: #,##0 displayFolder: 5. Weekday / Workday\Measures\# Workdays lineageTag: abc-123 ``` ### Measure with formatStringDefinition (Dynamic Format) ```tmdl measure 'Sales Target MTD vs. Actuals (%)' = Comparison.RelativeToTarget ( [Actuals MTD], [Sales Target MTD] ) displayFolder: 2. MTD\Sales Target lineageTag: abc-123 formatStringDefinition = FormatString.Comparison.RelativeToTarget ( "SUFFIX", 1, "ARROWS", "", "" ) ``` **Note:** `formatStringDefinition` replaces `formatString` when the format is computed dynamically via a DAX expression (often a calculation group format function). ## Other Object Types For complete examples of calculated columns, roles (RLS/OLS), calculation groups, date table marking, hierarchies, partitions, relationships, shared expressions, and model configuration, see **`references/tmdl-file-examples.md`**. ## Common Data Quality Patterns ### summarizeBy Rules | Column Type | Correct `summarizeBy` | Reason | |-------------|----------------------|--------| | Keys (surrogate/natural) | `none` | Keys are never aggregated | | Attributes (names, codes, types) | `none` | Text attributes are never summed | | Dates | `none` | Dates are never summed | | Boolean flags | `none` | Flags are never summed | | Additive numeric facts (amounts, quantities) | `sum` | Default aggregation is SUM | | Non-additive numeric facts (rates, percentages) | `none` | Cannot be meaningfully summed | **Common fix pattern** — changing `summarizeBy: sum` to `summarizeBy: none` for key columns: ```tmdl // Before (wrong - key column should not sum) column 'Customer Key' dataType: int64 isHidden lineageTag: abc-123 summarizeBy: sum sourceColumn: Customer Key // After (correct) column 'Customer Key' dataType: int64 isHidden lineageTag: abc-123 summarizeBy: none sourceColumn: Customer Key ``` ### formatString Patterns | Data Type | Pattern | Example |