Populate a Power BI parameter list using a query

yo what's up is this Patrick from Guyana cube and in this video I'm going to show you how to populate a parameter with a list of values from a query stay tuned [Music] okay populating parameters from a query hmm can you do that why would you do that well I had someone asked me about them they're like hey Patrick we have these parameters that we've created on our report and we allow end users to modify those parameters right now it's a free text box and sometimes they type in a valid value sometimes they don't we want to ensure that they always select the valid value okay so instead of having a free text box can we just have a drop down I was like of course you can there's like we've been trying and trying to do this and we can't figure it out can you show us sounds like of course I can so instead of all this talking let's head over to my laptop so you have this report right here and what I'd like to do is when my in users open this report they go edit queries they choose parameters this parameter a dialog window pops open there's a drop-down box with a select country easy enough easy enough but how do you do it right so let me show you so you click Edit queries so I'm gonna click edit queries and I want to tie whichever parameter I create to my sales territory table it'll automatically filter stuff because there's a many to one relationship between myself semi one-to-many relationship between my sales territory and my sales table and so I want to filter this table based on that parameter okay the first thing you want to do is create your parameter right so we go here manage click new parameter and we'll call it country just like that and you can select the type you can choose text or whatever you want this is text so I'm gonna choose text and then you can choose list of values if you want to populate it with the list but you have to type the values in and for me this is not very efficient because if a new value is added to that list then guess what I gotta come here or all the reports I use and continuing to update that not an efficient thing right or you can choose query but if I choose query notice it's disabled when you said I can populate it from a query you have a query that's tea countries that contains a distinct list of my not quite distinct I'll make it this thing but a list of my state my countries here why is it not enabled well hover over the little information icon and it'll say hey you need to choose a list query it's a list query I thought they were all just queries where there's two types of queries there's a table query and a list query okay let me show you how to create a list query by default everything comes in as a table query except if you do one thing now I'll show you this alright so we're gonna just go ahead and choose any value for now and make this United States all right it's United States I click okay so that's my parameter my parameters created and so I have a table right now list of countries there's some duplicates I'm gonna go ahead and remove my duplicates and now I have a distinct list of countries and I'd like to use that to populate my parameter right how do you do it well right now it's a table and then you can see that from the icon so there's parameter icon there's a table icon but there's also a list icon and so if you want to convert this table to a list you just need to go to transform in the ribbon and then you'll see a section labeled any column and there's a little icon labeled convert to list go ahead and click that sorry click it and now notice it changes and it says list and look at the icon the icon is now a list so if I go back to my parameter I choose manage parameter and then I change this from any value to query right and now this is enabled because T country's is a list query and now I can use it as a source for this parameter it's pretty cool it's pretty cool all right I promise to show you another way so we'll click OK so that's there but if you if you're the source of this particular drop-down box is you have to derive it from an existing query alright and it's only a single column that you need so if I right click here you'll get this option that say add is a new query ok if you choose two columns that options not there okay it's only for a single query so I'm gonna choose this one right click say add isn't in query all right I'm gonna name it I'm gonna give it L country's right and notice you may all you may have noticed something already that it's automatically a list so when you do at his new query it automatically creates it as a list I'm gonna remove my duplicates all right there's my distinct list if I want to write if I want to I can convert this to a table full table but for what I'm doing I needed to maintain a you know stay as a list and now what I can do is I go over here same thing manage my parameter and I can change it to you know my list query they have the same you know results the same values I was just showing you you know multiple ways you can get to this list once you're done right I want to use this I'm gonna use this parameter to filter out my sales territory table because that's all I just want the sales territory table to be populated with the value that selected from that parameter right so I'm gonna go ahead and filter country out and if you're you know expert in them you can freehand this but I like to do it this way so I'm gonna choose this choose United States click okay and what will happen is it adds a step into my my M and if I want to write I can just type freehand it right here or I can go into the advanced editor and so I'm gonna replace in my filter step I'm gonna replace United States with guess what my parameter so it's called country make sure that you use the exact same spelling with the same case sensitivity that you did when you created the parameter intellisense is there to help if you don't quite remember you click done and it stays populated with the United States if I go back to L country I'm sorry if I go back to my parameter I choose manage my parameter and I change it to France right when it changes to France click OK now watch what happens to sales territory right it's only showing France because it's that sales territory table-based is based on that filter and that filter relies on the you from my parameter now watch this right so I'm all done I click close and apply I was doing some work with storage modes so you may get to see a little dialog window pop up about storage mode so you can you may not see this depending on how you set it up if you do just click OK and it'll get it'll load everything up for me and everything is filtered down just to France because that's the value I had set when I built out the queries and the lists and everything and so now as an end user you can come here any queries you'll see edit parameters is enabled now and then I get a list instead of free text box and I get next boat right instead of a text box I get a list I can choose United States I click OK once I click OK I choose apply changes and now all right everything switches over to United States just like that it works answered the question from the customer they were happy I'm happy and now we move on so how are you guys doing this today have you used this approach right does this work does this not work let me know post it in the comments below if this is your first time visiting the guy in a cube channel you know what to do hit that subscribe button if you liked my video big thumbs up as always from Adam and myself thanks for watching see you in the next video no one take on that one

Channel: Guy in a Cube

Views: 150,150

Keywords: power bi, power bi demo, power bi desktop, power bi desktop tutorial, power bi desktop tutorial for beginners, power bi parameterized query, power bi parameters, power bi parameters in report, power bi parameters query, power bi reports, power bi training, power bi tutorial, power bi tutorial for beginners

Id: 7TfV8qTUyfU

Length: 7min 58sec (478 seconds)

Published: Wed Feb 20 2019