Power BI - Powered by Copilot

[PradipVS]

This article is co-authored by Pradip VS and Vivienne Vereen, Architects at Microsoft

In today’s data-driven world, businesses are constantly seeking ways to gain deeper insights and make informed decisions. Power BI, an advanced visualization tool, has been a leader in the Gartner Magic Quadrant in the Analytics and Business Intelligence space for many years, offering powerful features such as NLP, Q&A, smart narratives, and more. With the recent addition of Copilot, Power BI has become even smarter and more innovative, helping customers gain insights in a much simpler fashion.

Introduction to Power BI Copilot 

Power BI Copilot is designed to assist both developers and business users. For developers, it helps build, optimize, and explain DAX queries, improving productivity and efficiency. For report creators and business users, Copilot uncovers hidden insights from semantic models through a chat experience grounded in the provided semantic model data, which would otherwise require significant effort to discover.

Use Case and Challenges

Many customers aim to unlock the benefits of AI by leveraging Copilot’s features in Power BI. A common use case is discovering insights from reports by asking questions about report visuals and their underlying data. This is highly beneficial for business users, offering quick time to insights and high ROI. Often, as with our customer, a frequently used existing Power BI report will be the starting point rather than a newly built report.

While there is detailed information on best practices for preparing your model for Power BI Copilot, it is unrealistic to completely rebuild an existing model in production just to get Copilot to work properly.

In our case, the customer had an established, highly used, and well-adopted semantic model and report in production, making it an ideal candidate for leveraging Copilot’s features in Power BI. However, upon reviewing the semantic model, we found several challenges: no clear fact and dimension tables, no star schema, many-to-many relationships with bi-directional filtering, numerous disconnected calculated tables, and over 260 nested measures with custom business logic. While this worked for reporting in Power BI alone, it presented challenges for Power BI Copilot.

Part of the actual model which lacks fact table(s) and contains numerous many-to-many relationships with bi-directional filtering. The naming conventions are inconsistent, with many abbreviations originally used (some names have been changed for this blog). Additionally, there are several orphan dimension tables.

We were given a list of test cases (questions about data in report visuals or the semantic model, and creation of report visuals). Although this is clearly within the scope of Power BI Copilot, the underlying semantic model and reporting layer posed challenges.

For example, when asking about the value of a measure (not directly referencing the measure name but a synonym) present in one of the report visuals, Copilot could not identify the correct measure, did not recognize that this information was on the report page, and failed to identify the requested measure in the semantic model or apply the correct filter context. Consequently, the responses were incorrect and inconsistent.

Instead of blaming Copilot and questioning its production readiness, we investigated why Copilot gave the responses it did, identified patterns, and solved the underlying issues without making major changes.

Step-by-Step Solution

1. Identify why the measure wasn’t picked up and why the visual was not included as the response reference.

We found that visual level filters based on field parameters and dimension values (multiple visual level filters for a single visual) were present. Since visual level filters are currently not supported (but are on the roadmap), the workaround is to move the filters into measures. For nested measures, it is recommended to have a base measure with the field parameter logic and additional measures that reference the base measure with the dimension filter context.

2. Add synonyms in linguistic modeling and ensure the measure is not hidden and included in Q&A.

Base measures or measures/columns not to be included in Copilot should be excluded from Q&A and hidden.

3. Use unique synonyms across columns and measures.

Ensure that column/measure names are unique and clearly reflect their purpose. Avoid adding the same synonym to more than one column, table, or measure to prevent ambiguity.

4. Note that Power BI Copilot does not support bookmarks.

While Copilot can iterate over all report pages, it cannot open bookmarks. Create the bookmark content on a separate page if visual reference is required. Without this workaround, Copilot will query the semantic model, but there won’t be a reference to a report visual.

5. Prompt engineering and teaching business users how to properly prompt Copilot is crucial.

Ensure that all prompts are descriptive, specific, and clear. In our case, prompt engineering helped identify why some responses were incorrect, often due to the syntax of the prompt versus the actual measure synonym. Currently, synonym fuzzy matching is not supported but is on the roadmap. To get the best responses from Copilot, ensure the prompt includes an exact match of the expected result measure and its synonyms.

6. Add value-level synonyms.

Power BI allows table, column, and measure synonyms, but it does not natively support value-level synonyms. For example, NP = Next Period, PP = Previous Period in the Date Table. To do this, add a lookup synonyms table with ID and Synonym/Name. There can be multiple rows per ID, with a many-to-many relationship and cross-filter direction both ways.

Include the new table columns in Q&A and add a dynamic noun with Name to the ID in the Date table. This enables Copilot to understand terms like “Next Period” and “Previous Period” in a prompt and apply the appropriate filter context in its response.

Note: Testing was conducted in both Power BI Service and Desktop. It’s important to note that responses may differ because Power BI Service updates are released weekly, while Desktop updates are released monthly. This explains why Service responses may be more accurate.

Quick Wins and Long-Term Strategies

To maximize the benefits of Power BI Copilot, we suggest implementing the following several quick wins and for long-term scalability (extended impact on performance, concurrency, security) it is advised to follow the following guidelines:

Quick Wins	Long Term Strategies
As bookmarks are not included in Copilot context. If reference to bookmark visuals is required, create the bookmark visuals on a separate page or Copilot will query the semantic model.	Implement a star schema in the semantic model. Add surrogate keys where possible to clearly define relationships. Rename Fact and Dimension tables to clearly identify their content. Remove unused tables, columns, and measures from the model. Avoid many-to-many relationships. If needed, add a bridge table to keep one-to-many relationships instead. Avoid bi-directional filtering unless needed.
Use unique and easily understood measure and column names. Implement descriptive and consistent naming conventions to help Copilot interpret data correctly. For example, if the measure’s purpose is to calculate Total Sales, then write the full descriptive name instead of Tot_Sls.	Transformations should be done as far upstream as possible and as far downstream as necessary. Move transformations upstream (calculated tables/columns as well as Power Query transformations should be moved upstream for scalability and performance purposes).
Hide columns and measures that should be excluded from Copilot's context.	Add to the linguistic schema using synonyms, verbs, and nouns (refer to the references section).
Enable Q&A only for the measures to be included in Copilot's context.	Disable auto date/time unless specifically needed. In Power BI Desktop navigate to File > Options and setting > Options > Current File > Time Intelligence > Uncheck Auto Date/Time.
Add synonyms for measures and columns to improve response accuracy.	Sync slicers across pages unless there is the need to keep them independent.
Avoid visual level filters as a best practice; create measures with filter context instead.	Run and apply Best Practice Analyzer results.
If value synonyms are needed, adding a synonym table to the model will help. Add table and relationship. Include in Q&A. Add linguistic modeling as described above (refer to the references section as well).

Final Thoughts

By implementing just the quick wins, our customer achieved a 100% success rate on all questions about report visuals and the underlying model data, even with complex filter contexts. Initially, the prospect of improving the model to work well with Copilot seemed daunting. However, with just a few minor changes, we observed a significant impact on Copilot’s responses. This not only reduced time to market but also presented an incredible opportunity for business unlocks and quick insights for business users. Being at the forefront of innovation often involves change and taking risks. In the case of Power BI Copilot, the changes are minimal, the risks are low, and the rewards are substantial.

Example

Let’s take a look at a simple prompt as an example: “What is the growth for ASP?”

The answer is in a visual on the page and the expected response is -1.6% broken down by subcategories.

However, Copilot’s initial response was clearly incorrect:

1. Copilot did not identify the correct measure
2. Consequently, it could not locate it on the report visuals and queried the model instead
3. This ultimately resulted in returning incorrect measures

Three changes were made that resolved this issue:

1. Confirmed there are no visual level filters on the visual
2. Included the measure in Q&A and ensured that the measure was not hidden
3. Added unique synonyms

It was confirmed that there were no visual level filters on the visual and Copilot should be able to identify the measure without further DAX changes as discussed above:

In Q&A setup, ensure the measure (in our case “Average Selling Price Percentage”) is included in Q&A, note that synonyms can also be automatically generated by Copilot and can be reviewed in this view:

Synonyms can also be added in the model view and validate that the measure is not hidden:

Asking the same question again with the changes above, everything works as expected:

Doing some further testing for when Copilot should generate an answer based on a query against the semantic model with additional filter context: “What is the growth for ASP for beverages for last period for Large Grocery producer?” (data not available as a visual), we get the correct answer of 0.7%:

Note that currently, when querying the model in Preview, Copilot will return a visual such a card or bar chart vs. a text response when getting the response from a report visual. Additionally, there is an explanation on the logic Copilot used to generate this response against the model data.

Validating the response with the applied slicer value confirms Copilot’s answer was correct:

Is Copilot Worth It?

From what we have seen across customers, the answer is yes, if approached correctly. To ensure you get the best value out of Power BI Copilot for insight generation, start with a frequently used report that has good adoption. This way, the changes made to get the report ready for Power BI Copilot will have the highest ROI and impact. It’s important to note that Microsoft is making significant investments in AI and Copilot space. By optimizing your semantic models, you will gain long-term benefits as future features will also benefit from these improvements. Making smaller changes, as described above, rather than requiring a complete rebuild, makes the future of Power BI Copilot look bright. There are many advancements to the underlying LLM models, and the Power BI product group always upgrades to the latest one, making Copilot smarter and more efficient with fewer hallucinations.

What About the Development Effort?

Based on our experience with many customers, we have found that making reports Copilot-friendly not only aids in insight generation but also significantly enhances their performance. This process often involves removing unnecessary columns, measures, and data, which improves both efficiency and scalability. Additionally, Copilot can uncover insights that were previously unnoticed or not included in the report visuals, revealing key hidden information. While there is some initial effort required, the long-term benefits make it well worth the investment.

Roadmap and Future Outlook

Official documentation of the Power BI Copilot roadmap can be found here. A few exciting things coming to Copilot to look forward to include the ability for Copilot to answer why questions, and new Copilot features in Fabric Org Apps.

If you have experience with similar initiatives and have helped customers unlock potential using Copilot in Power BI, please share your insights in the comments.

— Pradip VS & @VivienneVereen, Architects at Microsoft