🔹1. Name Fact Tables Based on Actions or Events
Use names like SalesTransactions, CustomerInteractions, or InventoryMovements.
Why: Copilot interprets table names semantically. Action-oriented names help it understand the purpose of the data, improving the relevance of generated insights.
🔹2. Ensure All Fact Tables Have Relationships
Connect facts to dimensions using proper relationships.
Why: Relationships allow Copilot to traverse the model intelligently. Without them, it may misinterpret data or fail to generate meaningful summaries. I had a model with one, flat table, and Copilot could only return a card visual as a new report page.
🔹3. Set Default Categories for Location Data
Use the data category property to assign categories like City, State, or Country to geographic columns.
Why: Helps Copilot recognize location fields for mapping and geo-based analysis.
🔹4. Run Diagnostics on Import Models
Use tools to check for query folding.
Why: Query folding pushes transformations to the source system, improving performance and reducing memory usage. This does not directly affect Copilot, but you can obtain efficiencies on on refresh duration and CPU(s).
Technical insight: The developer of the Power BI connector builds logic into their connector to translate M functions into the language of the source system. Some functions "fold" and others do not. The source system compute will always perform better than the mashup engine of Power BI, which was originally built for Excel.
🔹5. Monitor Performance in Direct Query Models
Use Performance Monitor and DAX Studio to check for folding and bottlenecks.
Why: Ensures efficient query execution and helps identify areas for optimization, for example, identifying steps that do not fold or inefficient DAX measures. If Copilot cannot answer a question from the visuals, it will search the semantic model, and you need it to be timely.
🔹6. Use Measure Killer to Clean Up
Identify and remove unused columns and measures with Measure Killer.
Why: Reduces model complexity and size, making it easier and more efficient for Copilot to navigate and generate accurate results.
🔹7. Review Default Aggregations
Ensure fields are aggregated correctly by default (e.g., Average for price, not Sum). Some fields, like Year or CustomerNumber should be set to "Don't Summarize" by default.
Why: Prevents misleading insights and ensures Copilot applies the correct logic.
🔹8. Validate Data Types
Check that columns are correctly typed (e.g., numeric, date, Boolean).
Why: Proper data types help Copilot interpret fields correctly and apply appropriate operations. It also has performance bennefits when going from text to numeric if numeric data is stored as text.
🔹9. Hide Key Columns
Hide primary, foreign, and irrelevant business keys from report view.
Why: These are useful for relationships but not for analysis. Hiding them de-clutters the model and guides Copilot toward meaningful fields. This is easily done through Prep for AI.
🔹10. Rename Abbreviated Columns
The naming convention, e.g. Customer_ID or Customer ID does not matter, but be sure replace CustID with CustomerID, Rev with Revenue, spell out acronyms, etc.
Why: Full names improve clarity and help Copilot understand the semantic meaning of each field.
🔹11. Rename Columns to Be Descriptive
Use names like TotalSalesAmount instead of Amount.
Why: Descriptive names help Copilot generate more accurate narratives and visuals.
🔹12. Prep Data for AI Consumption
- Cast numeric strings to numeric types
- Convert 0/1 flags to Boolean
- Format date fields properly (e.g., Year as a date formatted as YYYY)
- De-normalize tables to include dimensions
Why: These steps ensure Copilot can apply time intelligence, Boolean logic, and numeric operations correctly. Flattened models also improve performance and simplify analysis.
🔹13. Include Time Intelligence Measures
Add measures like Year-over-Year Change, Month-over-Month Change, and Rolling Averages.
Why: These measures enable Copilot to analyze trends and identify key influencers over time. Without them, Copilot may only describe changes without explaining drivers. For example, in a model without time intelligence, Copilot might say:
"The decline in variable profit for Product X from Q1 to Q2 2025 is evident, with average profit dropping from $X in January to $X in April, $X in May, and $X in June. The data does not provide explicit reasons for this decline." Including time-based metrics gives Copilot the context it needs to surface meaningful insights.
✅Final Thoughts
Optimizing your semantic model both about performance and about enabling Copilot to deliver the best possible insights. These practices help bridge the gap between raw data and intelligent, AI-powered storytelling.
Microsoft Employee
