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] 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 a 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 works in practice. So here I've already built a report and a model used in Power BI Desktop. I've got some simple information here showing cells by product category and across different countries. And if I go and look at the query editor, I'm just pulling in two tables, one with the sales amounts and one with the product details. But like I said, perhaps if this is a very large data set or if it was connecting to a database and you wanted to limit that 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. And 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 the country code, and it's the two-letter country code for your area. So you can imagine sending this to 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 be 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 of them 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 a query. I'm just going to type in a list, so you know, Canada, US, and UK. I'm just going to select those three. And I get to choose a default—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 in the moment. So there are 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 have different databases within your organization but you can run the same queries and product 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 a filter on this country code field. Rather than typing in the value that I want to filter by, I'm going to choose one of the parameters that I've already defined— in this case that country code. I can 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 or a whole bunch of different ways you could use this. So when I close and apply this now, it will go and re-query that data. But in this case it's only going to 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 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 strips out all of the data from Power BI Desktop but it retains the query definition. It retains all the transformations. It retains any calculations that have been defined on top of it. And of course, the visuals in 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 country's template. And this is the thing that I could send to one of my colleagues. And you can see it's only 16 K. It's just the definition here. And when somebody opens this up, the first thing they'll see is they'll get 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 do you want to connect to? What date range do you want to pick? Et cetera, et cetera. And you can see those three values that were populated for me. So let's just choose Canada. And now I want to 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 it's never touched any—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 and 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 21, 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.