Get Deeper Insights: Prompt AI directly from your Power BI Interactions

Artificial Intelligence is finding its way into business and I thought I’d share one way for you to benefit from AI if your company uses Power BI for analytics. In this example we use ChatGPT but the principle applies to sending prompts to any Artificial Intelligence.

ChatGPT was released on 30 November 2022 and became the quickest app to reach a million users -- a feat it achieved within five days. Part of this virality can be attributed to its exceptional, almost human-like performance, despite no one being sure why ChatGPT is so good at it.

“A question many business leaders are asking is:
‘how can we merge the the virtues of AI with our business data and macro economic data to enrich our decision-making for growth?’”

If your company uses Power BI and you’d like to dial up your ROI on Business Intelligence with an injection of Artificial Intelligence, then this article is for you.

With our Power BI custom visual, you can communicate with ChatGPT right from your dashboard and gain deeper insights instantly. This article shows you how.

ChatGPT Prompts

At its core, ChatGPT is a chatbot. It takes inputs in text format and produces output in the same format (although you can get videos and graphics with extensions).

Here, the input matters the most. The inputs, in the AI context, are called ‘prompts’.

Prompts are the way you communicate with ChatGPT, telling it what you’re interested in or what task you want help with. And like a well-paid employee, ChatGPT obliges and gets the job done for you.

Prompts are so important that there’s a new skill set that has evolved altogether: prompt engineering.

These engineers are tasked with crafting prompts to get the desired results.

“But what if you could have prompts automatically generated and sent to AI purely based on your interaction with your Power BI dashboards ? ”

AI Prompts from your Power BI Dashboard

Power BI was a game changer when it was launched in July 2015. It is a popular business analytics service provided by Microsoft that empowers users to visualize and share insights from their data in the form of charts, graphs, maps, tables, etc.

Equally empowering were the dashboards that provide a real-time, consolidated view of key metrics and KPIs.

Fast forward to 2024, and ChatGPT holds the same game-changing potential.

You can go beyond data visualization and get insights, pointers, and conclusions without any manual effort. Of course, these may or may not be actionable at first, but you’ll at least get something to work on.

The big question is, how do you prompt AI from your Power BI dashboard?

As of the date of writing this blog, there’s no direct way to link the two.

As replied by ChatGPT, you can leverage APIs. But that method comes with its own technical expertise.

“To make this process simpler and faster, you can use Power Custom Visuals instead.”

FreshBI has been developing such Custom Visuals (and also Power BI Template Apps and Custom Connectors) for years now. And the recent Power BI Custom Visual is all about combining the best of both worlds: Power BI and Artificial Intelligence.

Let’s demonstrate the capability with an example.

Case Study: Prompting Power BI to Get Answers from ChatGPT for a Doughnut Company

Here is a dashboard of a fictional doughnut company reporting their sales for three consecutive years (’21, ’22, and ‘23):

The company sells different types of doughnuts. The rows on the left show the sales and quantity sold for each doughnut type.

While the dashboard offers factual data about the business, it doesn’t give any valuable insight about the micro and macro economics impacting the store.

For that, you need to go beyond the Power BI dashboard.

By merely clicking on the year, you send a prompt to ChatGPT. The prompt in the above example reads,

“Can you make some observations about the following data? In 2022 our top sellers were Plain: at $3,863.00 Old Fashioned: at $3,991.25 Chocolate: at $7,378.00 Holes at: $283.90 Strawberry: at $6,394.00 Stuffed at $10.947.00. In 2021, our top sellers were Plain: at $2,768.00 Old Fashioned: at $4,018.75 Chocolate: at $6402.00 Holes: at $300.30 Strawberry: at $7,324.00 Stuffed: at $9022.50”

Here the prompt directs ChatGPT to derive insights from the dataset provided. Notice how you didn’t have to leave the PowerBI dashboard to interact with ChatGPT.

Based on your interaction with the dashboard that renders the dynamic input and data provided, ChatGPT will generate the response and we render it into the Power BI Custom Visual.

Take note of the word “dynamic” because the prompt changes when we click on a different year (or any other dashboard interaction).

In this case, we clicked on the 2023 button, and the prompt (the dataset, to be more specific) changed accordingly:

Because there’s a change in input, there’s a different response, too. As you naturally interact with your dashboard you can be an passive observer of the data and gain insights instantly.

Beneath the surface, there’s a DAX code seen below that generates the prompt:

This Data =
"In "&SELECTEDVALUE('Calendar'[Year])&" our top sellers were " & CONCATENATEX(FILTER(VALUES('Doughnut Data'[Desc]), [CY_Sales_Amount] > 0), 'Doughnut Data'[Desc] & ": at " & FORMAT([CY_Sales_Amount], "Currency") & " ") & ". "
& If([PY_Sales_Amount] > 0, "In "&SELECTEDVALUE('Calendar'[Year]) - 1&" our top sellers were " & CONCATENATEX(FILTER(VALUES('Doughnut Data'[Desc]), [PY_Sales_Amount] > 0), 'Doughnut Data'[Desc] & ": at " & FORMAT([PY_Sales_Amount], "Currency") & " ") & ". ", "")

The code gets all the selling doughnuts for the year and their respective sales numbers. Non-selling items are excluded. The code then repeats the process for the previous year.

That’s how we get the 2023 and 2022 doughnut sales data inside of the prompt.

The code will not generate the previous year’s data if there isn’t one. So, for the above doughnut example, there will only be the sales data for 2021 inside of the prompt if you click on 2021.

This is just an example, and the prompt can be anything. To get the best results, it’s recommended to make your prompts intuitive and to the point. By adjusting the code, you can design the prompt for your needs.

Once the code is set, it’s possible to repeat the process over and over again for different dataset.

Getting Responses from ChatGPT

The Power BI Custom Visual - which you can download here>> - sends the instructions over to ChatGPT for processing and retrieves the response.

The response is displayed in the Power BI Custom Visual under the ‘Observations’ section in the bottom-right corner of the dashboard.

The changes in the Observations section change as you change the dataset instantly. This enables you to derive insights right from the Power BI dashboard.

Note that the response quality is tied directly to the prompt quality. How well you prompt would determine how good the insights are.

Conclusion

Business Intelligence is evolving from merely rendering factual data to cross-functional, macro and multi-source interpretation of the data to offer insights and actions that are geared to business growth.

A thoughtful, targeted investment into organization-wide Business Intelligence can drive innovative decisions, get your business ahead of the competition and sustain your business an unfair advantage in your industry.

The approach above to injecting AI into your Business Intelligence has the advantage of simplicity and consequently, adoption that is incremental and scalable.