Populate a Power BI parameter list using a query
ฝัง
- เผยแพร่เมื่อ 26 ก.ย. 2024
- Patrick shows you how to populate a Power BI parameter list using a query. This can help you avoid manually entering a list, or just having free text.
********
LET'S CONNECT!
********
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
Check out my Tools page - guyinacube.com...
#PowerBI #Parameters #GuyInACube
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
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.
Love it! 👊 Appreciate that soooo 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!
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.
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?
Same thought here! I wonder if maybe in situations with LARGE data sets filtering at the query level would increase performance as opposed to a slicer. Absolutely no clue if that's even 1% true but just an idea.
Thanks for wonder videos. I like your statement patrick
"I am not lazy, I am efficient".
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?
@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
My question is the same, how do you make a parameter searchable by a user on the dashboard/online or is this not possible?
I have the same question... if you have found a way.. please let us know
Same question. How does a User choose parameter value in live connected report ?
Any one found the answer for changes for end user? in read only report view?
Same question! It seems to be there is no feature yet to that.. Waiting for that so bad as it would be so sick if we can do that!
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.
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!
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
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.
Love your videos, you guys are one of the reasons that PowerBI is so good
Third time back to this one! Really useful
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?
Ed Hansberry I was thinking the same. Can anyone please think of a scenario where slicers are not enough?
Hey guys, I built a similar workflow for a team recently that prompted for them to pick two tables of data in a variance analysis model. So they would pick Current and Prior Sales to see what has changed. The lists were generated exactly as Patrick has shown here but I haven’t had any luck publishing this to service. It only works in desktop.
I'm also curious what the use case for this is.
Here is the scenario, if you want to create dynamic data source to changing one server/database it will helpful..!! Try out. And let me if you face any issues.
Thanks Patrick for the video
it will be good when ever the user open the report.
they can just select the list and focus on the job, without distracting to other that not related to them.
Love you Patric you got the best youtube power bi tutorial !!!!
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
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.
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.
Thank you Patrick!!!
I was just searching to solve a problem and you got me a great ideia. Thks
Absolutely fanning! Love your videos.
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.
This is an awesome video. It answered a number of questions on lists and parameters. Do you offer any structured Power Bi training?
Please make a video how to work with parameters and functions IN THE SERVICE. While desktop works fine, the service shows error. Thanks!
i was asking my self the same thing !
Agreed. It sure the dropdown list works in Web. Would love Patrick to go over that.
@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.
I was reading this comment and the solution for it was in the recommended videos right next to this comment. ;p
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!
Looking for this too! Did you manage to find a solution for this?
Great explanation, Patrick! Thanks a lot! (Allard from The Netherlands)
How to include the "select all" option in Manage Parameters, manage parameter only one item to be selected at one time
Thank you for your video! Is there a way that I can choose two values? Example: I want to select France AND Germany?
It's a stunning concept. But can we use those parameters as slicers on the dashboard?
Awesome stuff, tried but never transformed to list, now I know and will implement.
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
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?
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)
Looking for this too! Did you manage to find a solution for this?
This video is really awesome !
Hi Patrick, thank you for sharing very usefull things. I'm trying to use Power BI parameters into SQL quiery by using ¶meter&. It doesn't work. Any ideas how to solve it?
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
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).
hi Patrick, on the service still the user has to go to the dataset and write down his parameter? am i doing something wrong, or there is a missing step from myside?
Hi @GuyInACube. I'm trying to do this with a slicer (not Edit Parameters) (Tried both direct query, and direct query to list), and the bound field (to parameter) is not changing the parameter. Any ideas?
@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?
Exactly I have the same use case as well. Please let know if you come across any workaround. Thanks.
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
This is great, thanks Patrick!
@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?
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 :(
nice way to handle parameter
it's really helpful, Thanks
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
@nagesh shastry its exctly my question. Did you get a solution for this?
@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?
Amazing work, many thanks
the solution is helpful. thanks
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 ?
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.
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?
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).
why do we not load all and then add a filter on visual? is it help to shorten the refresh process?
@Patrick can we use the parameter list as part of drop down..
@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?
Patrick. Thanks for this video. Is there away I can pass parameters in the where clause in sql query. using the parameters in the drill through I want see return of rows from the query using the where clause. Do you have a video on that. Or v
Or can you provide a Video.
Hey Patrick, my stored procedure has Start and End date parameter and I want to bring the data to pbi and then have user able to select StartDate and EndDate as a filter in the dashboard. How can I accomplish this. The way the script was written in the stored procedure; my dataset doesn't contain proper date fields to bring in. So, my guess is if we bring a date column from calendar table to a list and then pass that list to both of the StartDate and EndDate parameter, can I bring the list date to the slicer in the visual; if that is correct way of doing. I am stuck, please help me out. Appreciate the time. Thank you.
@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.
@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
Goodday, maybe a silly question, but is there a way to let a webviewer select a different parameter? I don't think it's possible but i just want to be sure.
For example: i used a parameter list to switch propertyID / accountID (Google Analyticis 4) within powerbi desktop.
this allows me to swap source information without having to add all properties / accounts individualy.
But i want my viewers to see ALL the properties from different accounts individualy.
Can i make a slicer or button for webviewers to select a different parameter?
Kind regards,
Lucas van Dijk
PowerBI Rookie
Thank you! Great example and so clear!
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?
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.
@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
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
@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.
I have a direct query (Oracle database) for which I need to be able to limit the rows coming from the database, and I am trying to use a parameter based on another direct query table in the database. Power BI squawks saying my "main query name" references other queries or steps, so it may not directly access a data source. Please rebuild this data combination...". So, can you not use a parameter based on a direct query with a direct query table? (Video really helped get me started, so thanks!).
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?
Thank you! Also, this report is so pretty! Taking design ideas from your videos too :)
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 I have a long DirectQuery that I'm bring over from SQL MS to PowerBi and I'm not sure how to have the PowerBi to create parameter for the Account number and date range in my SQL Query. If I have to do the parameter in powerBi then I would have to do the SUMs and grouping in powerBi and I don't want to do that. Do you have a video that helps me with my quest
Thanks so much Guy! Saved me a ton of time. PBI is #userhostile
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?
Awesome video and info, thanks
Darn, I thought this was to keep all that is in the parameter list. I was wrong. What's the point of connecting to the list then? Please advise whether there is a way to filter for all in the list.
I hope it wont work in PBI service.. so will the end user needs to have power bi desktop and have this report downloaded to change the parameter. Please suggest
Thanks, Power Boss of Power BI. Great Tips.
Is there any way to have a user kick off a refresh of the power bi report deployed to services to return only the filtered data they want to see? It looks like the owner of the report needs to kick off the refresh but trying to see how we have have the user only grab what they want to see. This is pretty close to what we are looking for but we are trying to remove the need for the developer to refresh the data and we don't want the data refresh to affect all the user views.
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?
Can we get the Drop-down in Query Editor so that I can pass it on function while executing
Thanks
great video thanks for this Patrick
If you have a cell with multiple value for skills like let say Java, MySQL, ... etc how would you you convert to list
@Patrick - can u just give a slicer with dropdown will this work as same .
Hi Patrick, if we create a function to achieve a list of parameters does the SQL native query runs every time the function called? For example, if i have 100 stundent id, in the students Xls, will the SQL be executed 100 times ?
Can I have a query list with more than one column? I want to use ID instead of the label. Is it possible?
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 :(.
Veryyy helpful, thanks!
Is there a way to pass the parameter dynamically to the stored procedure from the report to pull the data?
Hey Patrick - is it possible to pass parameters to a dataset to refresh using the REST API cmdlets, or are we stuck to only doing it on PBI desktop?
Thanks, well explained, that's what i was looking for. :)
@Patrick - how can I pass the output of one query to multiple other queries in PowerBI? I am using COSMOS DB.
Hi! @Patrick, how can I implement date or timestamp values to list? Are there any date or calendar features instead data from another table?
Hi, I love you channel ,its like a complete guide to power BI. I was hoping if you could help me with an issue, I created a parameter in the same way as mentioned in this video i.e parameter using query which takes in a list. Now I have a function that gets weather data from acis website, it takes StationID as parameter. No how do I pass the values of the list parameter one by one to this function and combine all data from different stations. I want this to as dynamic as possible. So I am facing issue while setting up schedule refresh, of a report that uses custom function , parameters and dynamic data source. Thanks in advance.
will users be able to pass similar paramters also over service?
Nice Patrick! you rock it!!!
is it possible for end user edit parameters in the power bi service (SaaS)?
This is great feature, but how can users dynamically pass parameter value, query parameters dont showup as user prompts in power bi