Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Power BI Q & A Example and Demo


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

How can I use the Power BI Q & A? What are some helpful tips for using it? Are there any limitations? What sort of visualizations are available? Can I pin the visualizations to dashboards?

Solution

One of the neat features that is available in the Power BI tool set is the Q & A feature. This feature matches our natural curiosity and questioning techniques with a powerful natural language query functionality. The Q & A function provides the method to ask questions of your data, and based on the words and language used get pertinent and appropriate data and visualizations. Power BI uses its powerful backend processing to pick out key words, select a dataset, and display a specific visualization based on its best guess for an appropriate visualization. This whole process is done on the fly and in real time; as you change your question, the selected data and visualization also change dynamically.

Power BI Q & A in Every Way

Power BI Q & A searches through all the datasets, reports, and visualizations on a selected workspace to come up with the best results to return. Thus, as you formulate your question, the answer will change as more and more details are typed. The Q & A is smart enough to change as you change what you are interested in querying. You can quickly adjust the direction of your questions and interact with the data. However, there are things we can do both in our dataset and dashboard design and when using the Q & A functionality to query the data. The behind the scenes work looks for patterns and key words that help in the selection of data points and visualizations and the process happens all rather quickly.

So let's get started on using the Q & A functionality; of course there are some caveats and we will discuss those near the end of this tip. Furthermore, we will cover a few basic design techniques in this tip, but will follow up with a second tip on more advanced design methods and another tip on using the Cortana search functionality.

You will first need to login to Power BI, https://powerbi.microsoft.com/en-us/. Once you are logged into PowerBI, asking a question in the Q & A text box is as simple as starting to type. You, however must have an available dashboard and permissible dashboard selected. As shown below, I have the Customer Profitability dashboard selected and the Q & A textbox is readily available.

Q & A screen in Power BI

However, if you attempt to perform a Q & A on either report or datasets, you will see that you cannot complete a Q & A on each.

No Q & A in Power BI

No QandA on Dataset in Power BI

Additionally, you may notice some dashboards do not offer Q & A functionality. Several reasons cause this situation, but the most common reasons center around trying to Q & A a dataset where the data is not housed within Power BI. In the two below examples, the first is a direct query dataset while the second is a "shared" content pack which retrieves statistics directly from Bing. In both these cases, the data does not reside on Power BI, so unfortunately at this time, Q & A is not available.

Power BI Dashboards setting

Q & A not available in Power BI

Furthermore, for those dashboards that are "eligible" to use Q & A, you can actually turn Q & A on and off, as displayed below.

Show the Q & A search box on this dashboard

Now using the Q & A is as simple as starting to formulate your question and begin typing. You will notice in the below screen print that as I typed gross margin, Power BI suggested "show gross margin". You will also notice that Total Gross Margin shows as the suggested Card visualization. Finally, the source of the dataset shows at the bottom of the visualization.

Show Gross Margin

In the next screen print, illustrated below, we added "show" as the beginning and added "by region" at the end. The visualization immediately changed to a column chart showing the gross margin by region.

Show the Gross Margin By Region in a Column Chart

However, if we type a question which lacks a matching element or pattern on our dashboard, we receive a warning message that an answer could not be found.

Sorry I wasn't able to find a good answer

Alternately, suggested questions are presented, and you can simply click on one the suggestions to see the answer to that question.

Click on one of the suggested posts

The results of this suggestion are shown below, and illustrate another excellent point about the question process. We not only can suggest what data we would like to see, but also can reference a specific visualization in our question, a line chart in the below example.

Reference a specific visualization

We could easily switch the line chart to a column chart. Furthermore, we can pin our new visual, based on the question we asked, to a dashboard by clicking on the Pin Visual button.

Pin Visual button

We are now given the option to add the visualization to either an existing dashboard or to create a new dashboard. If we select the existing dashboard, then we can select the dashboard where the new visual will get saved.

Pin to dashboard for the visual to an existing or new dashboard

In the above example, we selected to add the visual to an existing dashboard, with the final result shown below.

New visual on dashboard is displayed

Now that we have many of the basics defined, we can discuss some of the more advanced ways to use the Q & A function. One of best ways to get answers that are correct in both result and in presentation is to select the words in the questions very specifically. For instance instead of using only "revenue", we should type "show year, customer name, and revenue for 2013 and 2014 on table". As you can see in the below screen prints, the first question of just "revenue" provides just a total revenue Card visualization, but in the second screen print we use words to describe what I want to show along with a criteria and finally a visualization. We subsequently get the specific results we are looking for. Also notice in the second screen print, that Power BI "rephrases" your question in the standard format; it changed the "for 2013 and 2014" to "where year is 2013 or 2014" and "on table" to "as table". It is a good idea to pay attention to these suggestions and adjust your question format to match those key words in the future.

Poor question asked with for total revenue

show year, customer name, and revenue for 2013 and 2014 on table

To write questions that will return accurate results, we would want to do several of the following:

  • Use the word "show" and then tell the question the specific column names that you would want returned. Of course knowing the dataset helps to know the appropriate names to use. For these items, you can also use aggregation suggestions such as total, average, number of, or count. (see first screen print below)
  • Use the word "where" and then tell Power BI the column name and what criteria you would like to return. Alternately, you can use key words such as: "is", "between", "for", or "equals".
  • Use the word "as" and then a visualization type to tell Power BI what visualization you would like the data displayed on.
  • Use the words "sorted by" plus a field to tell Power BI how you want the results sorted. Note, many of the visualizations also give you the ability to sort within the visualization itself. (see second screen print below)
  • For date oriented fields, use key words such as "this month", "before", "after", or "last year".
Count of customers

Sort option available for Total Revenue Column

Power BI is also smart enough to correct spelling errors, as illustrated below. The word "revenue" intentionally has an extra "w" added to the end of the word; Power BI corrected it to revenue. You will also notice I added the word "balloon" to the question; although it is a bit hard to see in the screen print, that word is grayed out. Power BI dims a word when it cannot find a correlation or matching pattern in the dataset and thus ignores that word.

Auto correct and ignore with text in gray

We have discussed many ways to make your questions in Power BI better, but on the design side there are also some basic items that help to make the questions better. These items include

  • Use accurate and descriptive names (for all objects!).
  • Make sure your data types are correct, especially for the normal culprits such as dates and those pesky values that look like numbers, but really are not.
  • Turn off aggregation for those fields that are numbers, but do not need to be summarized (month numbers for instance).
  • Each table and field should cover just one entity and attribute item (normalization).

These items are just the start for designing a good dashboard for Q & A. My next tip will cover more advanced methods of designing your Q & A strategy including using default fields and labs, adding synonyms, designing default questions, and using advanced phrasing techniques.

Conclusion

The Q & A functionality in Power BI is quite a feature for end users. It makes addressing specific questions about the data easy to ask and easy to see results. The functionality analyzes your natural / normal question and behind the scenes returns data based on the question and use its best guess of the appropriate visualization to display the results. You can also specify the type of visualization you would like returned. Furthermore, as you type your question, Power BI interactively returns the resulting visualization while changing the results as you type. All at the same time, it offers suggested, auto completed questions that can also be selected. As you work with the Q & A functionality more and more, you will see certain patterns of words that are helpful to use in your questions. Phrases such as between, show, and sorted by, act as key word qualifiers for Power BI to use for filtering and formatting the results.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools