Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"


0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
[Will Thompson] If you wanted to send a Power BI Desktop file that you've built to one of your colleagues to allow them to refresh data, but potentially only pull data for, say the country that they're interested in, you can use something called Query Parameters. What these let you do is let you define the report and the model and everything else in your Power BI Desktop file. But then specify a little prompt that appears when a user opens that file to type in a value or select from a drop down that you can then use in your queries or in your calculations as you build that report, and they'll only see, for example, if it was a prompt to select the country, they would only see then the data for that country. That's one of the examples, but let's take a look at how it looks in practice. So here I've already built a report and a model use in Power BI Desktop. I've got some simple information here showing sales by product category and across different countries. If I look at the query editor, I'm just pulling in two tables— one with the sales amount, and one with product details. But, like I said, perhaps, if this was a very large data set, or if it was connecting to a database that you wanted to limit the amount of data that people were bringing back from that database into Power BI Desktop, you could use one of these query parameters to do that. So in this case, we're going to filter it down by country. To start, I'm going to come up to this icon in the ribbon, and I can choose to create a new parameter. I'm going to type in the name and a description, so this is a country code and it's the two letter country code for your area. So I'm going to imagine sending this to sales managers who manage each of these different countries around my organization. I can choose here what the data type is, so if I wanted a date/time field, if I wanted to potentially filter it by date, I'd get a little date picker in the prompt. In this case, it's just a text field. I can choose whether somebody has to put in a value for this parameter, so potentially if I had a whole set of parameters, maybe some would be optional. And I can choose whether it's a free form text box, a list of things that we're going to specify, or actually the results of the query. I'm just going to type in a list, so Canada, US, and UK. I'm just going to select those three. I get to choose a default, and what the current value is. So by default, it means what will be shown in that prompt when somebody opens it by default. And the current value is just whatever I have selected at the moment. So there is a whole range of ways that you could use this. For example, filtering data down before it comes into Power BI Desktop, like I'm doing here. Maybe even changing the database that it was connected to. So if you had different databases within your organization, but you can run the same queries and produce the same reports against them, then perhaps moving from a test to a production environment, you could drive that through one of these parameters. So now I've got that parameter set up here, and I see that in the query editor as another query. And I can use it, just as within any other data source within the steps here in my query. So I can actually use this to come and do a filter on this country code field, rather than typing in the value that I want to filter by, I am going to choose one of the parameters that I've already defined, in this case, that country code. I could use this in my M script as well, so I've seen people kind of replacing values with a value typed into one of these parameters, or a whole bunch of different ways you could use this. So when I close and apply this now, it will go and requery that data, but in this case, it's only going to fill in data for the US. So that shows you kind of how you define these parameters and how you then use them when you're building and defining your report and your model. But what about the piece when you send it to another user? If I save this desktop file and just email it to them, they'll already see the US data, it's already been there, they'd have to go and refresh it again. The other thing is, it might be a very large data set. So what i can do is actually define something called a Power BI template. And basically what this does is, it strips out all of the data from Power BI Desktop, but it retains the query definition, it retains all of those transformations, it retains any calculations that have been defined on top of it, and, of course, the visuals on the reports as well. So, let's call this one sales by country, save that here onto my desktop, and I'll just close that one down. So, I've now got this Power BI countries template. And this is the thing that I could send to one of my colleagues. You can see it's only 16K, it's just the definition here. And when somebody opens this up, the first thing they'll see is I've gotten prompted to fill in those parameters. So, in our case, it's going to be the country code, but it could be like I said, kind of which database you want to connect to, what date range do you want to pick, etc., etc. And you can see those three values that were populated for me. So let's just choose Canada. And now when I hit load, the same queries will be run, but this time, it's filtering the data down to just Canada. So we just see those values for the Canadian data. And none of the US data has come into the Power BI Desktop here. That query is kind of pushed back down to the underlying source as well. So those query parameters give you a way to prompt your users to select particular data or enter particular values that then you can use when you're defining the rest of your models on your reports.

Video Details

Duration: 5 minutes and 47 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 53
Posted by: csintl on Aug 22, 2016

----- (Please provide translations for these languages: Chinese (Simplified) (chi_hans), Chinese (Traditional) (chi_hant), English (eng), French (France) (fre_fr), German (ger), Italian (ita), Japanese (jpn), Korean (kor), Portuguese (Brazil) (por_br), Russian (rus), Spanish (spa).)

Caption and Translate

    Sign In/Register for Dotsub to translate this video.