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 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.
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.
@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
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.
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.
@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
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
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 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?
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!
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
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 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.
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).
@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.
@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?
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 :(
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
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
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 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
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?
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?
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.
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.
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.
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.
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&")
@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?
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, 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?
@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
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 - 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.
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?
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; 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?
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.
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!
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?
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?
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
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).
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
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.
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?
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 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!).
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 ?
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???
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.
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 :(.
instead of going to edit parameter in power BI desktop and change the values over there, can't we put that option in the any slicer so that we can easily change that using any visual like slicer
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?
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
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.
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
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.
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.
@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!
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.
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.
How to include the "select all" option in Manage Parameters, manage parameter only one item to be selected at one time
@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
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.
Thanks for wonder videos. I like your statement patrick
"I am not lazy, I am efficient".
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 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?
It's a stunning concept. But can we use those parameters as slicers on the dashboard?
Thank you for your video! Is there a way that I can choose two values? Example: I want to select France AND Germany?
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!
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
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?
Love your videos, you guys are one of the reasons that PowerBI is so good
Love you Patric you got the best youtube power bi tutorial !!!!
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?
will users be able to pass similar paramters also over service?
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).
@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.
@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?
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 :(
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
@Patrick - can u just give a slicer with dropdown will this work as same .
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 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
why do we not load all and then add a filter on visual? is it help to shorten the refresh process?
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 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
can you select multiple values from the list
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?
is it possible for end user edit parameters in the power bi service (SaaS)?
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.
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.
Can we get the Drop-down in Query Editor so that I can pass it on function while executing
Thanks
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.
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.
thanks for this. what if i want to select multiple countries as parameter then how it will work ?
Third time back to this one! Really useful
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 ?
@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.
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, 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?
Outstanding! Glad I discovered this video, Patrick. Can be used in many applications. Thank you!
@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
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.
Do we have a multi select option here can i choose more than 1 parameter value
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
@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 you use the same method to add date parameters/ranges?
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?
How can I display the value of query parameters as a slicer in the report?
Guy in a cube are best for everything in power bi
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; 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?
Is there a way to pass the parameter dynamically to the stored procedure from the report to pull the data?
Absolutely fanning! Love your videos.
Will it work in the service or only on pbi desktop?
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.
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?
If you have a cell with multiple value for skills like let say Java, MySQL, ... etc how would you you convert to list
Great video, as usual. Thanks. Can we put the user's email as a parameter?
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?
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?
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.
Hi bro, Is it doable to filter the dashboard using this list in Slicer ??
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
How are you showing the flags in the left, based on the filter?
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).
Is it possible to place a Card into the page to display currently selected parameter?
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?
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.
@Patrick - how can I pass the output of one query to multiple other queries in PowerBI? I am using COSMOS DB.
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?
Can I have a query list with more than one column? I want to use ID instead of the label. Is it possible?
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 just searching to solve a problem and you got me a great ideia. Thks
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!).
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 ?
How to trigger the parameter value based the slicer selection instead of passing the parameters from edit parameter from the menu
Can we make parameter where all value is also an option?
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?
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???
what app do you use for the blue highlight on the cursor? you guys are SO great with interacting with the community...
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.
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 :(.
instead of going to edit parameter in power BI desktop and change the values over there, can't we put that option in the any slicer so that we can easily change that using any visual like slicer
Did you get a solution on it?
I have same question
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?
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.
i want to use these parameters from URL ? How can i?
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
Hi! @Patrick, how can I implement date or timestamp values to list? Are there any date or calendar features instead data from another table?
How can we give this parameter access to end user through slicer or filter... Is that possible?
This i want to know too. Have you find a way yet?
@@LucasvanDijk-yr6xn it is possible using direct mode and not possible with import mode
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?