Populate a Power BI parameter list using a query
HTML-код
- Опубликовано: 2 окт 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
@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
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?
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.
I was just searching to solve a problem and you got me a great ideia. Thks
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.
Outstanding! Glad I discovered this video, Patrick. Can be used in many applications. Thank you!
Guy in a cube are best for everything in power bi
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.
@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!
I love when I'm trying to solve a very specific problem, and I come across the perfect video walking me through it. Thanks!
It's a stunning concept. But can we use those parameters as slicers on the dashboard?
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.
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.
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
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!
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 :(
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?
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?
@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?
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.
Hey Prashant, did you get your answer?
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?
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).
This is great, thanks Patrick!
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.
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?
If you have a cell with multiple value for skills like let say Java, MySQL, ... etc how would you you convert to list
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
Great!
How to select multiple values from parameters list, kind of merge but (based on sub-string, i.e. Parameters contain....)
Thanks
That's what I need too. This works great for a single value, but I need it to apply all values in the list. Like an IN () operator in SQL
UPDATE, I did get this to work with SQL queries. I made a parameter called SiteList, entered the comma separated string that would be in a SQL IN statement, then in the SQL I put WHERE siteid IN("&SiteList&")
@@chucktanner2516 have you got any solution for this ?
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?
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.
How to include the "select all" option in Manage Parameters, manage parameter only one item to be selected at one time
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 - 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?
Thank You it’s soooo helpful
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
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.
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?
Hi bro, Is it doable to filter the dashboard using this list in Slicer ??
Hi, this is great, does anybody know how to filter directly in the SQL query. I don't have the column to filter in the table.
Patrick this helped me a lot. Just one thing which i am facing issue that when I provide slicer on top of list query it is not working. Could you please confirm if slicer is feasible??
Did you ever find a solution? This seems to be a huge gap in functionality in PBI. Pulling my hair out.
Will it work in the service or only on pbi desktop?
@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.
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?
Hi, I need help on PBI Dynamic Query. I have created an MDX to pull data from OLAP Cube, where I have parameterized the "Date". Now I am passing the date from an external excel file. I have created List Query for the Date table (Only one date in the table) and names the param Date Param. I created the Parameter RunDate from Date Param and then passed this RUn Date in my advanced Query Editor. But unfortunately when i refresh the date in excel the query runs for the Default or Old date value only. Pls give me solution. Thanks Suvajit
@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
what app do you use for the blue highlight on the cursor? you guys are SO great with interacting with the community...
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
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 ?
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?
@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
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.
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.
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.
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 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?
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.
Hello Patrick create a parameter with suggested-value as QUERY and now use this parameter in 2 tables and create some reports now save the file as pbit format and close the power bi desktop.
now open the pbit file which you saved by passing the parameter value ,if it's opening or not.
if its opening or not opening please make a video in this scenerio , for me the pbit file is not opening
Thank you for your video! Is there a way that I can choose two values? Example: I want to select France AND Germany?
Great Video, but infernal Msoft have changed the location of the QUERY BUTTON!!!!
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
This is great feature, but how can users dynamically pass parameter value, query parameters dont showup as user prompts in power bi
thanks for this. what if i want to select multiple countries as parameter then how it will work ?
@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.
Can we get the Drop-down in Query Editor so that I can pass it on function while executing
Thanks
I'm distracted by Patrick's shirt - the Rubik's Cube shirt has a block with red on two sides...
@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.
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?
Can I have a query list with more than one column? I want to use ID instead of the label. Is it possible?
Hi - this is very interesting?
Would it be possible to do "Manage Parameters" via WEB ? in other words, if I publish a report, there is no way to let the parameter be specified at a run time (e.g. via a popup in the browser, or via a parameter passed on the URL).
I am aware that if I install the "Power BI Report builder" , I could specify parameters by passing "rp:parameter=value" on the URL But I am not using Report builder. Is that anything similar for Power BI (I am using the latest November 2020 app)?
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 :(.
Impressive
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?
How to trigger the parameter value based the slicer selection instead of passing the parameters from edit parameter from the menu
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!).
We have a BW Query and use a dynamic filter = today's date. Rest of the logic in the report is based on this variable.
I am able to create a report on this query, issue is when i refresh next day. Value for dynamic variable date does not change to today's date as it happens in Bex.
how can we achieve this.
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.
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
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 ?
Hi, Close, but doesn't go all the way. I have a main table that runs a refresh each day. I have a column in this table that is called DataUpdateDate that is equal to getDate() or Today's date. When the query runs to create a refresh, I need to set the incremental refresh to only update these items. So If the parameter were set to this date, then I could get then power BI would only add these new rows. But I first have to be able to filer this table based on the rows that have a 'DataUpdateDate' on or after to today(). It looks like the query parameter still has to be set by the user. How can I get this set by the system?
@Patrick - can u just give a slicer with dropdown will this work as same .
hello, after the creation of parameters then i linked that to the power query actual table but here end result was coming like this "this table is empty". could you resolve that???
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.
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, 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
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?
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.
Thanks so much Guy! Saved me a ton of time. PBI is #userhostile
I am doing with the customer name instead of countries but i apply filter and replace the filtered option with the created parameter name i got the empty table. please tell me why it is so.
Great video, as usual. Thanks. Can we put the user's email as a parameter?
How can I display the value of query parameters as a slicer in the report?
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
why do we not load all and then add a filter on visual? is it help to shorten the refresh process?
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.
is it possible for end user edit parameters in the power bi service (SaaS)?
I am trying to understand the use case for this? Why not just use a filter ?
It's such a shame this doesn't also work in Excel! :(
@Patrick--thanks for this. I love how this is so similar to Data Validation in Excel. Going to add this to my Tips and Tricks on Power Query Editor at #PowerPlatformSummit2019. Question though--is there a way to avoid the APPLY CHANGES step? Or could I automate it in some way?
awesome! I'm not aware of a way to avoid the apply changes step 🤔
@Patrick - how can I pass the output of one query to multiple other queries in PowerBI? I am using COSMOS DB.