How To Use Dynamic M Query Parameters In Power Bi Desktop?

[@1989sherif]

I'm working on a project at work to build a self service portal for end users, and I need end users to be able to input a parameter which would then be used by DirectQuery to fetch data from oracle db. Exactly what i needed. Great video. Thank you!!!

[@IloveQuran810]

I like the way you have created M-Query Parameter. I found it to be a clever and effective solution. Well done! Thank You!. I am eagerly looking forward to watching another new video!.

[@BIConsultingPro]

Thank you! Much appreciated

[@leonvonmoltke7923]

Amazing Stuff!

[@cosminmoga3037]

This is all great. But, what if I need to use multiple parameters that have to be filtering out each other on the interface, not in the filtering pane. I need basically 4 parameters - Plant - Production Area - Business Area - Line Name, to work as injectable parameters and also to filter out each other. Is this possible? I'm looking for an answer to this question for 3 months now. Thank you for your work!

[@anilyadav-rt4sr]

Nice topic thanks for creating this video.

[@BIConsultingPro]

Thanks for the feedback

[@tanababa]

Great demo. Thank you 🙂

[@mirrrvelll5164]

This was a great video. You got a new follower!

[@paulinevaret7277]

Great video! I was hoping to reproduce it to a T but my system tells me that direct query doesn't support the usage of the parameter (at the step where you work in the advanced editor to input the parameter). It forces me to switch back to import modus, where I can't link the parameter. Not sure what the problem is :/

[@srivatsabommu6267]

am facing the same issue. I wanted my end user to pass dynamic parameter to my stored procedure but that is not happening.

[@aslanjRuns]

Stumbled upon the same issue...

[@omarezzeldin5790]

same issue did you know how to solve it ??

[@ashisdas9431]

Nice video. Is it possible to modify the parameter using input from Power BI ? Like a text box?

[@rajeshjetty-r1q]

I got a scenario where I have a stored procedure from SQL server where I'm passing two dates as inputs. When I create parameters its working fine. But I would to have my end customer to have those two dates listed on the Report and which dynamically can get passed to the stored procedure. I'm quite new to Power Bi and verified various material to handle such scenario but no luck. Any leads on this scenario.

[@samikshaadsule4682]

Do we have any functionality where we can redirect a visual to a particular module in an angular website post integration of a report in the same angular website?

[@urbandancecompany6759]

Can you help if we can pass multiple list.contains parameter in table.selectrows ? Use case to filter query with multiple slicer which are multi select , already storing them as list .. any help would be great .

[@aslanjRuns]

Is the same possible with the Import storage mode rather than the Direct Query?

[@alpajain1022]

If we want to filter the numeric value then how we will do, and i am unable to get bind option in binding

[@madhuhoneyboga]

Hi How can we get dynamic query parameters in import mode Can you please help on this Thanks

[@Sowjanyap644]

Hi, I tried this for date time parameter and it works with dropdown or a list slicer. Could you please suggest how to use this with date picker slicer and data type parameters.

[@BIConsultingPro]

Please follow the video or read more here: learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters/?wt.mc_id=MVP_356016

[@sowjanyap3267]

​@BIConsultingPro The video and the documentation talk only about datetime parameters but the same is not working when using date only parameters.

[@BIConsultingPro]

Did you read all the limitations over there?

[@astatine0085]

question; how do you make a parameter that has a 'query' type? My 'query' field is grayed-out, when I choose Type = query.

[@tatsianastarastsenka6251]

Hi, thank you for the video. It's great! Have you tried multi-select for parameters of date type?

[@BIConsultingPro]

Not yet! But you can give it a try by using in {parameter1, parameter2…}

[@vanshika553]

Great Video! But I am stuck somewhere if anyone could help me I have created the dynamic m query parameter for routeno and binded to routeno present in my routehistory table but in slicer I want to use the route name column present in my routehistory table like changing any route name in slicer also change and update M query routeno parameter value

[@kuto1]

Does this work with Databricks (using Direct Query) as well?

[@BIConsultingPro]

Yes, this should work there as well.

[@SiddharthGupta-x7n]

I am using direct query mode. I see bind to parameter, but parameter isn't coming. I am only seeing None. The example you shared, the data source is SQL Server. Does it work with Oracle SQL datasource? Thanks

[@BIConsultingPro]

Please read here: learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[@aryansena7290]

Can you please create a video for import mode with same dynamic logic?

[@BIConsultingPro]

Its only available in Direct Query mode

[@jorgegomezjr.-fe1kn]

Will this work on a api url path parameter?

[@ce_39_amaanmalik8]

Is this thing works with the APIs or not ?

[@bhakti1908]

How can I pass the value in mysql query dynamically from the report section whatever user selected in filter 1) users select a single date that date should pass on my store procedure which I am calling in power bi while fetching the data from database How can I achieve this please help..

[@galiceanugratziela9335]

Filtering is not supported in Direct Query mode. What should I do?

[@erkncvhr6570]

This is asking for converting to import mode , i manually change it and it worked , what is the set up has to be done on powerbi service

[@BIConsultingPro]

You can try using Dataflows on Power BI Service

[@dotnavneet]

But You have not shown how to use this in M-Query parameter in visualisation and allow end user to use it. Any how appreciate the effort

[@yogeshrai3039]

I am not able to see the bind the parameter option in Power BI

[@BIConsultingPro]

This only works for Direct Query

[@ajlfit]

Your table in the example is not direct query, it is “import” and you still are able to see bind parameter options

[@BIConsultingPro]

@@ajlfit Thanks for the feedback. However, I request you to watch the video again. Table is in Direct Query only. However, when I create a separate table from a Blank Query, that becomes the import one.

[@riazahmedshaik]

Great Video! How to set this up for Date?

[@BIConsultingPro]

As we did in this one, do follow the same steps and then if it doesn't work, share your work with us at [e-mail]

[@adityawadwankar1531]

did you able to do it?

[@riazahmedshaik]

Thanks, I'm able to do this. How to apply this for multi select on a Category (Eg: Stores)? and mine is a direct query. Is this possible on a direct query?

[@BIConsultingPro]

Yes, it's possible to use dynamic M query parameters for multi-select on a category like Stores in Power BI, even with DirectQuery. You need to: Create parameters in Power Query. Bind these parameters to a slicer. Ensure the M query in Advanced Editor references these parameters.

[@ducmanhdo9927]

I'm facing an error when doing filter table in transform query: "This step results in a query that is not supported in DirectQuery mode."

[@BIConsultingPro]

It’s strange. Please check the limitations under Microsoft documents. They always update something

[@gayathrivenkata621]

yeah same issue

[@slykni8]

I don't see parameter binding option in data modeling section

[@BIConsultingPro]

Your dataset should be DQ Query mode only. For more prerequisites, please visit: learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters/?wt.mc_id=MVP_356016

[@slykni8]

@@BIConsultingPro thank you, it's working

[@BIConsultingPro]

@@slykni8 Perfect!!

[@steponaszonys8385]

Everything ok until 6.30, but can't found binding option on table properties :(

[@BIConsultingPro]

Please check your data storage mode! It is only possible in Direct Query mode

[@udayuday6514]

Applying transformations in Direct Query mode is not working

[@BIConsultingPro]

Please watch the video again and try

[@Jahspecs1]

Please SLOW DOWN, you are speaking TOO FAST

[@BIConsultingPro]

Thank you for your feedback. You can also adjust the speed using settings on your video.