Skip to main content

Microsoft Fabric Copilot to write DAX queries in Power BI update

Headshot of article author Zoe Douglas

We are excited to announce that writing DAX queries with Copilot can utilize semantic model descriptions, synonyms, and sample values from columns.

Microsoft Fabric Copilot helps you with DAX queries in Power BI Desktop or the browser. In DAX query view, select the Copilot button to open an inline Copilot where you can enter your request for writing or explaining a DAX query. Your request will include the context of where you are, using metadata from tables, columns, and measures, such as names and data types.

With this update, additional information is included to help Copilot understand the semantic model.

  • Descriptions are a property on the model where you provide additional information about a column, table, or measure to help model consumers when building reports. This is great for spelling out acronyms. For example, for a measure called ‘YOY Growth’ the description could be ‘Year over year growth in sales, based on same range starting 12 months prior.’ To keep Copilot response fast, Copilot can only see the first 200 characters. Descriptions can be added in the Properties pane of any table, object, or measure selected in the Model explorer, available in the Data pane of Model view or in the new TMDL view. There is also a Copilot to help write descriptions of measures in a model, which is highly recommended as consumers of the model, with or without Copilot, can only see name and description and do not see the DAX formula itself.
  • Synonyms can be added to provide alternative names or how others may reference this column. This can help Copilot identify the right column to use if the user request uses a synonym. For example, to keep the axis label shorter the column is named ‘YOY Growth’, the synonyms could include ‘Yearly growth’, ‘YOY Change’, and ‘Year over year delta’. Learn more about how to add synonyms. Synonyms can also be added in the Properties pane of any table, column, or measure selected in the Model explorer, available in the Data pane of Model view and there is a Copilot to help you add these synonyms.
  • Sample values include the minimum and maximum values of a column. This gives the context of the numerical or date range and text examples. For example, the range of quantities ordered could be from 1 to 10 or 10 to 100, and a column of state names may be entered as ‘WA’ or ‘Washington’. You do not have to add these, we will do this automatically for you.

Here I have a semantic model where I have added in synonyms and descriptions.

Let’s see the power of descriptions. The model includes a calculated table with information about the semantic model’s tables, which is a self-documenting technique. The table is called ‘xTables’ which does not convey that purpose well but does put it at the bottom of the table list, which was why it was named that way. The description for this table can give more context with ‘This is information about this semantic model’s tables.’ Now when I ask Copilot to tell me more about the tables in this semantic model, the DAX query returned utilizes that table.

Note, the DAX function INFO.VIEW.TABLES() was used in the calculated table, which can also be run as a DAX query if you have permission to edit the model. If you only have permission to view and run queries, you cannot run that DAX function and would only be able to get the information from this table.

And in the case of column name using an acronym, the description can remove ambiguity on what it may mean, and Copilot can find the correct measure to use. Here I have a measure abbreviated to ‘S/O’, which I provide additional context for with the description, ‘Calculates the average sales per order by dividing the total sales by the total number of orders.’ Now when I request ‘Write a DAX query to show average sales per order’, Copilot can find the correct measure to use.

Let’s see how synonyms can help. The model has a measure called [Costs] but it has a synonym of ‘expenses’. I can now ask Copilot using the synonym and I get the data I was expecting from the DAX query. 

Finally, let’s see how sample values can help. In my model, I can see the minimum and maximum values using the Column statistics quick query, available from the Data pane. The values for states in this model are spelled out instead of using abbreviations.

When I ask Copilot for a DAX query, I may not know this and use the abbreviation. With the sample values included with the request to Copilot, Copilot generates a query using the correct filter value, in this case, using ‘Alabama’ instead of ‘AL’ from the request I sent. It also used ‘USA’ instead of ‘America’ as that was also in the sample values for the Country column.

When I run this query, I get the results I am looking for.

For more guidance on best practices in semantic modeling to give your model consumers the best experience whether they are creating reports or using Copilot, check out Optimization for Power BI guide at power bi optimization – Optimizing for report authors and model consumers

Copilot to write and explain DAX queries in DAX query view is also using the new Azure OpenAI model. You may notice some changes from previous interactions with Copilot and an overall faster experience.

Learn more about Microsoft Fabric and Copilot for Power BI with these resources.