Ask a Question (Q&A) Hidden Treasures in Power BI Desktop
The new Ask a Question (Q&A) in Power BI Desktop really does make it quick and easy to create new visuals.
Here is a link to the blog post from the Power BI Team: Q&A for report creation (preview)
I have been using Q&A in the Power BI Service for some time, and there are a few hidden treasures that you can now also use in Power BI Desktop. I will demonstrate a few of them that I know below.
I think the hidden gem is that the amazing people in the Power BI team have enabled Q&A to understand context so that you can not only ask questions as we would ask, but also it understands things like dates (Today, This Month), as well as for example highest, top, bottom etc.
So this can then be leveraged to easily create visuals that will always be in the context that they were created in.
Q&A for today
The first example, I created a card showing me the sessions for today 04 Jan 2018. The reason I wanted this, is because I am always interested in how many sessions I have for today. So as time moves, so will the card below, to always show me today, which is the context it was created in.
HOW COOL is that!
In order to confirm this, I validated it with the following screenshot below, showing what my sessions were for today 01/04/2018. As well as the DateTime where I have my PBIX file open.
And if I look on closer inspection at the Visual level filters, I can see where it has applied the filter automatically for me.
Q&A for current (this) month
A second example is where I want the total sessions for the current (this) month, so that as time moves on it will always show me the current month that I am in, again the context in which I created it. I did not script this at all, that it would add up to 1,000 exactly!
One thing to take note if, is that I chose “this month (Date > Date), which is from my Date table, and the Date column. This is because I have created a relationship from my Analytics table to the date table. This ensures that it is used within the right context.
Once again if I look at the Visual level filters it shows me how it created the measure in the card, as well as it shows that it will move with time.
Other hidden gems
As I found out from the documentation there is a whole host of hidden gems that you can use in context when asking a question.
At the end of this blog post are the current words and terminology that Q&A recognizes.
Differences between Q&A in the Power BI Service and Power BI Desktop
EDIT: 10 Jan 2018
Once again the Power BI team is incredibly quick at updating Power BI Desktop, and where I had previously said there were some differences between Q&A in the Power BI Service and Power BI Desktop, with the January 2018 release, it now appears to be identical.
So as shown below, if I was now searching for the date of my Max Sessions, this can easily be achieved with the following shown below.
Once again I am really impressed with Power BI, and I love working with the product!
It would be nice to have the ability to edit the Q&A as you can do on the Service. So I have created the following idea on the link below.
Vote here: Edit Q&A questions in Power BI Desktop
I hope that you have found this blog post useful and can start leveraging Q&A in Power BI Desktop, it really is a fantastic feature.
Power BI – Q&A Words and Terminology
If you are the owner of a dataset, add phrasings and synonyms to improve the Q&A results for your customers.
Aggregates: total, sum, amount, number, quantity, count, average, most, least, fewest, largest, smallest, highest, biggest, maximum, max, greatest, lowest, littlest, minimum, min
Articles: a, an, the
Blank and Boolean: blank, empty, null, prefixed with “non” or “non-“, empty string, empty text, true, t, false, f
Comparisons: vs, versus, compared to, compared with
Conjunctions: and, or, each of, with, versus, &, and, but, nor, along with, in addition to
Contractions: Q&A recognizes almost all contractions, try it out. Here are a few examples: didn’t, haven’t, he’d, he’s, isn’t, it’s, she’ll, they’d, weren’t, where’ll, who’s, won’t, wouldn’t.
Dates: Power BI recognizes most date terms (day, week, month, year, quarter, decade, etc…) and dates written in many different formats (see below). Power BI also recognizes the following keywords: MonthName, Days 1-31, decade.
Examples: January 3rd of 1995, January 3rd 1995, jan 03 1995, 3 Jan 1995, the 3rd of January, January 1995, 1995 January, 1995-01, 01/1995, names of months.
Relative dates: today, right now, current time, yesterday, tomorrow, the current, next, the coming, last, previous, ago, before now, sooner than, after, later than, from, at, on, from now, after now, in the future, past, last, previous, within, in, over, N days ago, N days from now, next, once, twice.
Example: count of orders in the past 6 days.
Equality (Range): in, equal to, =, after, is more than, in, between, before
Examples: Order year is before 2012? Price equals between 10 and 20? Is the age of John greater than 40? Total sales in 200-300?
Equality (Value): is, equal, equal to, in, of, for, within, is in, is on
Examples: Which products are green? Order date equals 2012. Is the age of John 40? Total sales that is not equal to 200? Order date of 1/1/2016. 10 in price? Green for color? 10 in price?
Names: If a column in the dataset contains the phrase “name” (e.g., EmployeeName), Q&A understands the values in that column are names and you can ask questions like “which employees are named robert.”
Pronouns: he, him, himself, his, she, herself, her, hers, it, itself, its, they, their, them, themselves, theirs, this, these, that, those
Query commands: sorted, sort by, direction, group, group by, by, show, list, display, give me, name, just, only, arrange, rank, compare, to, with, against, alphabetically, ascending, descending, order
Range: greater, more, larger, above, over, >, less, smaller, fewer, below, under, <, at least, no less than, >=, at most, no more than, <=, in, between, in the range of, from, later, earlier, sooner, after, on, at, later than, after, since, starting with, starting from, ending with
Times: am, pm, o’clock, noon, midnight, hour, minute, second, hh:mm:ss
Examples: 10 pm, 10:35 pm, 10:35:15 pm, 10 oclock, noon, midnight, hour, minute, second.
Top N (order, ranking): top, bottom, highest, lowest, first, last, next, earliest, newest, oldest, latest, most recent, next
Visual types: all visual types native to Power BI. If it’s an option in the Visualizations pane, then you can include it in your question. The exception to this is custom visuals that you’ve manually added to the Visualization pane.
Example: show districts by month and sales total as bar chart
Wh (relationship, qualified): when, where, which, who, whom, how many, how much, how many times, how often, how frequently, amount, number, quantity, how long, what