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

[@shreekantgosavi4726]

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

[@michaeldoyle4222]

This video is absolute gold dust and has saved me a huge amount of time. Thanks

[@dataplatformcentral3036]

Glad it helped! Feel free to like, subscribe and share across

[@lzhao7573]

Can the Start Date and End date slicers showing as a calendar date picker instead of dropdown list?

[@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

[@JaipalAnugandula]

Great video. One Question - Can we take date range slicer instead of selecting the particular date in StartDate and EndDate Slicers? Thanks!

[@dataplatformcentral3036]

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

[@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

[@kevinmarti2099]

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.

[@dataplatformcentral3036]

Dynamically assign from where? In the demo parameters are bound to a table

[@msuk-r4b]

Instead of drop down slicer, can’t we use the date picker?

[@dataplatformcentral3036]

Nope. By default once a parameter is bound to table, the values will be populated into a dropdown

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

[@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?

[@hadialmumayez999]

does it work on power bi server?

[@dataplatformcentral3036]

Are you asking about the On-Premise component Power BI Report Server?

[@robertosiga]

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

[@dataplatformcentral3036]

You're welcome :) Feel free to like and share across

[@tatsianastarastsenka6251]

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

[@dataplatformcentral3036]

Yes I have You just need to modify the query accordingly to parse list of values for multi-select

[@cosminmoga3037]

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

[@dataplatformcentral3036]

You will add 4th parameter also exactly like the others and generate the query dynamically based on it.

[@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

[@akashragi2347]

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

[@dataplatformcentral3036]

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

[@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! :))

[@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

[@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 [e-mail]> Will take a look Also provide scripts for creating the tables in SQLServer

[@yuanwang750]

I ran the same issue. Did you solve it?

[@deborahhaagenson549]

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!

[@dataplatformcentral3036]

Dynamic M query parameters will work only in DirectQuery mode and the source system should support it. What source system are you using?

[@shreyansjain9276]

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?

[@dataplatformcentral3036]

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.

[@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

[@SiddharthGupta-x7n]

Does it work if the datasource is Oracle SQL?

[@dataplatformcentral3036]

Havent tried it myself. Ideally it should support all directquery datasources and Oracle is specified as a directquery datasource

[@oghdgy1552]

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?

[@dataplatformcentral3036]

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?

[@vinayak_sharma]

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.

[@dataplatformcentral3036]

What is your source system? Are you using oAuth based data sources?

[@Akshay-Mishra]

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?

[@dataplatformcentral3036]

You need to add select all functionality for that and make it default

[@Akshay-Mishra]

@@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?

[@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.

[@mp_dev2000]

Awesome explanation!! You are a legend mate

[@dataplatformcentral3036]

Glad you liked it! Feel free to like, share and subscribe :)

[@sahuanalogchannel]

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

[@dataplatformcentral3036]

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

[@MattiaRepossi]

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)

[@dataplatformcentral3036]

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

[@MattiaRepossi]

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

[@dataplatformcentral3036]

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

[@MattiaRepossi]

@@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

[@dataplatformcentral3036]

No problem :) Just specified the point. That's all

[@earthdigit]

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.

[@dataplatformcentral3036]

Where are you seeing this error? In PowerQuery editor? Are you using source query or binding to a table?

[@shrt50]

Great explanation i was actually looking for this ...

[@dataplatformcentral3036]

Glad that you found it useful Feel free to like, share and subscribe :)

[@jenyura]

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

[@dataplatformcentral3036]

Glad that you found it useful :) Sure. Will use bigger font/screen size for future videos Feel free to like, share and subscribe!

[@dalwingreen1986]

Thank you so much ! Excellent video.

[@dataplatformcentral3036]

You are welcome! Glad you found it helpful Feel free to like, share and subscribe

[@huseyincrak8171]

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.

[@dataplatformcentral3036]

Sounds like an Oracle error. Can you post the full error please?

[@huseyincrak8171]

@@dataplatformcentral3036 Hello, the error is: "ORA-00904: invalid identifier" I get this error when I click on "Search" property from slicer.

[@dataplatformcentral3036]

This is an Oracle error. Which search property are you referring to?

[@huseyincrak8171]

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

[@dataplatformcentral3036]

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.

[@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?

[@karlaguerra1602]

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

[@dataplatformcentral3036]

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

[@rajeshkumar-qx5bx]

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

[@dataplatformcentral3036]

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?

[@ravinkabhi]

you are a legend brother

[@dataplatformcentral3036]

Thanks Feel free to like, share and subscribe :)

[@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

[@alexzw1686]

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

[@dataplatformcentral3036]

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?

[@sivasiva5611]

Hi Sir, Unable to see bind parameter . I am using store procedure and passing the parameter in my query ( Using import mode)

[@dataplatformcentral3036]

Dynamic M query parameters is only available for Directquery sources not for Import mode.

[@DHARMYOGCOM]

dhunyawad

[@dataplatformcentral3036]

You're welcome :)

[@sohailansari9337]

Can we use this to SQL Store procedure please assist 🙏

[@dataplatformcentral3036]

Yes, of course You can use it

[@sohailansari9337]

@@dataplatformcentral3036 where you can download the pbix file..?🙏

[@dataplatformcentral3036]

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

[@13akanksha]

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

[@dataplatformcentral3036]

Are you using a directquery based datasource?

[@sharavananp5570]

fantastic, awesome explanations, and great effort !!

[@dataplatformcentral3036]

Thanks Glad that you liked it :) Feel free to share and subscribe to get such useful tips

[@sreejithdas1766]

Very useful sir

[@dataplatformcentral3036]

Thank you Please like, share and subscribe :)

[@arunkoshyg]

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

[@dataplatformcentral3036]

Yes Its dynamic based on slicer value selection

[@hariharasudhan599]

awesome, you are genius.

[@dataplatformcentral3036]

Thank you so much 😀 Feel free to like, share and subscribe

[@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

[@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

[@patrickbcox]

Very helpful, Thanks!

[@dataplatformcentral3036]

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

[@amarchandbatwalkar5634]

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

[@dataplatformcentral3036]

Sorry how is it different from this concept, except for that fact that its used inside dataflow instead of PQ editor here?

[@BranislavSlavkovský]

Thank you very much my friend!

[@dataplatformcentral3036]

You are very welcome Feel free to like, share and subscribe :)

[@anuragbawankar685]

Thanks a lot Sir !

[@dataplatformcentral3036]

You're welcome :) Feel free to like, share and subscribe

[@KD-ph3vi]

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

[@dataplatformcentral3036]

What is the issue you're facing? Can you explain?

[@pauf.9111]

Thank you very much !!

[@dataplatformcentral3036]

You are welcome! Feel free to like, follow and subscribe :)

[@dineshzoomyt8879]

little complexity in the concept explained.

[@dataplatformcentral3036]

Which part is the complexity?

[@irmdev595]

25 MINUTES is definitely not a quick tip

[@dataplatformcentral3036]

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