Enhanced DAX Query Writing with Copilot Smarter Suggestions

[Abhilash_P]

Introduction

Copilot in Power BI empowers both developers and non-technical users by assisting with tasks such as generating reports, writing and explaining DAX queries, and creating measure descriptions. However, a common debate revolves around the quality of its results.

In my view, the accuracy and usefulness of Copilot’s output largely depend on the quality of your data model. For example, if your model contains numerous acronyms, abbreviations, or internal definitions, Copilot may struggle to interpret them correctly, leading to less-than-ideal results.

In this blog, we’ll explore how to improve Copilot’s DAX query suggestions by enriching your model with additional metadata such as descriptions, synonyms, and sample values.

1. With vs. Without Descriptions: How Copilot Interprets Your Measures

In Power BI, semantic model components such as tables, columns, and measures form the foundation for building meaningful reports and analysis. These components define the structure of the data model and play a crucial role in how DAX queries are written and optimized. Now, let’s examine how adding descriptions to measures influences Copilot’s ability to generate accurate DAX queries.
Example

Suppose we have a measure called “Total Sales Excl Refunded Transactions” in our Power BI model. If no additional metadata is provided, Copilot may suggest the following DAX query:

Total Sales Excl refunded transactions =

CALCULATE( SUM(Sales[Sales Amount]), Sales[Order Status] != “Refund” )

While this query seems reasonable, it assumes that refunded transactions are identified using the “Refund” status. However, what if our model actually uses “Completed” as the correct filter for valid sales?

To improve Copilot’s understanding, we provide a clear description for the measure:

● Measure Name: Total Sales Excl Refunded Transactions

● Description: Calculates the total revenue from all completed sales transactions, excluding refunded orders.

With this additional context, Copilot is more likely to generate a refined DAX query:

Total Sales Excl refunded transactions =

CALCULATE( SUM(Sales[Sales Amount]), Sales[Order Status] = “Completed” )

Adding descriptions helps Copilot interpret measures correctly and return more accurate results. However, many users might wonder “How can I add descriptions when I have hundreds of measures?” Well, here’s the good news just as you’ve helped Copilot, now it’s Copilot’s turn to help you by generating measure descriptions automatically!

Creating a Measure Description with Copilot

To generate a description for an existing measure in your Power BI semantic model, follow these steps:

1. In the Model view, select a measure from the Data pane to view its properties.
2. Click the “Create with Copilot (preview)” button under the Description textbox.
   
3. Review the suggested description from Copilot, then click “Keep it” if it meets your needs.
4. The description will now appear in the Description box. You can edit it as needed.

If you update the measure later, simply select the button again, and Copilot will generate an updated description based on the changes.

2. Synonyms

Another important metadata element is synonyms. Synonyms allow you to define alternative names for columns, helping Copilot understand different ways users may refer to the same data. This ensures that when a user requests a column using a different term, Copilot can still identify and use the correct one.
Example

Suppose we have a Sales table in our Power BI model with a column named “Revenue”. In different business contexts, users might refer to revenue using terms like “Sales,” “Turnover,” or “Income.” To help Copilot recognize these variations, we define synonyms as follows:

● Column Name: Revenue

● Synonyms: “Sales”, “Sale”, “Turnover”, “Income”

Now, if a user asks Copilot: “Write a DAX query to find the total income,”

Copilot will recognize “Income” as a synonym for “Revenue” and correctly generate the sum calculation.

3. Sample values

Unlike descriptions and synonyms, column statistics are automatically included by Power BI. These statistics, such as minimum and maximum values, help define the numerical or date range of a column. Additionally, sample values provide context about the type of data stored, allowing Copilot to generate more accurate and relevant DAX suggestions.
Example

Suppose we have a categorical column called “Segment”, which contains values like: Retail, Wholesale,Enterprise. When these sample values are available, Copilot gains a better understanding of the column’s purpose.

Now, if a user requests: “Write a DAX query to find the Retail Sales”

Copilot can correctly infer that “Retail” is a category within the Segment column. As a result, it suggests an appropriate DAX formula using the CALCULATE() function with the correct filter condition.

Key Benefits of Sample Values in Copilot

● Copilot understands whether a column contains dates, numbers, or text, ensuring more accurate function recommendations.

● When a column has predefined categories, Copilot can automatically suggest filters without the user having to look up values.

● For date-based columns, Copilot recognizes the need for time intelligence functions like TOTALYTD(), DATESBETWEEN(), or PARALLELPERIOD()

Summary

Copilot in Power BI is a powerful tool for generating DAX queries, but the quality of its suggestions depends on the metadata available in your semantic model. By enhancing your model with measure descriptions, synonyms, and sample values, you can significantly improve Copilot’s ability to generate accurate and context-aware DAX queries.

● Measure descriptions provide clarity on calculations, leading to better query suggestions.

● Synonyms help Copilot recognize alternative names for columns, improving interpretation.

● Sample values and column statistics give Copilot more context, allowing it to generate precise filter conditions.

By leveraging these metadata enhancements, you can make Copilot a more reliable assistant in your Power BI workflow. Try these best practices and experience the difference!

[Balaraju]

very Informative information Abhilash, thanks.

[VannurVali]

Helpful insight, thanks @Abhilash_P .