Power Bi : Setting up Dynamic Parameters
Vložit
- čas přidán 30. 07. 2024
- This basic tutorial walks you through the set-up of dynamic parameters against T-SQL data sources. This is available for Direct Query.
UPDATE Use Dynamic Parameters from Row Level Security username() or userprincipalname()
• Power Bi : Using Row L...
0:00 start
1:26 Importing Data
2:33 Filter Values
4:20 Create the Parameter
6:39 Working on Custom Query
10:50 Binding the Parameter
13:10 Closing Thoughts - Věda a technologie
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!
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!
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.
This is great! I having a difficult time figuring this out. Thanks for sharing
Great explanation, definitely interested on further videos incorporating row level security.
czcams.com/video/vyR0uAbLdTk/video.html&ab_channel=DapperDash
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
Thanks for the tutorial
Thank you for sharing !!!
Thanks a lot for your work!
Great Explanation, provide more videos on dynamic connections. thank you from India
That's Great explanation.
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?
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.
Good one . Cheers!
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?
Great Video! Can you do a video that pass multiple values into the query.
Thanks! Very helpful. Now I just need to get it to pass the username to the query.
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!
Sensacional!!!!! Muito obrigado.
Hi Please help me, I want update query parameter value based on card visual dynamically. How can i do it.
Can we implement this for redshift dB
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?
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?
Great Video! Do you have any video where I can multi select values?
Thanks Dash!, can we implement this with Oracle stored proc
Perfect
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: czcams.com/video/vyR0uAbLdTk/video.html
How to pass multiple values in the parameter
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.🤩
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
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.
This is an excellent tutorial! Can you please show me how to pass multiple countries to a query?
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
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 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:
czcams.com/video/vNK4xygN8Xw/video.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
😎 you are 👑
More content on this content
Thanks for the love Chris. Hope all is well.
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 📅!!!!
Really wow :)