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
>> 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 the dropdown, but you can then use in your queries or in your calculations as you build that report. And that'll only see, for example, if it was a prompt to select the country, they would only see the data for that country. That's one of the examples. But let's take a look at how it works in practice. So here, I've already built a report on a model using Power BI Desktop. I've got some simple information here showing sales by product category and across different countries. If we go to look at the query editor, I'm just putting in two tables, one with the sales amounts, and one with the product details. But like I said, perhaps if this is a large dataset or if it was connecting to a database that you wanted to... And 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 gonna filter it down by country. And to start, I'm gonna come up to this icon in the ribbon and I can choose to create a new parameter. I'm gonna type in the name, and a description, so this is this a country code, and it's the two letter country code for your area. So I'm gonna imagine sending this to, you know, sales managers who manage each of these different countries around my organization. And 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 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 of them will be optional. And I can choose whether it's a free from text box, a list of things that we're gonna specify, or actually the results of a query. I'm just gonna type in the list, so Canada, US, and UK. Just gonna select those three. I get to choose a default, you know, what the current value is. So that 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's a whole range of ways that you could you 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 have different databases within your organization, but you can run the same queries and produce the same reports against them, 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 any other data source within the steps here in my query. So I can actually use this to come and do filter on this country code field, rather than typing in the value that I want to filter by, I'm gonna choose one of the parameters that I've already defined, in this case of that country code. I could use this in my M scripts as well, so I've seen people kind of replacing values with a value typed into one of these parameters, a whole bunch of different ways you could use this. So I want to close this and apply this. Now it will gonna re-query data, but in this case, it's only gonna pull 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 e-mail it to them, they'll already see the US data, you know, it's already been there, they'd have to go and refresh it again. The other thing is, it might be a very large dataset. So what I can do is actually define some of the quarter Power BI Template. And basically, what this does is, it strips out all of the data from the Power BI Desktop, but it retains the query definition, it retains all those transformations, it retains any calculations that have been defined on top of it, and of course the visuals and the reports as well. So let's call this one our 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 I could send to one of my colleagues. And 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 that they'll get prompted to fill in those parameters. So in our case, it's gonna be the country code, but it could be, like a said, kind of, the, you know, which database do you want to connect to, what date range do you want to pick, et cetera, et cetera. And you can see there's 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 you just see those values for the Canadian data. And it never touched any of the... 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 that is query parameters, give you a way to prompt your uses to select particular data, or enter particular values that then you can use when you're defining the rest of your models in your reports.

Video Details

Duration: 5 minutes and 47 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 47
Posted by: csintl on Aug 11, 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.