I have been thinking about a mechanism to generate measures from numeric columns on Power BI data models. Of course, we can use Tabular Editor, but it requires some scripting, which is all right. However, the more advanced our requirements get, the more complex the C# script. In real-world development scenarios, it does not make sense to blindly create measures for all numeric columns, such as the key columns used to define relationships between tables, making C# scripting a bit more complex.
In this blog and accompanying YouTube video, I explain using Copilot within Power BI Desktop to create measures from numeric columns. This feature represents a significant advancement in Power BI’s capabilities as of April 2024, enabling data analysts and BI professionals to streamline parts of their data analysis tasks.
As explained in a previous posthere, we first need to enable Copilot on the Fabric Portal. Please note that Copilot in Power BI Desktop requires eitherPower BI Premium Capacityor AT LEAST anF64 Fabric Capacity. Unfortunately, Copilot is NOT available onPPU,Embedded capacities, Fabric capacities smaller thanF64and Fabric Trial (FT)capacities.
We also need to have the latest version ofPower BI Desktopinstalled on our machine. With that, let’s begin.
Here is the video on YouTube where I explain the same thing in less than 5 min. But if you are after more details, continue reading.
Introduction to Power BI and Copilot
As Power BI evolves, it incorporates more sophisticated AI-driven capabilities that simplify various aspects of data analytics. The integration of Copilot in Power BI Desktop enhances user interaction with data in many ways. Our focus on this blog is specifically using Copilot to create simple yet crucial measures based on numeric columns that previously required manual effort.
Use Copilot for Measure Creation
Using Copilot is straightforward and demonstrates impressive intelligence in its operational logic. The following steps explain how to do so:
Step 1: Import and Prepare Your Data
Start by signing into Fabric using your account, then importing your data into Power BI Desktop, ensuring it is clean and well-structured to maximise Copilot’s effectiveness. I explained in a previous post that the better the data model, the more accurate the results Copilot can generate.
Step 2: Use Copilot in the DAX Query Window
- Click the DAX Query tab
- Click the Copilot button from the Home tab from the ribbon
- Type in your prompt as “Create measures for all numeric columns in the “Internet Sales” table, except key columns used in relationships. Use SUM function to create the measures.“
- Click the Send button
- If this is the first time you are using Copilot on this file, you will be prompted to select a Premium Workspace; select the desired Workspace from the dropdown
- Click OK
- Review the DAX Query suggested by Copilot appearing on top of the prompt section
- Remove the measures that do not make sense; for example, summing up the values representing percentages does not make sense.
- For more visibility, we can click the Run button to see the results in the Results section at the bottom of the page
- Click the Keep it button which adds the query with the changes made in the previous steps
- Click the Update model: Add new measure option appearing on top of each measure to add the measure to the data model
- Click the Update model button to confirm the change
Repeat K and L to add the other measures to the data model and that is it.
Step 3: Refine Copilot Prompts
As you may have thought, we do not necessarily need to mention the table name in our prompt, especially in smaller and simpler data models. But, for complex data models, refining your prompt to specify particular tables or data types can lead to more reliable outputs from Copilot.
Step 3: Review and Implement Measures
Carefully review the generated measures to ensure they meet your analytical needs, adjusting or omitting as necessary.
Conclusion
These days, AI tools like Copilot in data platforms like Microsoft Fabric are revolutionising data analysis processes. These tools not only streamline procedural tasks but also allow data analysts to focus more on strategic data analysis. The expanding capabilities of Copilot promise to offer more advanced, user-friendly features moving forward. Even now, there are many other use cases where in Copilot saves a lot of time and effort, which I will cover in the next blogs/videos. So stay tuned.
As always, feel free to leave your comments and ask questions,follow me on LinkedInand@_SoheilBakhshion X (formerly Twitter).
