Discover how to write DAX queries with Power BI Copilot step by step with practical examples of prompts. Create DAX queries without being an expert!
Artificial intelligence is transforming all areas of technology, and Power BI is no exception. Power BI Copilot is the result of the integration between Copilot —an AI assistant— and Power BI The integration is aimed at helping users create and understand DAX queries more efficiently, among other features.
Power BI Copilot simplifies the creation of complex calculations in Power BI and provides detailed explanations, allowing users to maximise the potential of their data analysis without being DAX experts.
What Is Power BI Copilot?
Power BI Copilot is an advanced AI integration —specifically, GenAI— within Power BI that allows users to interact with their data in a more intuitive and efficient way.
Developed by Microsoft, Copilot uses natural language models to interpret and generate DAX queries, streamlining the analysis and reporting process in Power BI.
How does Copilot work in Power BI?
Power BI Copilot combines generative AI with Power BI data to help users discover and share information faster.
Among many others, these are some of the most remarkable features of Power BI Copilot:
- Automatic report creation: Power BI users can describe the information they need or ask a question about their data, and Copilot will analyse and extract the right data into a report, turning data into visual information in seconds.
- Improved interaction with data: Copilot helps users write code faster, as well as create dashboards and reports more efficiently.
- Question and answer synonyms: Copilot generates synonyms to enhance question and answer capabilities, making it easier to explore data.
Key benefits of Power BI Copilot
- Automation of repetitive tasks: Copilot can handle repetitive tasks such as creating measures and calculated columns, allowing users to focus on more strategic, high-level analysis.
- Clear and detailed explanations: In addition to generating DAX queries, Copilot has the ability to explain step-by-step what each query does, helping users better understand their data and how it is being analysed.
- Intuitive interface: With a user-friendly design, users can interact with Copilot through simple, straightforward commands, making data analysis accessible even to those who are not DAX experts.
For example, a user can ask Copilot to "show cumulative sales by product for the last three months" and the tool will automatically generate the corresponding DAX query.
This capability not only saves time, but also reduces the possibility of human error in writing DAX code.
Power BI Guide: Tips and Best Practices
If you want to go even deeper into the best practices for creating Power BI Reports, we invite you to download our free eBook: "21 Best Practices for Reporting in Power BI".
Power BI Guide: 21 Best Practices
Improve your Power BI repors with our guide where you will find the best practices and tips for reporting.
How to Enable Copilot In Power BI for DAX Queries
You're probably wondering how to use Copilot in Power BI, but don't worry, it's actually quite simple. The first step is to make sure Copilot is enabled in your Power BI Desktop environment.
It is important to keep in mind that in order to use Copilot, users must ensure that their workspace is running in the F64 or Premium capacity in Power BI Service or Desktop.
Here's a step-by-step guide on how to enable Copilot in Power BI:
How to enable Copilot in Power BI Desktop: Step by Step
- Open Power BI Desktop: Open Power BI Desktop on your computer.
- Options and Settings: Go to the "File" tab in the top left corner and select "Options and Settings", then click "Options".
- Activate Preview Features: In the Options window, navigate to "Preview Features" and check the "DAX Query View with Copilot" box. This will activate the Copilot capabilities in your working environment.
- Account Requirements: Make sure you are using a Power BI account that has the Copilot feature enabled. This may require specific permissions or an appropriate Power BI licence within your organisation.
Additional Settings
- Check Permissions: Verify that your account has the necessary permissions to use Copilot's advanced features. This may include administrator permissions in your Power BI environment.
- Upgrade Power BI Desktop: Make sure you have the latest version of Power BI Desktop installed to avoid compatibility issues and to take advantage of all recent enhancements and updates.
Once enabled, you can "call" Copilot by using the keyboard shortcut CTRL+I or by clicking on the Copilot button in the Power BI ribbon.
From here, you can start exploring the capabilities of this tool to enhance your data analysis and reporting in Power BI.
How to Write DAX Queries with Copilot?
Using Power BI Copilot to write DAX queries not only simplifies the process, but also improves the accuracy and efficiency of data analysis.
Here's a step-by-step guide on how to write DAX queries with Copilot:
Step 1: Activate Copilot
To get started, you need to activate Copilot. This can be done in several ways:
- Using the keyboard shortcut
CTRL+I. - Clicking the Copilot button located in the Power BI ribbon.
Step 2: Enter a Prompt in Natural Language
Once Copilot is active, you can type a request in natural language.
For example, if you need to list products and show the profits for the last three months compared to the previous three months, simply type: "list products and show profits for the last three months compared to the previous three months."
Step 3: Review and Execute the Generated Query
Copilot will process your request and generate the corresponding DAX query.
It's important to review the query to ensure it meets your needs before executing it.
Here’s a practical example of how a DAX query is generated and adjusted with Copilot:
EVALUATE
SUMMARIZECOLUMNS(
'Product'[ProductName],
"Last 3 Months Profit", CALCULATE(SUM('Sales'[Profit]), DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH)),
"Previous 3 Months Profit", CALCULATE(SUM('Sales'[Profit]), DATESINPERIOD('Date'[Date], DATEADD(LASTDATE('Date'[Date]), -3, MONTH), -3, MONTH))
)
Once you are satisfied with the query, click "Keep it" and then "Run" to execute the query and see the results.
Benefits of Using Copilot for Writing DAX Queries:
Using Copilot for DAX queries offers multiple benefits. It does not only save time by automating the creation of complex queries but also reduces the probability of human mistakes.
Additionally, it enables less experienced DAX users to create advanced analyses without having to learn every detail of the language.
Explaining DAX Queries with Copilot
One of the most interesting aspects of Power BI Copilot is its ability to explain DAX queries clearly and in detail.
This functionality is especially useful for those who are learning DAX or need to understand the workings of complex queries created by others.
How to Use Copilot to Explain Queries
To have Copilot explain a DAX query, follow these steps:
- Select the Query: First, select the DAX query you want to understand.
- Activate Copilot: Use
CTRL+Ior the Copilot button in the ribbon to activate the tool. - Request an Explanation: Type a prompt like "Explain what this query does" and wait for Copilot to process the request and generate a detailed explanation.
Practical Example of an Explanation
Suppose you have the following DAX query and want to understand exactly what it does:
EVALUATE
SUMMARIZECOLUMNS(
'Product'[ProductName],
"Total Sales", SUM('Sales'[SalesAmount])
)
When you ask Copilot for an explanation, you might receive a response like this:
- EVALUATE: Initiates the evaluation of a DAX table.
- SUMMARIZECOLUMNS: Creates a new table grouped by 'ProductName'.
- SUM: Calculates the sum of 'SalesAmount' for each product.
Advantages of Copilot Explanations
The primary benefit of this feature is that it allows users to learn and understand DAX more efficiently.
Instead of searching for external documentation or tutorials, users can now get contextual explanations they need directly within Power BI.
This not only improves the learning curve but also increases productivity by allowing a more direct and practical approach.
Copilot Advanced Features
One of the highlights of Power BI Copilot is its ability to offer advanced features that enhance the user experience when working with DAX queries.
These features not only make query creation faster and more accurate, but also help users better understand their data and how it is being analysed.
1. Syntax and Corrections
Power BI Copilot is able to check and correct the syntax of the DAX queries it generates. This is particularly useful for users who are not completely familiar with DAX syntax.
If Copilot detects an error in the query, it will automatically attempt to correct it or provide a suggestion on how to fix it. This process helps minimise errors and ensures that queries are executed correctly.
2. Inspiration Buttons
One of the most useful features of Copilot are the inspiration buttons. These buttons provide quick examples of what can be done with Copilot, such as generating a DAX query to calculate cumulative sales or creating a custom measure.
By clicking on these buttons, users can get a quick understanding of Copilot's capabilities and how they can apply them to their own data.
Practical Examples of Prompts for Power BI Copilot
Below, we provide a series of practical examples of prompts to illustrate how Power BI Copilot can be used in real-life situations.
Creating a Cumulative Sales Measure
Prompt: "Create a measure that calculates cumulative sales over time in Power BI."
Copilot will generate a DAX query that sums total sales over time, providing a clear view of cumulative sales performance. This measure is especially useful for analyzing trends and making future forecasts.
Generating a Customer Profile Report
Prompt: "Create a report summarizing the profile of customers who have visited our resort in the last 12 months, including a breakdown of new vs. returning customers, demographic data, length of stay, and types of customers received."
This prompt enables Copilot to generate a detailed report that segments customers based on various criteria, providing a deep understanding of the customer base and helping to identify marketing and sales opportunities.
Explaining a DAX Query
Prompt: "Explain what this query does."
This prompt is invaluable for those learning DAX or needing to understand complex queries created by others. Copilot will break down the query step-by-step, explaining each function and its purpose in the context of the query.
Other Best Practices for Power BI
Before you go, remember you can download our eBook where you'll find 21 best practices to optimize your Power BI reports.
Power BI Guide: 21 Best Practices
Improve your Power BI repors with our guide where you will find the best practices and tips for reporting.
Conclusion
Power BI Copilot is a revolutionary tool that transforms the way users interact with DAX in Power BI. Its ability to generate and explain queries efficiently and accurately makes it an invaluable resource for data analysts of all levels. Additionally, advanced features like syntax verification and inspiration buttons make it more accessible and easy to use, even for those who are not DAX experts.
If you haven't explored Copilot yet, we encourage you to enable it in your Power BI environment and start experimenting with its powerful functionalities today. Not only will you save time and reduce errors, but you will also significantly improve the quality of your data analysis and data-driven decision-making.
