Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Power BI Advanced Q & A Techniques


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

Problem

How can you enhance the Power BI Q & A functionality?  Are there any advanced techniques to make the data interaction any easier?  I need ways to enhance the accuracy and efficiency of Power BI Online Q & A queries.

Solution

In a previous tip we covered the basic Q & A functionality that is included by default within Power BI Online and Power BI Desktop.  Some basic techniques that can be completed to assist Q & A in retrieving your intended data include:

  • Using specific key words in your questions such as "show", "by", "where", "as"
  • Utilizing the auto complete / auto suggestion functionality
  • Specifying the visual you would like to see
  • Using good naming conventions

These techniques are great, but to truly utilize the Q & A functionality, you can enhance the process by using some advanced methods to make the Q & A process even better. Some of the more advanced Q and A functionality is designed to help Power BI make interaction with you data easier and more analysis friendly.  It means that users can get the data they want in the format they want and most importantly get the correct answer / data in a timely manner. Some of the advanced features and functionalities include the use of synonyms and adding featured questions. These methods are all items that dataset designers would utilize as opposed to items that could be implemented by Dashboard users. Let us get started on using these features.

Advanced Power BI Q & A Techniques

Save Featured Q & A Questions in Power BI

The first advanced technique is to save specific questions that will be offered as suggestion to Power BI users. In order to save a featured question for use with this functionality, you must first create a template question to use. The template then gets saved to a master set of questions that will be offered to all future Power BI users who are asking questions within this dataset.

Save Featured Q&A Questions in Power BI

To create the template question you simply ask a question using the Q & A functionality. The key is to use a quality statement with well-chosen key words.  In the below example, we ask for the gross margin and gross margin % by executive, sorted by gross margin and finally shown as a table.

Template for Featured Q&A Question in Power BI

Adding a question in Power BI Online starts with going to Gears-Setting Button > Settings > Datasets > Add a question. Next you fill in the question you have designed and click apply.

Add Question to the Featured Q&A Questions in Power BI

Now when the user clicks in the Q & A box or clicks on suggestions, the "saved" featured question(s) is presented to the dashboard user. You can create multiple featured questions for each dataset.

Featured Question Displayed in Power BI

Featured Questions in Power BI Shown in Suggestions

Synonyms in Power BI Q & A

Additionally as you work with datasets, you also would want to set appropriate synonyms.  Synonyms work very similar to renaming columns, except the original column name can be retained. As shown in the first screen print below, we must add synonyms not in Power BI online, but in our original data set within the Power BI Desktop. The default synonym for each column in a dataset is the column or field name. To adjust synonyms for a field, go to the relationships tab and then click on a table which contains the field(s) that need synonyms.

No Synonyms for Origanization Name in Power BI Q&A

As an example, maybe the Organization Name field in the above illustration actually has a meaning of Region Name, Subdivision Name or Geo Name. If we attempt to use Region Name in the Q & A field, as shown next, a completely different field is displayed in the visualization.

No Synonyms for Q and A in Power BI Q&A

In order to address this situation, we can add as many synonyms as necessary to the synonym list. In the below illustration, we add Region Name, Geo Name and SubDivision Name to the synonym list.

Assign Synonyms for Organization Name to include Region Name, Geo Name and SubDivision Name in Power BI Q&A

Now when we type Region Name or Geo Name in the Q & A box, Power BI immediately recognizes this term and equates it with the Organization Name field. This functionality is extremely useful when interacting with a dataset that contains fields that have different names within your organization. One caution about synonyms; as they are added to your field properties, you need to be sure they do not conflict with other names that are used in your dataset. These type of conflicts could "confuse" the Q & A functionality which in turn may provide data results that are not as expected.

Region Name shown as a Synonym in Power BI Q&A

Geo Name Shown as a Synonym in Power BI Q&A

Do Not Summarize in Power BI Q & A

In a similar manner, often when you load a dataset into Power BI, you will have "numeric" fields which are not truly fields that should be summarized (or averaged). In these instances we would want to "turn off" summarization for these fields as it has no meaning. Thus, as shown in the below example, you would select the field in question and then change the Default Summarization to Do Not Summarize. Now the Q & A functionality will see this column is non-additive and not include a summary value (and also use it an attribute value and not a fact measure value).

Default Summarization for the CreditCardID set to Count in Power BI Q&A

Set the Default Summarization to Do Not Summarize in Power BI Q&A

Data Category Setting in Power BI Q & A

In a similar fashion, Q & A makes use of the Data Category setting to determine how to best show the results of a question. Zip / postal code is perfect example. As shown in the below figure, postal code is initially set as Uncategorized.

Data Category set to Uncategorized for Postal Code in Power BI Q&A

This means that the Q & A function does not realize that the field is geographic / location related and thus when requesting the product cost on a map by postal code, nothing is shown.

Since Postal Code is not set to a geographical type no data is displayed in Power BI Q&A

However, we can set the postal code field to a Data Category of Postal Code.

Set the Data Category for Postal Code to Postal Code in Power BI Q&A

Now asking Power BI the same question, we see a bubble map created (of course we would likely need to zoom in on this map).

Data Shown by Postal Code in Power BI Q&A

Conclusion

The advanced Q & A techniques in this article provide methods to enhance the accuracy and efficiency of Power BI Online Q & A queries. The synonym functionality provides a way to map non-descript field names into names that are meaningful to the users who will be querying the Power BI dashboards. Furthermore, setting the data category and default summarization allows Q & A to recognize the type of data that is being requested. The data categorization additionally permits Q &A to use the most appropriate visualization based on the requested data and requested visualization.  All these items help to make Q & A more intelligent in the data that is selected and returned and certainly in how the data is presented and displayed to the Power BI User. The Power BI Excel tool also includes phrases and default fields functionality for Q & A; I am hoping this functionality will also come soon to Power BI Desktop and Power BI online.

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


 









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