This is GOLD!! YO!! Was trying to get the Query in Parameter setup to work but gave up until now. Much respect, brother. Your tutorials have helped me so much.

I love when I'm trying to solve a very specific problem, and I come across the perfect video walking me through it. Thanks!

Is there a way to use multiple values from the list? I need to be able to select 4 or 5 values at the time. working with PowerQuery

2:35 "There are 2 types of queries" --> i'm just nitpicking here, but there are more, you can store a single string in a query (e.g. I often write my queries in a "string query" and reference them later), it can reference a file, etc. But I liked the video, when I first tried this months ago I run into this "why I can't use my query here?!" problem, wish you published this earlier, would've saved some time and brain cell :D.

I am pretty new to PBI but have really enjoyed your videos, but this one is going over my head; why is this better than a simple filter on the filters pane over on the right or putting a slicer on the page? Thank you.

Thanks for wonder videos. I like your statement patrick "I am not lazy, I am efficient".

Really good video. I am somewhat new to Power Query and am working with Excel (rather than Power BI). I need to take a table; filter it for ids I need to look for; then go back to the original table and filter rows with those IDs. A bit odd, but I will try something like this. Great job explaining a powerful concept.

@Patrick - Will this work once it is published to the Service? If this would that would be so great and help with parameters in SQL Stored Procedures as a data source and allowing users to use a parameter drop down to get the data to populate the report

Same question. Why not use a slicer? I used to use parameter queries a lot in Access especially for date ranges, but the slicers to the trick for users.. So what am I missing? What's the added value?

I do not understand why someone would do this. Why not just use a filter or slicer? This requires the end user to have the desktop app and PBIX file. Our users are in the service. What am I missing?

I really liked the explanation. However, I still don't understand the advantage of creating the parameters, given that when we change the parameter in Power BI Desktop a new Query will be executed at the Power Query level and the filtered data will have to be loaded back into the Model. For large datasets this is an issue. If I have all the data loaded into the model and put a slicer that allows me to filter only what I want, the presentation is immediate. Where am I failing in my interpretation?

dam. i just played you video during my standup... your nutz intro just blasted my entire team. unfortunatly get this error This query contains transformations that can't be used for DirectQuery.

This is cool, but for a report user is this practical? As opposed to a slicer? Also, can one use DAX or a slicer to change the parameter or do you have to go to Edit Parameters? Really cool tip though. Your videos are great!

Love your videos, you guys are one of the reasons that PowerBI is so good

@Patrick: I created the Query Paramaters and everything works fine in Power BI Desktop.. But as soon as i Upload it to Power BI Report server (August - 2018) it simply doesnot shows the "Edit Parameter" dialog. Any quess, how do i enable this on Report server ? Thanks Tejas

Guy in a cube are best for everything in power bi

Outstanding! Glad I discovered this video, Patrick. Can be used in many applications. Thank you!

Love you Patric you got the best youtube power bi tutorial !!!!

Third time back to this one! Really useful

Thanks so much Patrick! It helps me a lot! Question: Is there a way to allow no selection of the parameter so the visualisation plots are based on all data after I set up the dynamic parameter filtering? Like a "All" option on top of the parameters

What's the difference between Parameters and Filter? For User it's easy to use filter special if you have weak connection or big data and need to wait long time to refresh your dashboard

I was just searching to solve a problem and you got me a great ideia. Thks

Please make a video how to work with parameters and functions IN THE SERVICE. While desktop works fine, the service shows error. Thanks!

For my purposes, my list is only 2 static values so I did choose the List of Values option. I saw in this video how you use Edit Parameter to apply the chosen value to your visualization. Do you have any videos on how you can use that parameter as a slicer on your visual for the user to choose real-time? NOTE: My users would not know how to choose Edit Parameter to change the result.

Absolutely fanning! Love your videos.

I don't expect my users to go to a "Parameters" menu to change this. Why can't it be an element on the report page like a slicer?

Great explanation, Patrick! Thanks a lot! (Allard from The Netherlands)

nice way to handle parameter

Thanks so much Guy! Saved me a ton of time. PBI is #userhostile

This video is really awesome !

Awesome stuff, tried but never transformed to list, now I know and will implement.

This is an awesome video. It answered a number of questions on lists and parameters. Do you offer any structured Power Bi training?

It's a stunning concept. But can we use those parameters as slicers on the dashboard?

This is great, thanks Patrick!

@patrick, this is great! all tips and tricks of you guys have changed the way we develop our dashboards, thanks for that. On this, I was wondering is there a way I can have a date picker enabled from a query? I want it to be more of a single date picker from calendar than from a list. Scenario we have: we have over 200 million rows of data in the underlying database table, we want to pick data based on the date an end user selects, but this date should be a distinct list of dates from the same table. We have set a parameter for now but want to have it more user friendly.

it's really helpful, Thanks

How to include the "select all" option in Manage Parameters, manage parameter only one item to be selected at one time

Amazing work, many thanks

I was hoping to filter the data prior to the import, but since it adds a step to the M, it seems to first bring back all of the data then filter it. In other words, it fetches the maximum number of rows then filters the results. Not really that efficient. Also, they really need to make this usable for multiple value parameters and optional parameters (a null option).

Omg i lost my attention for a while until i heard the "Textbooooooox". 😂 Love your videos!

Thank you! Also, this report is so pretty! Taking design ideas from your videos too :)

the solution is helpful. thanks

what app do you use for the blue highlight on the cursor? you guys are SO great with interacting with the community...

Thanks, Power Boss of Power BI. Great Tips.

Thank you! Great example and so clear!

great video thanks for this Patrick

Awesome video and info, thanks

Is there a way to change these parameters on the dashboard itself, instead of using an option in the ribbon. This process will become useless when the dashboard is published.

Hi...i am currently working with parameters in power BI desktop and i was able to do that..like users will have the ability to enter values from drop down list and then submit the report.but i cannot find a way to enable " select all" values in the parameter drop down list so that users can select all the parameter values at one go.can you please help!

Great video, as usual. Thanks. Can we put the user's email as a parameter?

Nice Patrick! you rock it!!!

I have made a list and a parameter that should read the date from the list. but when the list is refreshed the parameter doesn't read any value other than the current value being written manually. Why is there a Current value text field(Input) when it's supposed to be reading from a query?

Thanks, well explained, that's what i was looking for. :)

Hey, I have already used this and this method is great, but I have one query, If I want to publish this, and give options to choose to my partner then everytime I need to save as for each particular country. Is there any method where I can publish all countries and give options to my partner to select which ever country they need.? Thanks Patrick. You rock

Veryyy helpful, thanks!

What a fantastic video!

Patrick you are a legend :-)

@Patrick How we can use multiple parameters to filter our data and show data for multiple countries at once. Like France and UK both at once using a parameter.

Can I use this method to select all the values on my list parameter? (assuming that I have a list what is a subset of possible values in the column)

Hi Patrick, I would like to know if using parameters I'll be able to import less data, for example, data from MySQL based on the last 2 calendar months, this is oriented for looking the way to reduce consumption in the database, thanks for your videos

Thank You it’s soooo helpful

1 question on Power BI: how to get around privacy setting when referencing a parameter into a SQL query. - I have 2 text parameters: PositionTitle1 and PositionTitle2 - I have a SQL stored procedure asking for 2 inputs: PositionTitle1 and PositionTitle2 - I need to run that Stored Procedure on my SQL database and get the result into data table in Power BI. Please note that Ignoring Privacy Level is not an option. I need to find out how to do it with standard setting because I need to provide my solution to a wide range of users. You can either send me a clear instruction or do it live with me (preferred). I need it soon. Thanks.

@Patrick Thanks for this video, i was looking for this and you explained precisely. is there any way to user hierarchy in parameter? without using any plugins

Works like a dream. Thanks for sharing :-)

What I'm trying to understand, why would you choose this over slicers? It's the same thing right, or have i got it wrong

Thank you, this is awesome!

Dang Patrick... this is awesome! :)

Hi Patrick, wanted to know if i can you use multiple columns as a parameter, so if i have column name Country, Sate , City, Zip, etc..i can pull it’s value to a single column and then use the parameter as filter... I can create a bridge table and unpivot those columns.. But in my scenario there are +15 columns to be used as a kpi, and the data is heavy

Hey Patrick, great job! Geaux Tigers!

Hi Patrick thanks for the video! One quick query regarding the parameter what if my parameter value is from different SQL server. e.g. client id for different customers. We want to select client id as a parameter to populate the visualization, Every customers has same schema/table structure. currently we have six customers and in future we expected to increase. Please let me know how we can implement the same.

What's the best way to get a Parameter for DirectQuery that uses a list of options from a query? The Direct Query for the Parameter list can't be turned into a list, so it cannot be used for the Parameter's options. Using the same query as an Import query gives an error (formula.frewall ... references other queries or steps, so it may not directly access a data source) if the parameter's options come from an import query. Same if they are in Dual Mode. Paremeter is a dropdown list, from a query. Used on a DirectQuery (because of the size) to return the subset of data. SSRS Has this simple in Report Builder, same thing doesn't work here. What's the work around?

Would the parameters be affected by row security? i.e if we set up row security would we be able to restrict the list drop downs a person is able to see while setting the parameter?

@Patrick can we use the parameter list as part of drop down..

Thank you for your video! Is there a way that I can choose two values? Example: I want to select France AND Germany?

Hi @Patrick Thanks fot the video.I am working getting a percentage column as dynamic parameter but it's not completely working ..Please suggest any video to go through on ddynamic percentage parameter

@Patrick; very useful thanks; question: in my model I have a folder query in which a parameter is used to bring the files together, now in my template is also listing that parameter at the start; is there a way to chose what parameter to prompt at opening the file?

How to trigger the parameter value based the slicer selection instead of passing the parameters from edit parameter from the menu

Thank you Patrick, List queries, news for me, one question, is it possible to change a parameter value during User execution on a the published report?

Hi Patrick, thank you for sharing very usefull things. I'm trying to use Power BI parameters into SQL quiery by using &parameter&. It doesn't work. Any ideas how to solve it?

Great info! is there a way to have the user input a date in the report, click refresh and then all queries refresh based on that date? I want to avoid the user to have to click "Edit parameters" (yes, I have simple users).

Similiar to dependent drop down lists in excel . Very Cool!!

Awsome! But what happens to the drop down list when you publish? I'd expect to change my parameter value in the dataset->setting->parameters using a drop down list, instead a free text box is still there :(

you didn't explained (6:21 onward) that for filtering out table based on parameter you have to set the table filter out option for that particular column to Parameter and not text values.

Great tips, but if i use the parameter at sql query level, it does not work with the list. Here is a simplified example of the sql i want to use... [...]Query="select project, qty, ... from datas where project='"&MyParameter&"'"[...] Any idea how to make it work ?

@Patrick, I have a similar situation but need help with the parameter list where the options in the list of check boxes and more than one can be selected. Can you provide any guidance in this case?

What if you don't want to FILTER rows, but instead write a measure to change the value of another column based on what parameter was selected?

@Patrick - I guess these parameters from Web can only be changed from dataset (access to workspace) but don't work from PowerBI APP? All my End Users access Reports only through PowerbI APP only. Hoping this dynamic filter functionality don't work in PowerBI, I am currently creating (Oracle DB) Query based Reports in PowerBI Report Builder.

Great! How to select multiple values from parameters list, kind of merge but (based on sub-string, i.e. Parameters contain....) Thanks

I'm struggling to make a list query from a dax datedimension table. Trying to select a list query of the last 30 days to pass in as a parameter. I have an invoked function from a stored procedure with a date as a parameter but am stuck on creating a list query from the datedimension table I've created.

Hey Patrick thanks for all the great content. Was hoping you could help me solve something similar. I use parameters to update connection details for a specific report but I want get a way for the report to read either the folder it's stored in or the workspace it's published it to determine which parameter details to use. If I can just find a way to pass the workspace name into power bi I can set the parameter details accordingly to change the connection parameters? Please help?

@Patrick, this was awesome. Now I wanna get the value for all the countries instead of selecting one. This parameter doesn't allow me to do so. How can I meet the requirement?

Can we use Parameters created in Desktop like other normal filters in PowerBI Web Service? I think we need to use Report Builder or SSRS for it? Please correct me as some of Microstrategy Reports getting moved to PowerBI have dynamic filtering and I guess this parameters don't meet such need as they cannot be used as filters on Report directly...please advise.

This is great but not practical for an end user. Is there a way to list the parameters on the report so the user does not have to go to the query editor?

Guy in a cube. Great explination of how this works but as a user of Power BI I am watching this and wondering to myself how would I ever use this in my day job? I look at this and think... wouldnt a simple slicer based on country be much more user friendly? It would be nice to outline WHY this parameter option is helpful. If there is a situation where filtering down the query effects performance or something where a slicer wouldnt cut it then okay I would like to know that. Without knowledge of WHY its useful then its in one ear out the other :(.

"wow, that's insane!" what i explore is that, you don't need type "source (para as text)=> " in M script !! i jump through this step if that's good next time XD

Is there a way to pass the parameter dynamically to the stored procedure from the report to pull the data?

why do we not load all and then add a filter on visual? is it help to shorten the refresh process?

This is great feature, but how can users dynamically pass parameter value, query parameters dont showup as user prompts in power bi

If you have a cell with multiple value for skills like let say Java, MySQL, ... etc how would you you convert to list

Is there a way to make the parameters to behave in PowerBI as they do in Paginated Report Builder? I would like to have a dynamic slicer to pass the parameter.