Power BI Quick Tip: Using Dynamic M Query Parameters with SQL​​

Thank you very much !!

Nice presentation. Its a dynamic report isn't it. I mean values changing on real time basis.

I cannot find the option on bind to parameter, why so ??

Thank you very much my friend!

Does it work if the datasource is Oracle SQL?

Hi,
Thank you for the video. It's great. Have you tried with multi-selection for parameters of date type? I really need this.

does it work on power bi server?

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

This is one of most underated video when it comes to advanced power bi
Great work sir

Is not possible for import mode? I have the same requirement in import mode.

Is there a reason I don't see "Bind to parameter" in the "Advanced" section.

little complexity in the concept explained.

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.

im facing Message=Conversion failed when converting date and/or time from character string.

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?

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?

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

Hi sir will RLS work for these type of reports

Very good! Thank you and greetings from the friend of Brazil

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 ?

dhunyawad

Good explanation, Thank you so much. BTW, how I add another Multi-select M Query Filter in the same query?

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 ?

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 :)

Hello , this very helpful , can we use the same thing for RLS in direct query mode?

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).

Very useful sir

25 MINUTES is definitely not a quick tip

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

Thanks a lot Sir !

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?

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.

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

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!

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?

Great explanation i was actually looking for this ...

@Sir, How will the page level filter will work here. Can we use page level filter above this.

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?

is this functionality gone? After last powerbi upgrade functionality seems to be not available anymore?

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

Can we use this to SQL Store procedure please assist 🙏

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.

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.

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.

can you please create video for multi select m parameter to data flow of power bi

fantastic, awesome explanations, and great effort !!

I have followed same steps as per vdo, but at the end data is not filtering as I filter the data

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)

Very helpful, Thanks!

Very useful. Would be great if you could increase screen when you type. I have small screen and cannot see text