Power BI Natural Language Q&A looks great in demos; mind blowing in fact. Just type in any question and get your answer right away! Can’t get any better, can it? Here’s me showing Q&A in my last webinar:

However, if you are anything like me, you would be a little skeptical of demos. And if after watching the demo, you uploaded your model in Power BI and started typing in questions, you would find that it’s a hit or miss. In this article I would share some tips with you to make Q&A really work for you and your team. Let’s go!

1. Build a Q&A Specific Power BI Model
2. Identify Top Question Terms and Then Make Sure They Work
3. Learn how to ask “nicely”
4. Encourage Adoption of Q&A

1. Build a Q&A Specific Power BI Model

We are HUGE fans of single source of the truth. There should be one model and ALL reports should be connected to that model. One Model that contains your Sales data, Finance data, Marketing data, Social data etcetera, gives you the opportunity to show these metrics side-by-side and even define calculations (DAX Measures) which go across data sets. For example – What % of Sales Increase came from the Marketing Campaign targeting our Facebook followers? Oooh……how fun!

image
One Power BI Model to power all your reports

So our advice to build a separate Power BI Model for Q&A would seem to go counter to that. But it isn’t. I’ll explain in a bit. But first…
Why do we recommend a separate model for Q&A?

Well, any production BI model we build, even the ones on the smaller side, end up with 50+ measures. Larger models may have hundreds of measures. All these measures are not superfluous, they are needed for all the rich reports and dashboards we create. Measures are the magic pixie dust of Power BI. So we believe in sprinkling them liberally Smile

Measures are the magic pixie dust of Power BI. Sprinkle Liberally!

image
We often create hundreds of Measures in our Power Pivot/Power BI Data Models

By creating a separate Power BI model for Q&A we can:-

  • Limit the Q&A model to only include key measures, tables and fields – thus easily optimize it for Q&A
  • We can make the above and other needed changes without worrying about impacting all the existing reports and dashboards connected to our production model

You: Okay Avi, I got all that. But what about having a single source of truth? You want us to just ignore that.
Me: Hakuna Matata Mate!

image

Single Source of Truth

The trick here is to build the Power BI Q&A model off of your production Power BI model! That way your one Power BI model is still the single source of truth. See, no worries!

image
Your One Data Model is still the single source of truth

At present, we are able to do this easily because our Data Model is hosted on an SSAS Tabular server. We can easily connect to it from another Power BI Desktop file. Connecting to an Excel Power Pivot/Power BI Desktop or Power BI.com hosted model may be tricky. If you have attempted that, do leave a comment with the approach that you tried.

2. Identify Top Question Terms and Then Make Sure They Work

At one point, working with a Finance team, I had just finished building all the basic measures – Actual, Budget, Forecast, Variance etc. With a proud smirk on my face, I asked the customer to go ahead and type in any question into Q&A.  The user went ahead and typed: “How much travel budget is remaining for Q4?”

How much of my travel budget is remaining for Q4?

Doh! My model sure wasn’t ready for that. The message is: You really need to work with your users to identify their frequently asked questions. The challenge is, if you ask your users for their top 5 questions, they may give you a list like this:

image
Top 5 questions or just Top 1?

So instead of top 5, I just ask them for a list of, say their top 20 questions (you can go larger if you like, can also solicit questions from multiple people). Once I have their list of questions, I determine the top question terms. I may come up with something as below. Note that the terms below can be combined to ask hundreds of different questions!

image
Top Q&A Terms that should be recognized

Synonyms: Note above that I record whatever “Synonyms” I may need to create in the model. You can access synonyms in Power BI Desktop, in the Relationship view by selecting Modeling > Synonyms.

3. Learn how to ask “nicely”

When you talk to Power BI in the Q&A window using natural language, you might see something like this:

image

Underlined terms indicate the terms that Power BI understood

In my experience, a lot of fancy phrasing (important for human communication) is superfluous for Q&A. You can use “Please” and all the other magic words but it won’t make much of a difference to Q&A.

Thus I have trained myself to be succinct in my Q&A questions. You may find the same to be true. This is my typical format of asking questions in Q&A:

image
Typical pattern I use when asking questions

In turn, I have also nudged all my users to ask questions in the above pattern. You can do that by actively training them on this and/or set all your Featured Questions in the above pattern (Featured Questions covered later in article).

Another cool tip to learn and to teach your users is to check how Power BI actually interpreted your question.

image
Check to see if Power BI interpreted your question correctly

4. Encourage Adoption of Q&A

If a tree falls in the forest, does it make a sound? If Q&A is not being used, is it of any use? We have noticed that there is some hesitation initially for new users to start Q&A. Once they get going though they rely on Q&A more and more to get their questions answered. Here’s how you can help them along the journey.

  • Featured Questions

Set common question patterns as Featured Questions. These are shown when users click on the Q&A box. These give them an easy way to start exploring Q&A. See how to Create Featured Questions.

image
New users can simply click on one of the Featured Questions to get started with Q&A

  • Share links to Q&A Answers

Once you or someone else gets a question answered via Q&A, you can simply grab the URL from the address bar and share it with anyone (who has access to the Dashboard). This is very convenient and encourages others to give Q&A a try as well.

image
Just copy the URL and share it via Email or Instant Message for an instant answer

Here is my way of encouraging Q&A

1st Time I get asked a question: I use Q&A to get the answer then send the URL along with a screenshot of the result

2nd Time I get asked the same question: I paste the URL and say that “Hey just open the same Q&A link that I sent you earlier”

3rd Time I get asked the same question: I simply ask them to use the same Q&A link I sent them last time.

By now, they usually take the hint Smile

Once they are used to opening the link, they also quickly realize that they can type in terms themselves to ask different questions.

How do you Q&A?

Phew! That’s the list from my side. Would love to hear from all of you about your experience with Q&A.

Have you have found Q&A frustrating or exhilarating?
Tell us about the tips and tricks or best practices you have picked up around Q&A?

Power On!
– Avi Singh
LinkedIn Twitter

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI. He is based out of Seattle, WA.

This Post Has 10 Comments

  1. Another great article. I’ve been a bit unsure about how good and worthwhile the Q&A feature really is. This has reassured me, I need to give it some time and effort as it will bring benefits to all when used correctly. Cheers.

  2. Fantastic post. I have a best practice of adding 3-5 featured questions in all the PowerBI.com reports I produce and have been encouraging students to utilize the feature. The addition of creating a seperate Q&A specific data model off a hosted tabular model is a really great way to help limit fields to ensure better question asking and retrieval. Well done!

  3. Im new and want to learn power bi/power query what resources should I start with?
    Any resources would be appreciated.
    Thank you

  4. You’d probably benefit a lot from Rob and Avi’s book. It’s a great tutorial for a beginner (like myself) I’ve learned a lot.

  5. Great blog post and some great idea’s.

    A quick tip for Q&A is that it does recognise things such as “Today” or “Yesterday” or “This Month” etc… So that often helps when creating items that you want to pin to a particular dashboard that will always show yesterdays figures!

  6. The best thing of PowerBI models versus SSAS ones is PowerQuery, mainly the M language and the connectors.

    Should there be a reliable way to “host” a PowerBI model and have OLAP clients connect to it like they do with SSAS, I would jump in immediately!

  7. Hello Geniuses,

    I want to calculate % of room revenue from total revenue for 3 months in pivot table.

    Months Aug’16 Sep’16 Oct’16 YTD Total
    ROOM REVENUE 94 96 94 284
    Total Revenue 134 132 128 394

    % To Total Revenue 70% 72% 74% 216%

    Grand total of % To Total Revenue should be (284 / 394 = 72%), but here is showing 216%

    Can we fix this? Please help.

    Thank you..
    Nikunj

Leave a Comment or Question