Thanks, Mr. Dash! I looked at sites and forums and never found an explanation that worked for me. Your video finally got me over the problem I had! I was about 90% of the way there a couple times, but I just couldn't get it to work without your video. I had some mistakes with the Parameter settings, and I also assumed we would hook up an SQL statement to the Parameter immediately, but no: it happens when we BIND the Parameter at the end. Thanks again!
Thank you so much! I looked into this about 6 months ago and was convinced it wasn't possible. Your explanation was very clear a d I will try this on a couple of my dashboards to achieve the desired effect and response time. Thank you again!
Wow. Kudos to this video. This is the most useful application of Field Parameterization in Power BI by far. Subscribed and liked. Couldn't thank you more.
Hi my friend nice explanation but: at position 11:11 you show the setting for "Bind to parameter" but in my May 2022 version the field doesn't exist. Which version did you use that? Or is that not possible with PBI Desktop for Reporting Server?
Hello, everyone, it now works in version: 2.112.1161.0 64-bit (December 2022). You only have to wait 5 months, then the solution will come almost by itself. Now I'm happy. If you still have questions, just leave a post.🤩
Hi this was fantastic, but is there a way to change the slicer to allow for multi-value selection? I would have to somehow change the custom query so that the values are not filtered by WHERE [Field1] =, but WHERE [FIeld1] IN....?
I'm trying these steps with a custom query but using a date filter. I'm able to get it to work with a text filter but for some reason and having trouble with dates.
Great explanation! The next step is using the dynamic parameters for stored procedures instead of tables. Could you show us how you would do that? Thanks in advance.
Hey Allard. Good point. I used it recently in a stored procedure at my company. Let me look at putting a tutorial together. It's very similar to the way I set this one up.
@@DapperDash Ah, that is good news. Because I myself tried it and I cannot find a way to call a parameterized T-SQL stored procedure and bind the parameters to user-controls on the dashboard. So I see forward to your tutorial!
Sorry im new to Power BI - For Direct query, the fact table would normally have IDs (Numbers) which you would want to filter, how do you pass the lookup names to filter the fact table IDs?
I am not sure what you mean by changing the filter. But this should work with a custom query. The only reason it could fail is if you are passing multiple values. The example I shared is made for single value parameters
Thanks sir for the detailed explanation. I have some doubts to clear. Will this work in aggregations? Can we apply rls on this parameterized model? For example, I have a big dataset to query into my model. Dataset for different clients. I need to apply the dynamic RLS into the model using userprincipalname(). Then, can I use this parameters to query only for the specific client?
I have built models with RLS and Dynamic Parameters together. I don't believe you can apply an RLS rule directly on a table that is using a a field as the Dynamic Parameter, but you can build a model where some datasets leverage dynamic parameters and others RLS rules. And yes, you can use userprincipalname() as a way to dynamically filter a query. You cannot pass the DAX function into the M Query, but there are workarounds that allow you to capture the value from userprincipal() and pass it into a dynamic parameter query. I have done this before. It requires a little bit of set up so that your end user doesn't have to select a filter. In fact, they are unaware that dynamic parameters are going on in the background. Maybe we can find some time to chat and go over this.
@@DapperDash Big thanks for the reply. In my case, I have to parametarize the dataset using client name. And also the userprincipalname () will also configured for each client. Is that possible to use both parameters and rls togather in my case? And could you mention some workarounds to make this work?
Are you querying against a TSQL data source? (Azure, MSSQL, MYSQL...). I have also seen it bug where I have had to delete the parameter and recreate it.
Can you do a video on how to handle a parameter with multiple values (countries in your case)? I have a list of accounts in a PowerQuery table that I want to filter by in SQL Server, rather than pulling 1.5M records back into PowerQuery. The time difference is 2 seconds vs 20 minutes. I can't figure out the exact MCode.
Great tutorial, Do you know if this will also work for stored procedures that have parameters in them? i.e. the slicer can act as a value to pass through to a stored procedure?
Yup, you can use a date. The Microsoft documentation page on dynamic query parameters uses a date field as an example: learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
Thank you for sharing this. I am able to perform till there but facing some issue with Select all option, can you please make a video on that. Please consider SQL SERVER data base
Saddam, There is an example of "Select All" and choosing multiple values here: ruclips.net/video/vNK4xygN8Xw/видео.html&ab_channel=MicrosoftPowerBI You can see the code at 10:54 into the video.
Here is the code you need. You also have to enable "Multi-Select" on the column properties. I can create a tutorial for you if you want: let
param = if Type.Is(Value.Type(YOURFILTER),List.Type) then Text.Combine({"'", Text.Combine(YOURFILTER,"','") ,"'"}) else Text.Combine({"'",YOURFILTER,"'"}), selectAll = if Type.Is(Value.Type(YOURFILTER),List.Type) then List.Contains(YOURFILTER,"_SelectAll_") else false, query = "Select * From dbo.YOURTABLE", filter = if selectAll then " " else Text.Combine({" Where COLUMN in (", param, ")"}), finalQuery = Text.Combine({query,filter}), Source = Sql.Database("Server", "Database", [Query=finalQuery]) in Source
Very Informative Video. How can we insert a dynamic parameter like a dynamic list of individual values with brackets and inverted commas like ('A123','B123',C123') (not a cell reference or do no want to type individual values by creating a list parameter) which can fetch result by running the native SQL query from ODB. Appreciate your help or video on this!! Thanks Again!
Thanks, Mr. Dash! I looked at sites and forums and never found an explanation that worked for me. Your video finally got me over the problem I had! I was about 90% of the way there a couple times, but I just couldn't get it to work without your video. I had some mistakes with the Parameter settings, and I also assumed we would hook up an SQL statement to the Parameter immediately, but no: it happens when we BIND the Parameter at the end. Thanks again!
Thank you so much! I looked into this about 6 months ago and was convinced it wasn't possible. Your explanation was very clear a d I will try this on a couple of my dashboards to achieve the desired effect and response time. Thank you again!
Wow. Kudos to this video. This is the most useful application of Field Parameterization in Power BI by far. Subscribed and liked. Couldn't thank you more.
Thanks a lot, this tutorial really helped me. I have troubles switching from SSRS to Power BI and this is exactly what I needed to know. Just perfect!
Great explanation, definitely interested on further videos incorporating row level security.
ruclips.net/video/vyR0uAbLdTk/видео.html&ab_channel=DapperDash
This is great! I having a difficult time figuring this out. Thanks for sharing
Great Explanation, provide more videos on dynamic connections. thank you from India
This is so so good video Thanks Dapper Dash
Nicely explained
I am able to follow and will continue to watch other videos as well
Hi my friend
nice explanation but:
at position 11:11 you show the setting for "Bind to parameter"
but in my May 2022 version the field doesn't exist.
Which version did you use that?
Or is that not possible with PBI Desktop for Reporting Server?
no one has this answer
having the same problem. Has anyone found any solutions yet?
i perform query through oracle database and having the same problem, hope there is solution for this
hello guys did you fond a solution ? i cannot found thé " Bind to parameter"
Hello, everyone,
it now works in version: 2.112.1161.0 64-bit (December 2022).
You only have to wait 5 months, then the solution will come almost by itself.
Now I'm happy.
If you still have questions, just leave a post.🤩
Hi this was fantastic, but is there a way to change the slicer to allow for multi-value selection? I would have to somehow change the custom query so that the values are not filtered by WHERE [Field1] =, but WHERE [FIeld1] IN....?
I'm trying these steps with a custom query but using a date filter. I'm able to get it to work with a text filter but for some reason and having trouble with dates.
Great Video! Can you do a video that pass multiple values into the query.
Im importing via an excel doc, I dont see the bind value anywhere. Ive also updated my power bi to the latest version any thoughts?
Great explanation! The next step is using the dynamic parameters for stored procedures instead of tables. Could you show us how you would do that? Thanks in advance.
Hey Allard. Good point. I used it recently in a stored procedure at my company. Let me look at putting a tutorial together. It's very similar to the way I set this one up.
@@DapperDash Ah, that is good news. Because I myself tried it and I cannot find a way to call a parameterized T-SQL stored procedure and bind the parameters to user-controls on the dashboard. So I see forward to your tutorial!
hello all , please there 's some update on it ?
Game changer if this one can be figured out . . . Dapper Dash can you provide a video on dynamic parameters for Stored Procs????
@@allardbon1967 did you ever figure out how to do this, or a similar workaroubd? Would love to hear a solution, i'm completely stuck
Sorry im new to Power BI - For Direct query, the fact table would normally have IDs (Numbers) which you would want to filter, how do you pass the lookup names to filter the fact table IDs?
Does changing the filter work with the customerquery? If I change it nothing happens
I am not sure what you mean by changing the filter. But this should work with a custom query. The only reason it could fail is if you are passing multiple values. The example I shared is made for single value parameters
Hi Please help me, I want update query parameter value based on card visual dynamically. How can i do it.
Thanks sir for the detailed explanation.
I have some doubts to clear.
Will this work in aggregations?
Can we apply rls on this parameterized model?
For example, I have a big dataset to query into my model. Dataset for different clients. I need to apply the dynamic RLS into the model using userprincipalname(). Then, can I use this parameters to query only for the specific client?
I have built models with RLS and Dynamic Parameters together. I don't believe you can apply an RLS rule directly on a table that is using a a field as the Dynamic Parameter, but you can build a model where some datasets leverage dynamic parameters and others RLS rules.
And yes, you can use userprincipalname() as a way to dynamically filter a query. You cannot pass the DAX function into the M Query, but there are workarounds that allow you to capture the value from userprincipal() and pass it into a dynamic parameter query.
I have done this before. It requires a little bit of set up so that your end user doesn't have to select a filter. In fact, they are unaware that dynamic parameters are going on in the background. Maybe we can find some time to chat and go over this.
@@DapperDash Big thanks for the reply.
In my case, I have to parametarize the dataset using client name. And also the userprincipalname () will also configured for each client.
Is that possible to use both parameters and rls togather in my case?
And could you mention some workarounds to make this work?
Sorry it took me a while to get you the solution: ruclips.net/video/vyR0uAbLdTk/видео.html
i made sure i checked matching data types and its a direct query but i cannot see the bind to parameters option in the advanced tab
any clue why?
Are you querying against a TSQL data source? (Azure, MSSQL, MYSQL...). I have also seen it bug where I have had to delete the parameter and recreate it.
Thanks for the tutorial
Can you do a video on how to handle a parameter with multiple values (countries in your case)? I have a list of accounts in a PowerQuery table that I want to filter by in SQL Server, rather than pulling 1.5M records back into PowerQuery. The time difference is 2 seconds vs 20 minutes. I can't figure out the exact MCode.
Great Video! Do you have any video where I can multi select values?
Thanks Dash!, can we implement this with Oracle stored proc
For some reason, it not working as a database source. I have the same tables in different databases and I want to change my source by parameter.
I have a table that contains the name of all the databases on my server and I want to use it as a parameter.
Thank you for sharing !!!
Is there any way for this to work when using import?
Great tutorial, Do you know if this will also work for stored procedures that have parameters in them? i.e. the slicer can act as a value to pass through to a stored procedure?
Can we implement this for redshift dB
This is an excellent tutorial! Can you please show me how to pass multiple countries to a query?
How to pass multiple values in the parameter
what about multiselection parameter
That's Great explanation.
Thanks a lot for your work!
Would this work on date type?
Yup, you can use a date. The Microsoft documentation page on dynamic query parameters uses a date field as an example:
learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
@@DapperDash gtfoh! Man you're the 👑
Ahh there are limitations,
Can't use date slicer on it 📅!!!!
Thanks! Very helpful. Now I just need to get it to pass the username to the query.
Sensacional!!!!! Muito obrigado.
Hi this is very detailed tutorial you create which help me a lot. Would you mind how to retrieve list and put it in direct query?
Thank you for sharing this. I am able to perform till there but facing some issue with Select all option, can you please make a video on that. Please consider SQL SERVER data base
Saddam, There is an example of "Select All" and choosing multiple values here:
ruclips.net/video/vNK4xygN8Xw/видео.html&ab_channel=MicrosoftPowerBI
You can see the code at 10:54 into the video.
Here is the code you need. You also have to enable "Multi-Select" on the column properties. I can create a tutorial for you if you want:
let
param = if Type.Is(Value.Type(YOURFILTER),List.Type) then
Text.Combine({"'", Text.Combine(YOURFILTER,"','") ,"'"}) else
Text.Combine({"'",YOURFILTER,"'"}),
selectAll = if Type.Is(Value.Type(YOURFILTER),List.Type) then
List.Contains(YOURFILTER,"_SelectAll_") else
false,
query = "Select * From dbo.YOURTABLE",
filter = if selectAll then " " else
Text.Combine({" Where COLUMN in (", param, ")"}),
finalQuery = Text.Combine({query,filter}),
Source = Sql.Database("Server", "Database", [Query=finalQuery])
in
Source
Thanks a lot sir, but select all is not working in my case that's why I am worried about. I will watch this video again
@@DapperDash I just tried with SQL SERVER and it is working but with snowflake it not giving me the results I am testing it once again thanks
Very Informative Video. How can we insert a dynamic parameter like a dynamic list of individual values with brackets and inverted commas like ('A123','B123',C123') (not a cell reference or do no want to type individual values by creating a list parameter) which can fetch result by running the native SQL query from ODB. Appreciate your help or video on this!!
Thanks Again!
Good one . Cheers!
Perfect
😎 you are 👑
More content on this content
Thanks for the love Chris. Hope all is well.
Really wow :)