This is one of most underated video when it comes to advanced power bi Great work sir
@dataplatformcentral3036 Жыл бұрын
Thanks a lot! Feel free to follow the channel. Like the videos if you find it useful and share across
@michaeldoyle42227 ай бұрын
This video is absolute gold dust and has saved me a huge amount of time. Thanks
@dataplatformcentral30367 ай бұрын
Glad it helped! Feel free to like, subscribe and share across
@robertosiga Жыл бұрын
Very good! Thank you and greetings from the friend of Brazil
@dataplatformcentral3036 Жыл бұрын
You're welcome :) Feel free to like and share across
@hariharasudhan5992 ай бұрын
awesome, you are genius.
@dataplatformcentral3036Ай бұрын
Thank you so much 😀 Feel free to like, share and subscribe
@mp_dev20005 ай бұрын
Awesome explanation!! You are a legend mate
@dataplatformcentral30364 ай бұрын
Glad you liked it! Feel free to like, share and subscribe :)
@shrt502 жыл бұрын
Great explanation i was actually looking for this ...
@dataplatformcentral30362 жыл бұрын
Glad that you found it useful Feel free to like, share and subscribe :)
@dalwingreen19867 ай бұрын
Thank you so much ! Excellent video.
@dataplatformcentral30366 ай бұрын
You are welcome! Glad you found it helpful Feel free to like, share and subscribe
@lzhao75732 ай бұрын
Can the Start Date and End date slicers showing as a calendar date picker instead of dropdown list?
@sharavananp55702 жыл бұрын
fantastic, awesome explanations, and great effort !!
@dataplatformcentral30362 жыл бұрын
Thanks Glad that you liked it :) Feel free to share and subscribe to get such useful tips
@ajitverma6047 Жыл бұрын
Hello , this very helpful , can we use the same thing for RLS in direct query mode?
@dataplatformcentral3036 Жыл бұрын
Can you elaborate on what you mean by RLS in direct query? May be give more insights on the use case
@kumarprabhu Жыл бұрын
Lets say we have a user table which contains all the user info...can we somehow use Userprincipalname dax function to parameterize and bind the user name table to M query and use it to fetch only the records belongs to the user...This is what Row level security is..so is it possible with dynamic M query parameters?
@dataplatformcentral3036 Жыл бұрын
You mean this? radacad.com/dynamic-row-level-security-with-power-bi-made-simple
@ravinkabhi8 ай бұрын
you are a legend brother
@dataplatformcentral30367 ай бұрын
Thanks Feel free to like, share and subscribe :)
@user-ye2jh9of1x10 ай бұрын
Thank you very much my friend!
@dataplatformcentral303610 ай бұрын
You are very welcome Feel free to like, share and subscribe :)
@kevinmarti20992 жыл бұрын
Hi there Is there a way to dynamically assign a value to the parameter like using a meassure which returns a value? I know thats currently not available because you cannot bind a meassure but would absolutelly change my game.
@dataplatformcentral30362 жыл бұрын
Dynamically assign from where? In the demo parameters are bound to a table
@user-ol5bc6sc1o7 ай бұрын
Great video. One Question - Can we take date range slicer instead of selecting the particular date in StartDate and EndDate Slicers? Thanks!
@dataplatformcentral30366 ай бұрын
Nope As of now, you need to have separate date filters for start and end dates and use the values in the M query for filtering
@patrickbcox2 жыл бұрын
Very helpful, Thanks!
@dataplatformcentral30362 жыл бұрын
You're welcome :) Glad that you found it helpful Feel free to like, subscribe and share across.. Click the bell icon to get notifications on weekly videos
@boo-ke6wr Жыл бұрын
It looks like you are using Power BI desktop as I am as well but I do not have the 'Bind to parameter' feature. I looked at documentation and I believe its only for the cloud version. My settings do not have even have the ability to enable Preview Features. Please verify as to whether this is the case as I saw another article posted by Microsoft using the 'Bind to parameter' and never mention if this feature is not on Desktop (onprem) and just on cloud. Video is great though! Thanks.
@dataplatformcentral3036 Жыл бұрын
Nope In my demo, I was using Power BI Desktop version. It was a preview feature at that time so may be something might have changed since then. Also the dataset has to obviously DirectQuery based one
@jenyura2 жыл бұрын
Very useful. Would be great if you could increase screen when you type. I have small screen and cannot see text
@dataplatformcentral30362 жыл бұрын
Glad that you found it useful :) Sure. Will use bigger font/screen size for future videos Feel free to like, share and subscribe!
@vinayak_sharma2 жыл бұрын
Thanks for the info. It worked fine on PowerBi desktop but not after publishing it to power bi service. Getting error "the connection string used for the mashup data source is invalid or the gateway needs to be upgraded to the latest version" Gateway is have double checked and it is updated.
@dataplatformcentral30362 жыл бұрын
What is your source system? Are you using oAuth based data sources?
@sivasiva56117 ай бұрын
Hi Sir, Unable to see bind parameter . I am using store procedure and passing the parameter in my query ( Using import mode)
@dataplatformcentral30366 ай бұрын
Dynamic M query parameters is only available for Directquery sources not for Import mode.
@deborahhaagenson5492 жыл бұрын
Hello - I am not able to create a table to store the calendar dates to select from and bind to the parameters. Do you know if Power Query added the feature to create a table or removed it at some point. If so do you happen to know about when that might have happened. I really need this to work. We need to limit our SQL data. If I use Import mode I don't have the option to bind the data. I am assuming I'm missing that option in Relationships when in Import Mode. In Direct Query Mode I can't create the tables to bind. Any suggestions or information would be appreciated. Thanks!
@dataplatformcentral30362 жыл бұрын
Dynamic M query parameters will work only in DirectQuery mode and the source system should support it. What source system are you using?
@user-qe2tg2pg4c5 ай бұрын
Instead of drop down slicer, can’t we use the date picker?
@dataplatformcentral30364 ай бұрын
Nope. By default once a parameter is bound to table, the values will be populated into a dropdown
@oghdgy15522 жыл бұрын
Hi so I have been trying to do this for work but I coming across the issue where “Bind to Parameters” is not available. I am using direct query to a SQL server and have the preview features set properly. I keep seeing online that you aren’t able to do this with SQL server however you just did it. Any ideas?
@dataplatformcentral30362 жыл бұрын
It was released as a preview feature when I did this video. Are you sure you're having the latest version of Power BI Desktop?
@pauf.911110 ай бұрын
Thank you very much !!
@dataplatformcentral30369 ай бұрын
You are welcome! Feel free to like, follow and subscribe :)
@Patiencelad Жыл бұрын
Very clear explanation! Thank you! Unfortunately, I still can't get this to work. Bind to Parameter on my slicer dim is selected, but it's not binding. Any ideas? I have the latest PBI version. No syntax errors. (single) Parameter is text and &'d with single quotes in the SQL (Server).
@dataplatformcentral3036 Жыл бұрын
Can you share your pbix file to ssonlinetrain@gmail.com> Will take a look Also provide scripts for creating the tables in SQLServer
@yuanwang750 Жыл бұрын
I ran the same issue. Did you solve it?
@ritzky100 Жыл бұрын
Hi i am using a posgreSQL database. The creation of the parameters works fine but i cant create new tables for the StartDateValues as long as i am in Direct Query Mode. This seems only to be possible in Import Mode. Do you have any suggestions how to avoid this problem?
@dataplatformcentral3036 Жыл бұрын
The tables are not created in datasource but you can create it within Power BI using DAX code and it can be in dual mode
@tanaytupe5029 Жыл бұрын
Is there a reason I don't see "Bind to parameter" in the "Advanced" section.
@dataplatformcentral3036 Жыл бұрын
Are you sure you're using a DirectQuery based source?
@shreyansjain92762 жыл бұрын
Hello. Can you please tell me that if the data is fetched as a direct query into power BI is not a table but a view created in the source.. In my case it is Snowflake tool... Still does Dynamic M query parameter can be applied on the same with the help of slicers?
@dataplatformcentral30362 жыл бұрын
From the DQ source it can be based on a query from a table or from a view Whether or not Snowflake supports dynamic parameter you need to check.
@muhammadsuleman7824 Жыл бұрын
im facing Message=Conversion failed when converting date and/or time from character string.
@dataplatformcentral3036 Жыл бұрын
This is mostly attributed to date format you're using. It might be conflicting with your region/locale setting. One way to avoid this is to always pass it in unambiguous formats like ISO based yyyyMMdd etc
@gauthamn1603 Жыл бұрын
Hi sir video looks good. I have a stored procedure, i need to pass two parameters dynamically to get the output. Will this solution work ?
@dataplatformcentral3036 Жыл бұрын
Yes it will Just pass the parameters through a string just like its explained in the video
@tatsianastarastsenka625111 ай бұрын
Hi, Thank you for the video. It's great. Have you tried with multi-selection for parameters of date type? I really need this.
@dataplatformcentral303611 ай бұрын
Yes I have You just need to modify the query accordingly to parse list of values for multi-select
@anuragbawankar6852 жыл бұрын
Thanks a lot Sir !
@dataplatformcentral30362 жыл бұрын
You're welcome :) Feel free to like, share and subscribe
@arunkoshyg10 ай бұрын
Nice presentation. Its a dynamic report isn't it. I mean values changing on real time basis.
@dataplatformcentral303610 ай бұрын
Yes Its dynamic based on slicer value selection
@DHARMYOGCOM Жыл бұрын
dhunyawad
@dataplatformcentral3036 Жыл бұрын
You're welcome :)
@akashragi23472 жыл бұрын
Can I Use this functionality for changing the database names ? I need to allow users to change the database names using parameters in Power BI
@dataplatformcentral30362 жыл бұрын
For changing database names you can make data source settings expression driven. But you cant let users change the database using Dynamic M query parameters as of now
@sreejithdas1766 Жыл бұрын
Very useful sir
@dataplatformcentral3036 Жыл бұрын
Thank you Please like, share and subscribe :)
@karlaguerra16022 жыл бұрын
Hi! I followed this video step by step but I´m getting the error: "Query (2, 14) The M parameter 'StartDate' not defined in model or M parameter type is not valid." Do you have any idea what is the problem? Thanks
@dataplatformcentral30362 жыл бұрын
What did you name your M parameter? I named it StartDate in the example. The error looks like the name given was different. Can you check if parameter name coincides with what you used in M code and also the data types are correct? I created parameter with Date/Time type and hence I used DatetIme.ToText in my M code for converting to text
@aoraveed Жыл бұрын
Good explanation, Thank you so much. BTW, how I add another Multi-select M Query Filter in the same query?
@dataplatformcentral3036 Жыл бұрын
The same way as shown Extend the query to add dynamic part based on second parameter filter
@aoraveed Жыл бұрын
@@dataplatformcentral3036 Thank you very much. I can do it now! :))
@srlabhe Жыл бұрын
Nice info, How would I add date flgs as dynamic , my Date Dim has many date flags and I wanted to change M query in such a way that If I use any of date flags it should filter data accordingly
@dataplatformcentral3036 Жыл бұрын
Can you elaborate? How you need to write logic based on date flags?
@aryansena7290 Жыл бұрын
Is not possible for import mode? I have the same requirement in import mode.
@dataplatformcentral3036 Жыл бұрын
Not possible in Import mode. Dynamic M query parameter is supported only for directquery as of today.
@bensalazar5763 Жыл бұрын
great video, i was able to set up the parameter and include in my quey as well as bind a table and create a slocer with it. The issue im having is that when i select the value in the slicer it does not refresh my data with the new value. I know that my parameter is working because when i change it on the back end in the power query editor it refreshed the data correctly, any one have this same issue ?
@dataplatformcentral3036 Жыл бұрын
Glad that you found it useful Feel free to like, share and subscribe :) What is your source? Also I hope this is DirectQuery based report
@cosminmoga303711 ай бұрын
Great video sir!. What if I need a 4th parameter that is let's say an item form that store? How can I make the store slicer, filter the 4th parameter? Right now I am stuck at this for more than 2 months and I cannot find a way to do it. THX! Basicaly I need the 4th slicer to be filtered by the store name and only after that to send the query
@dataplatformcentral303611 ай бұрын
You will add 4th parameter also exactly like the others and generate the query dynamically based on it.
@user-pi2rp7tk4u10 ай бұрын
Does it work if the datasource is Oracle SQL?
@dataplatformcentral303610 ай бұрын
Havent tried it myself. Ideally it should support all directquery datasources and Oracle is specified as a directquery datasource
@Akshay-Mishra2 жыл бұрын
Hi, Thanks for the video. I have a case where let's say User doesnot want to select any value from the slicer, and he expects to see all values in the result. But in my case I see that if no value is selected in the slicer, the result shows only those rows which corresponds to initial parameter single value that was set. How can we overcome this?
@dataplatformcentral30362 жыл бұрын
You need to add select all functionality for that and make it default
@Akshay-Mishra2 жыл бұрын
@@dataplatformcentral3036 So the default functionality of No selection means all Selection will not work with dynamic m query, right? Are there any workaround that we can use in the query itself?
@rajeshkumar-qx5bx2 жыл бұрын
Hello sir, thanks for the video. I have tried the same but I'm seeing the interaction not happening with date slicer and the data available on the grid. Though I bind the parameter, the interaction is not happening. I could see only the data only showing for the default value. I'm not sure where I made the mistake. Can you please assist me. Thanks
@dataplatformcentral30362 жыл бұрын
I've showed you step by step video Can you check it and see which step you missed? How did you create the slicer?
@yuanwang750 Жыл бұрын
Hi I ran into the same issue, did you solve it?
@earthdigit2 жыл бұрын
First, I really liked your video. I am using an ODBC SQL query. The error is "Expression.Error: We cannot convert a value of type Record to type Text'. The SQL as displayed gives the string as expected. This is hitting an oracle db. Any ideas? Thank you. I am still new to PBI so lots to learn and will be back to your channel.
@dataplatformcentral30362 жыл бұрын
Where are you seeing this error? In PowerQuery editor? Are you using source query or binding to a table?
@13akanksha10 ай бұрын
I cannot find the option on bind to parameter, why so ??
@dataplatformcentral303610 ай бұрын
Are you using a directquery based datasource?
@KD-ph3vi2 жыл бұрын
I have followed same steps as per vdo, but at the end data is not filtering as I filter the data
@dataplatformcentral30362 жыл бұрын
What is the issue you're facing? Can you explain?
@sahuanalogchannel2 жыл бұрын
@Sir, How will the page level filter will work here. Can we use page level filter above this.
@dataplatformcentral30362 жыл бұрын
Page level filter will work same as in other cases. Page level filter gets applied after data gets extracted from source using dynamic M query
@tharanya Жыл бұрын
Hi sir will RLS work for these type of reports
@dataplatformcentral3036 Жыл бұрын
Nope They wont Its clearly documented here learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters which says this Dynamic M query parameters don't support row-level security (RLS).
@huseyincrak81712 жыл бұрын
Hello, thanks for video. I have applied dynamic M query parameter exactly the way you told in the video but I have encountered a problem. I have created a slicer and put my field into it, but when I click on the "Search" on the slicer, it gaves me ORA-00904 error. Any guess related to this error? Thanks in advance.
@dataplatformcentral30362 жыл бұрын
Sounds like an Oracle error. Can you post the full error please?
@huseyincrak81712 жыл бұрын
@@dataplatformcentral3036 Hello, the error is: "ORA-00904: invalid identifier" I get this error when I click on "Search" property from slicer.
@dataplatformcentral30362 жыл бұрын
This is an Oracle error. Which search property are you referring to?
@huseyincrak81712 жыл бұрын
@@dataplatformcentral3036 I put a slicer in Power BI, and I want to search items instead of selecting them. But when I type some characters it gives me error. When I remove "Search" property from the slicer, it works.
@dataplatformcentral30362 жыл бұрын
That search bar functionality will work well for Imported tables as all values are readily available under Power BI model. For DirectQuery it will try to fetch the value from database as you search and may be the query sent for Oracle server may have an issue. Your best bet is to check the query it sends.
@MattiaRepossi2 жыл бұрын
Hello, Great video. Question: I have a table clientID that based on the user entering the report will see certain results. With this dynamic parameter M can I update from SQL all the data of only a respective cliendID? (For now I always have to load massively on all clientIDs)
@dataplatformcentral30362 жыл бұрын
So you want the data to be automatically displayed based on logged in user credentials on your report? Then best way to do that is dynamic RLS as per below link radacad.com/dynamic-row-level-security-with-power-bi-made-simple The dynamic M parameters is for DirectQuery sources where user can interactively control data based on one or more slicers which are bound to M code parameters. Here there's no question of loading any data to the PBI model as it is all DirectQuery
@MattiaRepossi2 жыл бұрын
@@dataplatformcentral3036 thanks! So you can’t use dynamic M query parameter for import mode. Because i already use RLS but i would like to update data from my sources with a parameter (to not update all data every times but only with a selected parameter)
@dataplatformcentral30362 жыл бұрын
I dont think you viewed the video completely as that was clearly mentioned in the video :) Dynamic M query is for parameterizing the M code based on slicer selections for DirectQuery sources This is clearly specified in official documentation as well docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters which states this "With Dynamic M Query Parameters, model authors have more control over how filter selections get incorporated into DirectQuery source queries."
@MattiaRepossi2 жыл бұрын
@@dataplatformcentral3036 I actually saw it all and as I said at the beginning I thank you for the great video. Unfortunately, however, I have a bit of difficulty with the language so I may have missed some steps. Sorry
@dataplatformcentral30362 жыл бұрын
No problem :) Just specified the point. That's all
@alexzw16862 жыл бұрын
is this functionality gone? After last powerbi upgrade functionality seems to be not available anymore?
@dataplatformcentral30362 жыл бұрын
This was still in preview the last time I checked. Not sure if any changes have happened since then
@jatinagrawal5125 Жыл бұрын
@@dataplatformcentral3036 : Is this feature available for snowflake as a data source. When i try to use it, it gives me an error : The current version of the data source do not support this feature, consider upgrading the data source
@dataplatformcentral3036 Жыл бұрын
The official document doesnt specify any restrictions for snowflake so I would expect it to work as it does for all other SQL sources. What connector are you using to connect?
@amarchandbatwalkar56342 жыл бұрын
can you please create video for multi select m parameter to data flow of power bi
@dataplatformcentral30362 жыл бұрын
Sorry how is it different from this concept, except for that fact that its used inside dataflow instead of PQ editor here?
@NikitaSharma-ds4eu Жыл бұрын
Hello, I am trying to create two parameters and pass them as date range in a sql function query that returns the output of the view based on date range. I have followed the video, but when selecting start date and end date from slicer the matrix does not interact, it adjusts data according to the startDate & EndDate entered in parameter manually. It would be really helpful if gotten some advice. Thanks :)
@dataplatformcentral3036 Жыл бұрын
First question is whether your report is DirectQuery report or Import?
@NikitaSharma-ds4eu Жыл бұрын
@@dataplatformcentral3036 It is a DirectQuery Report.
@NikitaSharma-ds4eu Жыл бұрын
@@dataplatformcentral3036 How to resolve?
@dataplatformcentral3036 Жыл бұрын
How is your function query written currently inside M code?
@NikitaSharma-ds4eu Жыл бұрын
@@dataplatformcentral3036 The code for accessing data using sql function as M query is written as follows : let SD = DateTime.ToText(startDate,"yyyy-MM-dd HH:mm:ss"), ED = DateTime.ToText(EndDate,"yyyy-MM-dd HH:mm:ss"), Source = Sql.Database("MyServerName", "MyDatabaseName", [Query="Select * From MyFunctionName('"& SD &"','"& ED &"')#(lf)GO"]) in Source
@hadialmumayez99911 ай бұрын
does it work on power bi server?
@dataplatformcentral303611 ай бұрын
Are you asking about the On-Premise component Power BI Report Server?
@sohailansari93372 жыл бұрын
Can we use this to SQL Store procedure please assist 🙏
@dataplatformcentral30362 жыл бұрын
Yes, of course You can use it
@sohailansari93372 жыл бұрын
@@dataplatformcentral3036 where you can download the pbix file..?🙏
@dataplatformcentral30362 жыл бұрын
Unfortunately I dont have the pbix file handy now I create videos with the intention of people self-trying and learning it from scratch which is why I do it in a detailed and step-by-step way :)
@dineshzoomyt8879 Жыл бұрын
little complexity in the concept explained.
@dataplatformcentral3036 Жыл бұрын
Which part is the complexity?
@irmdev5952 жыл бұрын
25 MINUTES is definitely not a quick tip
@dataplatformcentral30362 жыл бұрын
But still much shorter than the long step by step videos :) The intention was to try to include all the required details within minimal time in a step by step way to help anyone trying to configure
@Patiencelad Жыл бұрын
@@dataplatformcentral3036 You did a great job!
@dataplatformcentral3036 Жыл бұрын
Thanks @Patiencelad
@anandkumarmadireddi7515 Жыл бұрын
Great video and awesome explinations. But when I check the backend SQL, both Dynamic M Query and normal filter with in report are having similar query profiles (query execution plan) for database. (in this case Snowflake is the database). Even if we create the report with out dynamic M query, the filters are being pushed down to database query, so that it only return the specific filtered data back to PBI. So can you please explain if there is any specific reason to go for dynamic M query?
@dataplatformcentral3036 Жыл бұрын
What do you mean by normal filter? If you mean filter set at report/page level then these are static and cant be changed at runtime by the end users. Thats why the above feature is specified as Dynamic M parameters and it works only for DirectQuery datasets