semantic-model

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

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

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

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

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

Relationships

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

With additional properties:

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

Hierarchies

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

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

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

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

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

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

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

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:

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:

1. Add relationships - Define relationships between fact and dimension tables
2. Add measures - Use the dax skill to create business measures
3. 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