← Back to series overview

Semantic Models: Why They Matter + Build with Copilot

Hey everyone, this week I learned why my customer's "just point Power BI at gold" idea kept producing three different Total Sales. The model is where meaning lives, here's how it really works under the hood.

Power BI & AI 3 min read Core concept
By Felipe Andrade Author, TemJayto

What you'll build in Part 3

  • Understanding: why semantic models beat "direct table" reporting
  • A DirectLake semantic model over gold Delta, no refresh lag
  • Relationships + DAX measures generated by Copilot via Power BI MCP
  • RLS role so regions only see their own sales

Table of Contents

Why semantic models are important

Pointing Power BI at gold tables works, until it doesn't. My customer provides services to financial institutions, so "two versions of sales" isn't a nit, it's an audit finding. Notice I'm talking about the DirectLake semantic model of today, not the old Import-only PBIX pattern. Under the hood it's the same lesson as EmbedToken: the token (or measure) you see is derived from something upstream, get the upstream wrong and everything downstream inherits it. A model fixes three things at once:

Without modelWith semantic model
5 versions of "sales"One certified measure: [Total Sales]
Slow full-table scansRelationships + aggregations + DirectLake = sub-second
No governanceRLS/OLS, lineage, audit built in
Report authors write SQLDrag-and-drop trusted fields
What I missed the first time: gold is storage shape (facts + dims). The model is business meaning (measures, hierarchies, security). You need both. Parts 2 and 3 together.

Connect Power BI MCP

Fabric MCP got data in. Power BI MCP lets Copilot read and write the model: discover tables, generate DAX, run queries, apply themes. Same as Part 1, your user login is enough, no service principal.

// .vscode/mcp.json, add alongside fabric, user auth
{
  "servers": {
    "fabric": { "...": "see Part 1" },
    "powerbi": {
      "type": "stdio",
      "command": "npx",
      "args": ["@microsoft/powerbi-mcp-server@latest"],
      "env": {
        "PBI_WORKSPACE_ID": "${env:PBI_WORKSPACE_ID}"
      }
    }
  }
}
XMLA required: workspace must sit on Premium/Fabric capacity with XMLA read/write enabled. That's what lets the MCP server edit the model. Sign in with a user that has workspace Admin/Member, no SP needed locally.

Build the model on gold (DirectLake)

You: Create semantic model Sales Star Schema on gold Delta in DirectLake mode: tables FactSales, DimDate, DimProduct, DimCustomer, DimStore with single-direction relationships from dims to fact
Copilot: 
// MCP Call: powerbi.createDataset(ws, {
  name: "Sales Star Schema", mode: "DirectLake",
  source: "SalesLakehouse/Files/gold/star_schema/",
  tables: ["FactSales","DimDate","DimProduct","DimCustomer","DimStore"],
  relationships: [
    "DimDate[DateKey] to FactSales[DateKey]",
    "DimProduct[ProductKey] to FactSales[ProductKey]",
    "DimCustomer[CustomerKey] to FactSales[CustomerKey]",
    "DimStore[StoreKey] to FactSales[StoreKey]"
  ]
})
Model created. DirectLake = queries hit Delta directly, no import refresh. Fallback to DirectQuery is automatic.

DAX measures with Copilot

Tables are scaffolding. Measures are the product. Let Copilot draft them, then validate.

You: Add measures: Total Sales, Total Quantity, YoY Growth % with SAMEPERIODLASTYEAR, and Gross Margin % handling divide-by-zero
Copilot: 
Total Sales = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice] - FactSales[DiscountAmount])

Total Quantity = SUM(FactSales[Quantity])

YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN DIVIDE(CurrentSales - PriorSales, PriorSales)

Gross Margin % =
DIVIDE([Total Sales] - SUM(FactSales[CostAmount]), [Total Sales])
// MCP Call: powerbi.addMeasures(ws, datasetId, measures)
// MCP Call: powerbi.runDax(ws, 'EVALUATE ROW("YoY", [YoY Growth %])') to 0.152 [ok]
Validate every measure: ask Copilot "explain this DAX + give 2 test cases" before merging. Wrong measures in a certified model spread fast.

Secure it: RLS in 5 minutes

// TMDL role: regions see only their rows
role RegionalSales
{
    modelPermission: read;
    tablePermission DimStore
    {
        filterExpression: DimStore[Region] = USERPRINCIPALNAME();
    }
}
You: Test RLS: query Total Sales as a North America user vs an EU user. Results must differ
Copilot: 
// MCP: powerbi.runDax(ws, query, { effectiveUser: "na-lead@contoso.com" }) to $4.2M
// MCP: powerbi.runDax(ws, query, { effectiveUser: "eu-lead@contoso.com" }) to $2.8M
RLS enforced. Executives keep a full-access role.

What's next in Part 4

Model = meaning. Now we need a report. Part 4 moves to local development: PBIP projects in VS Code, Copilot generating pages and visuals, bound to this exact model.

← Part 2: Engineering Continue to Part 4: Build the Report →