Power BI Semantic Models: Measures, Governance and Copilot Readiness

· Power BI

The semantic model (formerly "dataset") is where consistency is won or lost — one governed layer of tables, relationships, and DAX that every report inherits.

The semantic model is the foundation of any Power BI report. Formerly called "datasets," semantic models define how your data is structured, related, and calculated. Get it right once and every report built on top inherits the same definitions, the same logic, and the same numbers. Get it wrong and every analyst quietly rebuilds their own version of the truth.

This guide covers model design, governed measures, security testing and the changes that matter when evaluating Fabric and Copilot. Updated 9 October 2026.

TL;DR

A semantic model is a reusable definition of your business data, not simply a report's imported tables. Start with grain, relationships and explicit measures. Storage mode changes how data is read; Copilot changes how users explore it. Neither removes the need for reconciled numbers.

Semantic models in the Fabric and Copilot era

Import, DirectQuery and Direct Lake are storage choices within the broader modelling architecture. Direct Lake uses Delta tables in OneLake and reframes file references rather than creating a full Import copy. Its OneLake and SQL forms have different fallback behaviour; compare them in the Direct Lake decision guide.

For Copilot, readable measures and descriptions become especially important. “Net Revenue” should explain currency, credit notes, intercompany treatment and reporting period. A field called “Amount” does not carry that context. Microsoft's preparation workflow includes AI instructions and verified answers, with some features in preview. See the Copilot readiness checklist and official tutorial.

Hiding technical columns reduces clutter; it does not restrict access. Resolve permissions independently from the fields shown to report authors.

What You'll Learn

  • Understanding semantic model architecture
  • Designing effective data models
  • Creating calculations with DAX
  • Optimizing model performance

What is a Semantic Model?

A semantic model is a business logic layer that sits between your raw data sources and your reports. It includes tables, relationships, hierarchies, and calculations that make data easy to understand and use.

Components of a Semantic Model

  • Tables: Data arranged in rows and columns
  • Relationships: Links between tables
  • Hierarchies: Drill-down structures
  • Measures: Calculated values
  • KPIs: Performance indicators

Designing Your Data Model

Star Schema

The star schema is the recommended approach for Power BI models:

  • Fact tables: Transactional data (sales, orders)
  • Dimension tables: Descriptive attributes (customers, products)
  • Single relationship: One-to-many from dimension to fact
  • Simplified queries: Direct relationships

Table Types

TypePurposeExample
FactTransactionsSales, Orders
DimensionDescriptionsCustomer, Product
BridgeMany-to-manyCustomer-Product

Creating Relationships

Relationship Properties

  • Cardinality: One-to-One, One-to-Many, Many-to-Many
  • Cross Filter Direction: Both or Single
  • Make Relationship Active: Enable/disable

DAX in Semantic Models

DAX (Data Analysis Expressions) powers calculations in your model.

Measure Types

  • Implicit: Auto-created by visuals
  • Explicit: Created by you using DAX
  • Calculated columns: Stored model columns, not measures; useful for grouping or relationships when upstream transformation is unsuitable

Best Practices

  1. Use star schema: Facts and dimensions
  2. Hide raw columns: Expose only business logic
  3. Create hierarchies: Enable drill-down
  4. Document measures: Use descriptions
  5. Test with different reports: Ensure consistency

Optimization

  • Reduce columns: Only include necessary fields
  • Use aggregations: Pre-summarize large tables
  • Optimize relationships: Single direction when possible

Advanced Modeling Patterns

Beyond basic star schema, Power BI supports advanced modeling techniques for complex business scenarios. Understanding these patterns helps you handle situations that don't fit the traditional dimensional model.

Snowflake Schema

While star schema is preferred, snowflake schemas (normalized dimension tables) can be appropriate when dimensions have significant shared attributes that warrant their own tables.

  Snowflake Example:
  ────────────────────────────────────

  fact_Sales
    └── dim_Product (FK)
          └── dim_ProductCategory (FK)
                └── dim_ProductGroup (FK)

  Benefits:
  - Reduced data redundancy
  - Easier category-level reporting
  - More normalized data structure

  Trade-offs:
  - More complex queries
  - Potentially slower performance
  - More relationships to manage

Slowly Changing Dimensions (SCD)

When dimension attributes change over time, you need to decide how to handle those changes. Power BI supports various SCD strategies depending on your analytical needs.

TypeDescriptionUse Case
Type 1Overwrite old valuesCorrect typos, minor updates
Type 2Add new row with historyCustomer address changes
Type 3Add columns for old/newCompare before/after

Calculated Columns vs Measures

Understanding when to use calculated columns versus measures is fundamental to building efficient semantic models. Each has specific use cases and performance implications.

AspectCalculated ColumnsMeasures
When evaluatedAt processing/refresh for Import; mode-specific behaviour elsewhereWhen a query requests the measure
StorageStored in modelNot stored
ContextStarts with row context; CALCULATE can introduce context transitionEvaluated in the query's filter context
Use forSlicing, relationshipsAggregations, KPIs

Row-Level Security in Semantic Models

Row-level security (RLS) restricts data access based on user roles. Semantic models support both static and dynamic RLS to control what data each user can see.

Static RLS

Static RLS assigns specific values to roles. This approach is simpler but requires more maintenance as users change roles.

Dynamic RLS

Dynamic RLS automatically filters data based on the current user's credentials. This approach is more scalable and maintainable.

  Dynamic RLS Example:
  ────────────────────────────────────

  -- Exact user identity match, not an email-domain match
  [Email] = USERPRINCIPALNAME()

  -- Filter by user's department
  [Department] = 
      LOOKUPVALUE(
          'User Departments'[Department],
          'User Departments'[Email],
          USERPRINCIPALNAME()
      )

  -- Filter by user's region
  [Region] IN {
      LOOKUPVALUE(
          'User Regions'[Region],
          'User Regions'[Email],
          USERPRINCIPALNAME()
      )
  }

Performance Optimization Techniques

A well-optimized semantic model improves report performance and user experience. Implement these techniques to ensure your models perform efficiently.

  1. Use appropriate data types: Choose the smallest data type that can hold your values
  2. Remove unnecessary columns: Only import columns that will be used in reports
  3. Hide unused fields: Hide columns from the field list to prevent accidental use
  4. Use aggregations: Pre-calculate summaries for large tables
  5. Optimize DAX: Use variables and avoid nested iterators when possible
  6. Monitor VertiPaq: Use Analyzer to identify optimization opportunities

Semantic Model Best Practices Summary

  • Start simple: Begin with a basic star schema and add complexity only when needed
  • Use meaningful names: Name tables and columns descriptively for business users
  • Document your model: Add descriptions to tables, columns, and measures
  • Test thoroughly: Verify calculations with known data sets
  • Monitor performance: Use tools to identify and resolve bottlenecks
  • Version control: Save your work and track changes

Frequently Asked Questions

Can I use a single semantic model for multiple reports?

Yes, sharing semantic models across multiple reports is a best practice that ensures consistency, reduces maintenance, and improves performance through model caching.

How many tables should a semantic model have?

There is no universal ideal table count. Organise facts by grain and share conformed dimensions. Assess maintainability, relationship clarity and measured performance rather than merging unrelated facts to hit an arbitrary limit.

Should I use calculated columns or Power Query for data transformation?

Prefer source-side transformations or Power Query for reusable data preparation when appropriate. In Import models, calculated columns are computed during model processing and stored, not recalculated for every visual query. Measures are evaluated at query time. Choose based on folding, storage mode and the purpose of the calculation.

A finance model acceptance checklist

Before publishing a shared model, reconcile totals to the source ledger. Then slice by period, business unit and account to expose disconnected dimensions or duplicated facts. Validate that ratios divide aggregated numerators by aggregated denominators rather than averaging row percentages.

Document currency and sign conventions. Keep monthly budgets separate from transaction-level actuals and filter both through shared dimensions. Define what happens for an incomplete month or zero denominator.

Test access as a restricted user, not just the author. In the Power BI service, RLS applies to Viewers; Admin, Member and Contributor workspace roles are not restricted by RLS in the same way. Confirm workspace roles and model permissions using Microsoft's RLS guidance.

Use data modelling best practices for performance diagnosis and the Excel migration guide when replacing spreadsheet-specific definitions with a shared model.

Conclusion

A well-designed semantic model is the foundation of successful Power BI implementations. By following dimensional modeling principles, understanding when to use calculated columns versus measures, implementing proper security, and optimizing for performance, you create a robust foundation that scales with your organization's analytical needs.

Need a semantic model reviewed before a Copilot rollout or a migration? See the Power BI service or send a short brief.