How to Use Natural Language to Ask Questions in Power BI — AI and Power BI — Chapter 1

You will learn how to create a Q&A visual without writing a single line of code.

Nowadays, if you are going to know about any self-service business intelligence tool, you don’t want to limit yourself to explore only data visualization. With the same thought, I explored the advanced features of Power BI and found the smart features of Power BI.

In this blog, you will learn how to create a Q&A visual without writing a single line of code.

Get Data

I consider the US Superstore dataset from Kaggle.

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
Image by Author
  • Click on Load and save data.
  • In this data model, create one date table with required columns. You can follow my blog about date table creation.

How to Create

Q&A AI Visuals of Power BI
Image by Author

First Click

If you use any of the above options, the first view will be like below.

Q&A Visual of Power BI
Image by Author

If you type your question in the text area, automatically it comes up with suggested questions like the below section of the view.

If you click on any question, the text will copy in the input area and the answer will be generated. It will happen every time of click.

Example 1: Only One Measure

Now you type one simple measure “Total Sales” or “Sales” and observe multiple suggestions are visible as the drop-down list.

One thing to remember suggested measures should be pre-calculated by you.

When you click on any one of them and answer will display in the visual area.

Image by Author

Example 2: One Measure by One Dimension

Let’s type Total Sales by Category. Here Total Sales is measure and Category is dimension value. Your aim to find the answer for categorical sales information.

After selecting the suggested question, the report displays

formatting options

Image by Author

Example 3: One Measure by One Dimension with a Filter

It’s time to explore with a filter. Now you add one filter condition in your question section and observe the answer.

Image by Author

After converting Q&A Visual to Standard Visual, you find standard visual like a clustered bar chart added filter condition whatever you have provided in the question area.

Image by Author

Example 4: Visual with Geographical information

If you type any question with geographical information, for example by city or country etc., there are two possibilities to display the answer.

In the dataset, if you have latitude and longitude information, then it will take a map view by default. Then you can type for example “by bar” to convert the chart type from the map to bar.

But in the dataset, there is only city, country information, then by default bar chart will display. In this case, you need to type “by map” and convert to map view instead of a bar.

Q&A Visual Power BI
Image by Author

Example 5: Q&A Button and Q&A Dialog

  1. Go to the Insert tab.
  2. Under the Elements section, Click on Buttons drop-down and select Q&A
  3. The Button view with all format options will display
Image by author

4. Press Ctrl+Click on the button view and Q&A Dialog will open.

5. In this box, you need to questions and find the answer accordingly. But you can’t add this view to your report page.

6. However you can add this feature to your report page.

Image by Author

Keyword Categorization

If you want to categorize based on keyword search, then there can be some possible options.

  1. With aggregate words like total, sum, count, average etc.
  2. With comparison and range related words like a top, versus, and, or etc.
  3. Query related words like sort, ascending, descending.
  4. Visual type related like bar, line, table etc, mainly related to standard visualization.

Synonyms

As you understand in your data model, if you do not have a similar name then in Q&A visual will not provide any answer.

In our example data model, we have already sales word, so whenever we are searching with sales, answers are displayed.

Image by Author

Now you type “loss”, you will not find any result. But for the profit, we have a positive value as well as a negative value. A negative value means loss related value.

How will you handle this type of situation? Let’s find out in the below steps.

Image by Author
Image by Author

After making all changes, now if you type loss in Q&A visual, you will find the answer. Though it is the combined value of profit and loss.

Q&A Set Up

Till now, you observe different questions which are mainly related to the defined data model.

If you want to add some set of questions related to your project, then you can go for the following options.

  1. Under the Modelling tab, go to the Q&A group.
  2. Three options: Q&A setup, Language, Linguistic schema
  3. The main option is Q&A setup.
Image by Author

Download

Please find the code in the below location

AI and Power BI — Chapter 1

Video

Conclusion

In this blog, we understand how natural language is used to ask any questions and how Q&A visual is providing the answers.

In my next blog, we will learn more about AI and Power BI.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: