Skip to main content

Microsoft Power BI Blog

Deep dive into DAX query view with Copilot

Headshot of article author Zoe Douglas

Product Manager
2 April, 2024

Boost your productivity in DAX query view with Copilot to write and explain DAX queries.

At Build in 2023, Christian and I showed an exciting vision demo of DAX query view and writing DAX queries with Copilot. In November 2023, the public preview of DAX query view released with native tooling for the powerful DAX queries. And now using DAX queries is even easier with the much-anticipated public preview of writing and explaining DAX queries in DAX query view with Copilot!

Let’s see how Copilot can help.

  • Type in what you would like in a DAX query and Copilot can write it for you. Such as simply showing sales by country or even all measures by a specific column, such as product. Comments are automatically added to explain each part of the generated DAX query as well as noting it was generated with Copilot with the user prompt.
  • Adjust an existing DAX query with Copilot. Add additional group by columns or otherwise adjust a DAX query already written. The bonus of this approach is you will see all changes made by Copilot with an inline diff editor, so you know exactly what was added, removed, or updated.
  • Create new measures with Copilot. DAX queries include the syntax to define a measure, and in a DAX query this measure can be Run without modifying the semantic model. So, you can create measures and try them out. Then finally, in DAX query view you can use the CodeLens to “Update the model” if you would like to modify the semantic model by adding the measure from the DAX query.
  • Find out more about DAX functions or topics while you are in DAX query view with Copilot. This explanation will try and give you an example using your semantic model: staying in context of where you are working. No longer do you have to go to a search engine to find the answer using some other model as an example!
  • Explain a DAX query already written with Copilot. Have Copilot explain step by step what is happening in the DAX query you are looking at. Your very own helper right where you need it!

Let’s see it in action.

To follow along, use the Power BI Regional Sales sample available at https://learn.microsoft.com/power-bi/create-reports/sample-regional-sales. The first thing I did after downloading the PBIX was to remove the relative month filter in the Filter pane to see data in the report.

Type in what you would like in a DAX queries and Copilot can write it for you. Here I want to explore the Product Categories a bit, so I am going to ask to see them with “Show me all product categories”.

  1. Go to DAX query view in Power BI Desktop
  2. Invoke Copilot: CTRL + I, clicking the Copilot button in the ribbon, or the Copilot button next to the line number.
  3. Type in “Show me all product categories”

  4. Send it by hitting Enter key or clicking the send button to the right of the textbox. It will then change to a loading screen.
  5. The DAX query will be generated! You cannot Run this query just yet, and in a future update the Run button will be disabled while Copilot is active.
  6. Click Keep it, and then you can Run the query to see the results.

That’s great! Now, using the Data pane / Model Explorer, I can see a bunch of measures already created in the model. Let’s see what those all look like by the Product Categories. Adjust an existing DAX query with Copilot.

  1. I select the DAX query, and again invoke Copilot. This time I use “Evaluate all the measures in this model by Product Category”.
  2. And just like that Copilot has added in all 15 measures in this model to my DAX query! I even get a diff view to show me what Copilot has altered in the original DAX query.
  3. Again, I click Keep it, and Run the query to see the results.

This has saved me a lot of time. Before I could only see that if I went to report view and created a visual, dragging and dropping all the fields, or typed it all out by hand in DAX query view.

Now let’s create new measures with Copilot. DAX queries support the ability to create measures without having to add them to the model. This useful way of creating measures means I can test it before adding to the model and I can also author multiple measures at once. For now, let’s create just one additional measure with Copilot’s help. I want to analyze the Revenue Won by Category further by adding in a % of total.

  1. I go to a new query tab and invoke Copilot (CTRL+I or the Copilot button in the editor or ribbon). This time I use “create a measure for % of total revenue won and show it by product category with the revenue won”
  2. The DAX query is generated this time with a DEFINE block for the measure I wanted to create!
  3. I again click Keep it and Run my query to see the results.  

In DAX query view when a measure is defined in a DAX query that is not already in the model, there is a CodeLens option to Update the model by adding the new measure, which you can see there between lines 3 and 4. Right in context of what I am doing I have the tools I need to get the job done.

Speaking of being in context, I can find out more about DAX functions or topics while I am in DAX query view with Copilot. The query just generated used the ALL function. Let’s see if Copilot can tell me a bit more about it.

  1. Again, I select the DAX query just created and invoke Copilot (CTRL + I). This time I ask, “Tell me more about the ALL DAX function”.
  2. Now I get back detailed explanation of the ALL DAX function, and how it’s used in the DAX query just generated!

And now I understand more about the DAX functions where I am using them and in context of the model I am using them in. I can stay focused on my work without getting distracted by different websites or videos to explain it further, and I don’t have to reach out to my friend who can explain it to me.

And speaking of that friend who could explain the DAX function to me, maybe they wrote a DAX query earlier and now I don’t quite remember what they said it did. Now I get into my last example: explain a DAX query already written with Copilot.

  1. I have a DAX query already written on a DAX query tab. For this example, I did the Quick query > Show top 100 rows on the Territories table. I select the DAX query, invoke Copilot (CTRL + I) and use the prompt “What is this DAX query doing?”.
  2. And I get a detailed explanation of the DAX query, and each of the parts in an easy-to-read format.

Again, I am still in context of what I am doing, and in context of the model I am working with. I can keep going right where I left off after getting my answer.

This feature should already be enabled in the latest Power BI Desktop version (March 2024 or later), but it can be turned on and off in File > Settings and Options > Options > Preview features section.

Some things to be aware of while using DAX query view Copilot. During public preview we will be making changes and updating the functionality and UI so these examples may return different results for you.

  • We are also parsing the DAX query returned by Copilot and performing one retry if the syntax is incorrect. In the uncommon scenario that the retry also fails a parser check, we will still return the DAX query but note that there is an issue. You can type in a new prompt or adjust the DAX query if you can see what is causing the issue.
  • As stated above, currently the Run doesn’t work until you are finished with the inline Copilot, so in a future update the Run button will be disabled while Copilot is active.
  • A final limitation we are also working on is that the inline Copilot is not aware of the previous prompt before you click Keep it. For example, if you ask it to create a DAX query to show “Sales by Year” then it generates the DAX query, you cannot simply type in “Add in product” to adjust it further. For now, you will have to click Keep it, select the generated DAX query, invoke Copilot again, and then “Add in product” will work as expected.

So, try it out today! Share your feedback and check out additional resources available below.