Optimize your semantic model for Copilot in Power BI

Applies to: ✅ Power BI service

A well-prepared semantic model gives Copilot a stronger foundation for answering business questions accurately in Power BI. Before you introduce Copilot to users, evaluate your model and address issues that could affect answer quality. Start with the measures, relationships, and business definitions already used in your reports. Resolve modeling issues in the semantic model because additional instructions can't correct inaccurate relationships or inappropriate aggregations. Then, optimize the model for Copilot and establish a consistent review process.

This article is intended for semantic model authors, report owners, and teams that are introducing Copilot to users. It complements Ask Copilot questions about your data, which describes how users can ask questions and verify the results. This article explains how to prepare your semantic model and evaluate the answers Copilot produces.

Note

Keep the following requirements in mind:

Note

In the Power BI service web modeling experience, Copilot can do more than evaluate your model. It can also propose and apply changes directly, such as renaming tables and columns, creating relationships, and generating DAX measures. The optimization guidance in this article directly supports better results from that analysis: a well-structured, clearly named model helps Copilot produce more accurate and useful suggestions.

Start with focused business questions

Choose a focused business scenario and identify the questions users commonly ask. For each question, define the expected measure, grouping, date basis, filters, and validated result.

Include questions that expose ambiguity. For example, How are sales performing? might refer to revenue, order volume, growth, or margin. Define a default interpretation, or instruct Copilot to ask for clarification.

Start with questions about totals, comparisons, rankings, and trends that the semantic model supports. Add more complex questions after Copilot answers the initial set consistently.

Review and improve your semantic model

Use the recommendations in the following tables to help Copilot interpret your semantic model and improve answer quality.

Model structure

Element Consideration Description Where to apply Example
Table linking Define clear relationships Clearly define all relationships between tables and make sure they're logical. Indicate which relationships are one-to-many, many-to-one, or many-to-many. In Model view, select Manage relationships Create a one-to-many relationship from Date[DateID] to Sales[DateID] and verify the relationship is active.
Fact tables Clear purpose and level of detail Clearly identify fact tables and define what each row represents. Ensure calculations use aggregations appropriate for that level of detail. In table properties and the model structure Name fact tables clearly, such as FactSales, and describe their level of detail, such as one row per order line.
Dimension tables Supportive descriptive data Create dimension tables that contain the descriptive attributes related to the quantitative measures in fact tables. In table properties and data model structure Create dimension tables like DimProduct with attributes (ProductName, Category, Brand) and DimCustomer with attributes (CustomerName, City, Segment).
Hierarchies Logical groupings Establish clear hierarchies within the data, especially for dimension tables that support drilling down in reports. In the table context menu, select New hierarchy In the Date table, create a hierarchy: Year > Quarter > Month > Day. In Geography table: Country/Region > State > City.
Relationship types Clearly specified To ensure accurate report generation, clearly specify the nature of relationships (active or inactive) and their cardinality. In relationship properties dialog Set Date to Sales as Many-to-One (active), Product to Sales as Many-to-One (active), and mark role-playing relationships as inactive when appropriate.

Measures and KPIs

Copilot can create ad hoc calculations, but add frequently used or business-critical calculations to the semantic model as reviewed measures. This approach gives report authors and Copilot consistent business definitions to use.

Element Consideration Description Where to apply Example
Measures Standardized calculation logic Give measures standardized, clear calculation logic that's easy to explain and understand. In measure definition and description property Measure DAX: Total Sales = SUM(Sales[SaleAmount]) and add description: "Sum of all sales amounts."
Measures Naming conventions Give measures names that clearly reflect their calculation and purpose. In measure name field when creating measures Use descriptive name: Average Customer Rating instead of abbreviated: AvgRating.
Measures Predefined and reviewed measures Create reusable measures for common requests and business-critical calculations. Review their logic so that report authors and Copilot use consistent business definitions. In the measure definition and description property Define [Net Sales] with the approved treatment of returns, discounts, and canceled orders. Include commonly requested measures such as year-to-date sales and month-over-month growth.
Measures Ratios Define ratios with the appropriate numerator, denominator, and aggregation behavior. In the measure definition Define [Gross Margin %] by dividing the approved gross margin measure by the approved net sales measure instead of averaging row-level percentages.
Measures Counts Create separate measures when row counts and business-event counts represent different results. In the measure definition Define [Order Count] separately from [Order Line Count].
Measures Point-in-time balances Use measures that aggregate snapshots appropriately across products, locations, and dates. In the measure definition Define an ending-inventory measure instead of summing daily inventory snapshots across dates.
Measures Display formats Apply formats that make the unit and scale of each measure clear. In the measure Format property Format monetary measures as currency, ratios as percentages, and counts as whole numbers.
Key performance indicators (KPIs) Predefined and relevant Establish a set of KPIs that are relevant to the business context and appear often in reports. Create measures for commonly tracked KPIs Define measures like ROI = DIVIDE([Profit], [Investment]), CAC = DIVIDE([Marketing Spend], [New Customers]), LTV = [Avg Order Value] * [Purchase Frequency] * [Customer Lifespan].

Columns and data quality

Element Consideration Description Where to apply Example
Column names Unambiguous labels Make column names unambiguous and self-explanatory. Retain useful business identifiers, but avoid IDs or codes that require further lookup without context. Rename columns in Power Query Editor or Model view Rename column from ProdID to Product ID or Product Name, and from CustNo to Customer Number.
Column data types Correct and consistent Apply correct and consistent data types for columns across all tables to ensure that measures calculate correctly and to enable proper sorting and filtering. In column properties, set Data type Ensure Sales[SaleAmount] is Decimal Number (not Text), Date[Date] is Date (not Text), Product[ProductID] is Whole Number.
Data consistency Standardized values Maintain standardized values within columns to ensure consistency in filters and reporting. Use Find and Replace or Power Query transformations In Status column, ensure all values use consistent casing: Open, Closed, Pending (not mixed case like open, CLOSED).
Date columns Clear business meaning Distinguish dates with different business meanings. Document whether periods use a fiscal or calendar year, including when the fiscal year starts. Use date columns and relationships instead of relying only on text labels. In date tables, relationships, and description properties Use distinct Order Date and Shipment Date columns. Define fiscal period columns instead of relying only on labels such as Q1.
Key columns Complete and unique values Address missing or duplicated keys so that relationships and calculations produce accurate results. In Power Query transformations and source data Verify that each row in a customer dimension has a unique, nonblank customer key.

Refresh, security, and metadata

Element Consideration Description Where to apply Example
Refresh schedules Transparent and scheduled Clearly communicate the refresh schedules of the data to ensure users understand the timeliness of the data they're analyzing. In dataset settings and documentation Add a text box or description stating: "Data refreshes daily at 6:00 AM UTC" or "Real-time data with 15-minute incremental refresh."
Security Role-level definitions Define security roles for different levels of data access if there are sensitive elements that not all users should see. In Model view, select Manage roles Create role "Sales Team" with filter: Sales[Region] = USERNAME() and role "HR" with filter on employee data tables.
Metadata Documentation of structure For reference, document the structure of the data model, including tables, columns, relationships, and measures. Use description properties and external documentation Add descriptions to tables and columns. Create a separate document with model diagram, data dictionary, and measure catalog.

DAX query considerations

The following table lists other criteria that can help you create accurate Data Analysis Expressions (DAX) queries with Copilot. These recommendations can help you generate accurate DAX queries.

Element Consideration Description Where to apply Example
Measures, tables, and columns Descriptions In the description property, define each element and how you intend to use it. Begin with its business meaning, unit, and important exclusions. Copilot uses only the first 200 characters. In Properties pane, Description field for measures, tables, and columns For measure [YOY Sales], add description: "Year-over-year (YOY) difference in Orders. Use with the 'Date'[Year] column to show by years other than the latest year. Partial years compare to same period of the prior year."
Calculation groups Descriptions The model metadata doesn't include calculation items. Use the description of the calculation group column to list and explain how to use the calculation items. Copilot uses only the first 200 characters. In Properties pane for the calculation group column For Time intelligence sample calculation group column, add description: "Use with measures and date table for Current: current value, MTD: month to date, QTD: quarter to date, YTD: year to date, PY: prior year, PY MTD, PY QTD, YOY: year over year change, YOY%: YOY as a %." For a measures table, add: "Measures are used to aggregate data. These measures can be shown as year-over-year by using this syntax CALCULATE([Measure Name], Time intelligence[Time calculation] = YOY)."

Troubleshoot test results

Review the generated query and results to identify the source of an issue before you update AI instructions.

Issue What to check
Incorrect business metric Review measure names, descriptions, AI data schema selections, and instructions that define the default metric.
Unexpected total or time period Compare the filter context and date basis with the expected result. Then, review relationship behavior and aggregation logic.
Missing categories or no results Check the source data, refresh state, category values, requested period, and the consumer's permissions. Don't interpret no result as zero.
Correct data with a misleading presentation Review units, formatting, sorting, and whether the explanation states more than the data supports.
Slow or failed response Inspect the generated query and semantic model performance. Compare the query with a validated reference query before you change business guidance.
Prep data for AI changes aren't reflected Confirm that you changed the correct semantic model and allow time for the changes to take effect. Close and reopen the Copilot pane, and verify any deployment and refresh requirements.

For testing, deployment, and refresh requirements, see Test your Copilot tooling changes and Considerations and limitations.

Related content

  • Last updated on