Power BI has a number of great ways to integrate Artificial Intelligence into reporting dashboards. Power BI AI can answer questions about data, create advanced AI visualizations and auto-generate full dashboards. We cover the current and upcoming AI features of Microsoft’s premier business intelligence tool.
Ever since ChatGPT broke through as pop culture phenomenon businesses are rushing to integrate AI into their software.
Let’s take a look at the current state of AI tools that are already built into Power BI and what is coming in the near future.
Power BI has AI features to help users explore data, including Key Influencer, Decomposition Tree and Smart Narrative visuals. Developers can use AI to create Quick Measures, Draft Dashboards, and Columns from Examples. Microsoft’s upcoming Copilot feature will expand Power BI’s AI capabilities even further.
Table of Contents
Advanced AI Visuals in Power BI
Power BI has 4 different AI powered visuals that are separate from the regular visuals in Power BI. They can be accessed from the Power BI ribbon under Insert > AI Visuals.
Each one is designed to help users explore their data in different ways.
- Q&A – Allows you to use plain language to ask questions about data.
- Key Influencers – Helps identify factors that drive a metric of interest.
- Decomposition Tree – Explore data across multiple dimensions and easily drill into details.
- Smart Narrative – Summarizes data and places insights into plain language.
Let’s look at each one in more depth. We are going to save Q&A for last, because it has some of the biggest changes coming.
Key Influencers Visual in Power BI
Key Influencers allow you to add a number of different criteria into the visual to explain a key metric. In our example, we want Power BI to analyze Total Sales and explain what causes sales to increase.
We add all of the different factors that we have data for into the Explain by section.
The AI model behind key influencers will only surface insights that it believes to be statistically significant.
In our case we added 5 different criteria, but it only determines that cookie sales increase as the temperate rises. More interestingly, Power BI determines the numeric price sensitivity when prices change.
The visual can take some experimentation, and not all data sets will have enough information for Power BI to determine key influencers. However, it is especially useful for AI sales analysis and comes as part of a low cost, low code solution.
Similar solutions can costs thousands of dollars per month to achieve similar results.
Decomposition Trees in Power BI
Using a decomposition tree allows users to explore data throughout a preset hierarchy. It’s an interesting way for users to drill down into their data.
In this example, we start with Total Sales.
Each column can be expanded into the next sub-level.
For example, we can identify that Sunday has the lowest sales out of every day of the week. From there, we can expand it to explain the Sundays with the lowest sales based on Average temperature. It becomes evident that the lowest temperature days are the ones that have the lowest sales.
Each of the blue bars presented can be clicked on. The visual will automatically filter and re-adjust lower levels of analysis for the selected portion of the visual.
Smart Narrative in Power BI
Smart Narrative uses AI to tell stories about data. The results can range from incredibly impressive to underwhelming based on your data.
To setup a Smart Narrative, you have to first create a separate visuals.
The smart narrative tool works in conjunction with other visuals, to help explain what users are seeing. In the example below we can see that Total Sales are trending down over the last year.
Developers can either choose to leave the visuals in their dashboard, and they will automatically summarize data as the Power BI dataset is automatically refreshed. They could also hide the visual and use it as a starting point for internal analysis.
Q&A with AI in Power BI
The Q&A Visual in Power BI is one of the most interesting and promising ones available. Previously, it’s been limited to describing simple scenarios such as sales for a specific month. It sends the request to the Power BI Service and returns a custom visual based on the question being asked.
This enables non-technical users to get more out a reporting dashboard and circumvents additional development requests going from business users to Power BI developers.
For the visual to work, Q&A must be enabled on the Power BI Service. If it does not work, reach out to your Power BI tenant administrator to enable it.
We saved Q&A for last, because Microsoft has invested over $10 billion in OpenAI, the creators of ChatGPT and has been aggressively integrating ChatGPT into their entire office suite of products, including Power BI.
Microsoft Copilot is bringing ChatGPT to Power BI
Microsoft has slowly been adding new features into Power BI. Fabric and OneLake are two of the most promising Power BI features to help consolidate data pipeline and data model development.
On the end user and dashboard development side, Microsoft is planning to enable people to ask questions about their data, and even use natural language processing to convert requests into dashboard elements.
You can view Microsoft’s vision of the future of Power BI in the following demo video.
These will be some very powerful and useful features. However, we expect that it could take years to fully see Power BI’s full potential with ChatGPT technology.
It takes a long time to collect enough data for the AI models to understand what people are asking and each business that utilizes it will have a slightly different nuanced context of questions being asked.
Using AI to Recommend Dashboards in Power BI
Users that already have datasets published to the Power BI Service, powerbi.com can ask Power BI to auto-create a report. Microsoft sees generative AI as a way to create first drafts of documents, or slide decks. Power BI is no different, and the feature is highlighted as part of the Copilot demo.
After opening a dataset from powerbi.com navigate to the + Create a Report option and choose Auto-create
You have the option to choose subsets of data, in our example we let it auto-generate based on a simple dataset about cookie sales.
It’s not a bad starting point. Power BI automatically created a title, some KPI examples, along with a Q&A visual tied to another chart.
We do have to wonder why it created a number of different bar charts where the data isn’t that much different.
Keep in mind that this is a starting point of AI creating dashboard. As Microsoft improves the training dataset, it’s likely that this will improve. It could potentially even create or recommend dashboards based on elements of other dashboards within your organization.
Using AI to Create Column from Example
Use create a Column From Examples in Power Query to quickly and easily add a new column to a Power BI dataset. Available under Add Columns > Column From Example, it allows you to feed example results into a separate column. Power BI will detect the pattern and generate the remaining rows.
Once you click Column From Example, you can choose a column or multiple columns to apply the examples to.
On the right side of the screen, you can begin to type part of text or the new pattern that you want as the result. In this example, we point at an Address column, and type the name of the city. Power BI detects the pattern and automatically extracts the city.
For more advanced patterns, you may need to fill in multiple rows to give Power BI more hints as to what the end result should look like.
While it does not work all of the time, it is a nice time saver when it does work.
Create Quick Measures with Copilot and Power BI AI
One of the most exciting features of Microsoft Copilot that is already available to users is the ability to generate DAX Quick Measures by describing the measure that you are trying to create. While quick measures have been around for a while, Copilot converts natural language descriptions of measures into auto-generated DAX formulas.
To utilize the feature, Q&A along with Copilot suggestions have to be enabled by your Power BI admin at the tenant level. You must also be logged into the Power BI service. An internet connection is required to send the request to Microsoft and receive a response.
Quick measures along with ChatGPT’s ability to generate code has been pretty impressive and continues to improve. We expect this feature to continue to become more useful and generate increasingly complex DAX formulas.
Recent testing shows that it works best with simple measures and struggles with complex DAX.
Python and R integration with Power BI
Microsoft publishes a Tutorial on How to Use Cognitive Services in Power BI that mentions a series of AI features that can be invoked from within Power BI. We generally don’t recommend the approach because it sends data to Azure cognitive services using R or Python.
Features include
- Text Analysis
- Image Tagging
- ML Modeling
While in some cases it could be useful, we prefer to keep coding to a minimum when using no-code and low-code tools.
Power BI is primarily designed as a way to visualize data, and while some functions like text analysis can be useful for dynamic sentiment analysis or generating word clouds functions that require actions like image tagging or ml training would be better suite d for Power Automate.
The following video provides an overview of AI Builder when used in conjunction with Power Automate. It is a low code AI solution for document processing, analysis, and building machine learning models that goes far beyond the ML tools available in Power BI and requires less code to setup.
Conclusion
Power BI has a number of AI features available today, and many more on the way. Developers can use AI to write DAX formulas, create custom columns, and even create a full dashboard as a starting point. End users can take advantage of AI backed Q&A or a number of other advanced visuals to surface key insights.
As Copilot evolves, it will enable more complex questions to be asked and answered. Auto-generated dashboards will be more to the point and surface key trends that business users are likely to see. It may even be possible for business users to use AI to build dashboards without the need for specialized developer skillsets.