Semantic Model Skill

This skill helps create and modify Power BI semantic models using TMDL (Tabular Model Definition Language) format.

When to Use This Skill

•Creating new tables with columns
•Defining relationships between tables
•Adding hierarchies to tables
•Configuring data sources and partitions
•Setting model properties (culture, compatibility level)
•Applying Tabular Editor patterns

TMDL Syntax Overview

TMDL uses indentation-based syntax (tabs, not spaces) with these key constructs:

Tables

tmdl

table Sales
	lineageTag: a1b2c3d4-e5f6-7890-abcd-ef1234567890

	column 'Sales Amount'
		dataType: decimal
		formatString: "$#,##0.00"
		summarizeBy: sum
		lineageTag: b2c3d4e5-f6a7-8901-bcde-f12345678901

	column 'Order Date'
		dataType: dateTime
		formatString: Short Date
		lineageTag: c3d4e5f6-a7b8-9012-cdef-123456789012

	partition Sales = m
		mode: import
		source =
			let
			    Source = Sql.Database("server", "database"),
			    Sales = Source{[Schema="dbo",Item="Sales"]}[Data]
			in
			    Sales

Columns

tmdl

column 'Column Name'
	dataType: <type>
	formatString: <format>
	summarizeBy: <aggregation>
	isHidden
	lineageTag: <guid>

	annotation SummarizationSetBy = Automatic

Data Types:

•string - Text values
•int64 - Whole numbers
•decimal - Fixed decimal numbers
•double - Floating point numbers
•dateTime - Date and time values
•boolean - True/False values
•binary - Binary data

Summarize By:

•none - No aggregation (for dimensions)
•sum - Sum values
•count - Count rows
•min - Minimum value
•max - Maximum value
•average - Average value

Measures

tmdl

/// Description of the measure
/// Appears as tooltip in Power BI
measure 'Total Sales' =
	SUM(Sales[Sales Amount])
	formatString: "$#,##0.00"
	displayFolder: Revenue
	lineageTag: d4e5f6a7-b8c9-0123-def0-234567890123

Calculated Columns

tmdl

column 'Profit Margin' =
	DIVIDE(Sales[Profit], Sales[Revenue], 0)
	dataType: double
	formatString: "0.00%"
	lineageTag: e5f6a7b8-c9d0-1234-ef01-345678901234

Relationships

tmdl

relationship <guid>
	fromColumn: Sales.'Product Key'
	toColumn: Products.'Product Key'

With additional properties:

tmdl

relationship a1b2c3d4-e5f6-7890-abcd-ef1234567890
	fromColumn: Sales.'Date Key'
	toColumn: Date.'Date Key'
	crossFilteringBehavior: bothDirections
	securityFilteringBehavior: bothDirections
	isActive

Hierarchies

tmdl

hierarchy 'Date Hierarchy'
	lineageTag: f6a7b8c9-d0e1-2345-f012-456789012345

	level Year
		column: Year
		lineageTag: a7b8c9d0-e1f2-3456-0123-567890123456

	level Quarter
		column: Quarter
		lineageTag: b8c9d0e1-f2a3-4567-1234-678901234567

	level Month
		column: Month
		lineageTag: c9d0e1f2-a3b4-5678-2345-789012345678

File Organization

Standard File Structure

code

<ProjectName>.SemanticModel/
└── definition/
    ├── database.tmdl       # Database name and compatibility
    ├── model.tmdl          # Model-level settings
    ├── relationships.tmdl  # All relationships
    ├── expressions.tmdl    # Shared expressions/parameters
    └── tables/
        ├── Sales.tmdl
        ├── Products.tmdl
        ├── Date.tmdl
        └── ...

One Table Per File

Each table should be in its own file named tables/<TableName>.tmdl:

tmdl

table Products
	lineageTag: <guid>

	column 'Product Key'
		dataType: int64
		isKey
		summarizeBy: none
		lineageTag: <guid>

	column 'Product Name'
		dataType: string
		summarizeBy: none
		lineageTag: <guid>

	column Category
		dataType: string
		summarizeBy: none
		lineageTag: <guid>

	partition Products = m
		mode: import
		source = ...

Common Table Patterns

Fact Table

tmdl

table 'Fact Sales'
	lineageTag: <guid>

	/// Foreign key to Date dimension
	column 'Date Key'
		dataType: int64
		isHidden
		summarizeBy: none
		lineageTag: <guid>

	/// Foreign key to Product dimension
	column 'Product Key'
		dataType: int64
		isHidden
		summarizeBy: none
		lineageTag: <guid>

	column 'Sales Amount'
		dataType: decimal
		formatString: "$#,##0.00"
		summarizeBy: sum
		lineageTag: <guid>

	column Quantity
		dataType: int64
		summarizeBy: sum
		lineageTag: <guid>

	partition 'Fact Sales' = m
		mode: import
		source = ...

Dimension Table

tmdl

table Products
	lineageTag: <guid>

	column 'Product Key'
		dataType: int64
		isKey
		isHidden
		summarizeBy: none
		lineageTag: <guid>

	column 'Product Name'
		dataType: string
		summarizeBy: none
		lineageTag: <guid>

	column Category
		dataType: string
		summarizeBy: none
		lineageTag: <guid>

	column Subcategory
		dataType: string
		summarizeBy: none
		lineageTag: <guid>

	hierarchy 'Product Hierarchy'
		lineageTag: <guid>

		level Category
			column: Category
			lineageTag: <guid>

		level Subcategory
			column: Subcategory
			lineageTag: <guid>

		level Product
			column: 'Product Name'
			lineageTag: <guid>

	partition Products = m
		mode: import
		source = ...

Data Source Configuration

SQL Server

tmdl

expression Server = "your-server.database.windows.net" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression Database = "YourDatabase" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Partition with SQL Source

tmdl

partition Sales = m
	mode: import
	source =
		let
		    Source = Sql.Database(Server, Database),
		    dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data]
		in
		    dbo_Sales

Partition with SharePoint

tmdl

partition Data = m
	mode: import
	source =
		let
		    Source = SharePoint.Files("https://company.sharepoint.com/sites/data", [ApiVersion = 15]),
		    File = Source{[Name="data.xlsx"]}[Content],
		    Data = Excel.Workbook(File, true, true),
		    Sheet = Data{[Item="Sheet1",Kind="Sheet"]}[Data]
		in
		    Sheet

Lineage Tags

Every object needs a unique lineageTag (GUID). Generate new GUIDs for each object:

code

lineageTag: a1b2c3d4-e5f6-7890-abcd-ef1234567890

GUIDs should be lowercase and properly formatted (8-4-4-4-12 pattern).

Boundaries and Constraints

DO

•Always use TMDL format (not JSON/BIM)
•Always include lineageTag for every object
•Use isHidden for key columns in dimension tables
•Set summarizeBy: none for dimension columns
•Use summarizeBy: sum (or appropriate) for measure columns
•Include isKey on primary key columns
•Add descriptions using /// comments above measures
•Use single quotes for identifiers with spaces

DO NOT

•Never use implicit measures (set discourageImplicitMeasures: true)
•Never create bi-directional relationships unless required
•Never expose key columns to end users
•Never duplicate data across tables
•Never use calculated columns when measures will work

Workflow Integration

After creating tables:

•Add relationships - Define relationships between fact and dimension tables
•Add measures - Use the dax skill to create business measures
•Validate - Use the best-practices skill to check the model

Common Issues

"Duplicate lineageTag"

Each lineageTag must be unique. Generate a new GUID for every object.

"Invalid relationship"

•Verify column names match exactly (case-sensitive)
•Ensure data types are compatible
•Check that the "to" column is the key column

"Partition source error"

•Verify M expression syntax
•Check that data source expressions exist
•Ensure credentials are configured in Power BI Desktop