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
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?
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.
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).
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!
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
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?
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?
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
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?
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
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
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?
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.
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.
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
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 ?
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
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
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 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?
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)
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
@@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)
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."
@@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
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).
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, 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 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.
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.
@@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
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?
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.
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 :)
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
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 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"])
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
This is one of most underated video when it comes to advanced power bi
Great work sir
Thanks a lot!
Feel free to follow the channel. Like the videos if you find it useful and share across
This video is absolute gold dust and has saved me a huge amount of time. Thanks
Glad it helped!
Feel free to like, subscribe and share across
Very good! Thank you and greetings from the friend of Brazil
You're welcome :)
Feel free to like and share across
Thank you so much ! Excellent video.
You are welcome!
Glad you found it helpful
Feel free to like, share and subscribe
Awesome explanation!! You are a legend mate
Glad you liked it!
Feel free to like, share and subscribe :)
Great explanation i was actually looking for this ...
Glad that you found it useful
Feel free to like, share and subscribe :)
you are a legend brother
Thanks
Feel free to like, share and subscribe :)
fantastic, awesome explanations, and great effort !!
Thanks
Glad that you liked it :)
Feel free to share and subscribe to get such useful tips
awesome, you are genius.
Thank you so much 😀
Feel free to like, share and subscribe
Very helpful, Thanks!
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
Thank you very much my friend!
You are very welcome
Feel free to like, share and subscribe :)
Very useful sir
Thank you
Please like, share and subscribe :)
Hello , this very helpful , can we use the same thing for RLS in direct query mode?
Can you elaborate on what you mean by RLS in direct query?
May be give more insights on the use case
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?
You mean this?
radacad.com/dynamic-row-level-security-with-power-bi-made-simple
Thanks a lot Sir !
You're welcome :)
Feel free to like, share and subscribe
dhunyawad
You're welcome :)
Thank you very much !!
You are welcome!
Feel free to like, follow and subscribe :)
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.
Dynamically assign from where?
In the demo parameters are bound to a table
Can the Start Date and End date slicers showing as a calendar date picker instead of dropdown list?
yes
If you bind it to a table containing the dates, it will come as a dropdown list
Hi,
Thank you for the video. It's great. Have you tried with multi-selection for parameters of date type? I really need this.
Yes I have
You just need to modify the query accordingly to parse list of values for multi-select
Great video. One Question - Can we take date range slicer instead of selecting the particular date in StartDate and EndDate Slicers?
Thanks!
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
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).
Can you share your pbix file to ssonlinetrain@gmail.com>
Will take a look
Also provide scripts for creating the tables in SQLServer
I ran the same issue. Did you solve it?
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!
Dynamic M query parameters will work only in DirectQuery mode and the source system should support it.
What source system are you using?
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
You will add 4th parameter also exactly like the others and generate the query dynamically based on it.
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?
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
Good explanation, Thank you so much. BTW, how I add another Multi-select M Query Filter in the same query?
The same way as shown
Extend the query to add dynamic part based on second parameter filter
@@dataplatformcentral3036 Thank you very much. I can do it now! :))
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?
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
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?
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?
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
Can you elaborate?
How you need to write logic based on date flags?
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
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
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 ?
Yes it will
Just pass the parameters through a string just like its explained in the video
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?
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.
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.
What is your source system? Are you using oAuth based data sources?
Very useful. Would be great if you could increase screen when you type. I have small screen and cannot see text
Glad that you found it useful :)
Sure. Will use bigger font/screen size for future videos
Feel free to like, share and subscribe!
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.
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
Instead of drop down slicer, can’t we use the date picker?
Nope. By default once a parameter is bound to table, the values will be populated into a dropdown
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 ?
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
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
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
@Sir, How will the page level filter will work here. Can we use page level filter above this.
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
Does it work if the datasource is Oracle SQL?
Havent tried it myself. Ideally it should support all directquery datasources and Oracle is specified as a directquery datasource
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?
You need to add select all functionality for that and make it default
@@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?
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)
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
@@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)
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."
@@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
No problem :)
Just specified the point. That's all
Hi sir will RLS work for these type of reports
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).
Hi Sir, Unable to see bind parameter . I am using store procedure and passing the parameter in my query ( Using import mode)
Dynamic M query parameters is only available for Directquery sources not for Import mode.
Is not possible for import mode? I have the same requirement in import mode.
Not possible in Import mode. Dynamic M query parameter is supported only for directquery as of today.
Is there a reason I don't see "Bind to parameter" in the "Advanced" section.
Are you sure you're using a DirectQuery based source?
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
I've showed you step by step video
Can you check it and see which step you missed?
How did you create the slicer?
Hi I ran into the same issue, did you solve it?
Nice presentation. Its a dynamic report isn't it. I mean values changing on real time basis.
Yes
Its dynamic based on slicer value selection
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.
Sounds like an Oracle error. Can you post the full error please?
@@dataplatformcentral3036 Hello, the error is: "ORA-00904: invalid identifier"
I get this error when I click on "Search" property from slicer.
This is an Oracle error. Which search property are you referring to?
@@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.
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.
is this functionality gone? After last powerbi upgrade functionality seems to be not available anymore?
This was still in preview the last time I checked. Not sure if any changes have happened since then
@@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
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?
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.
Where are you seeing this error? In PowerQuery editor? Are you using source query or binding to a table?
Can we use this to SQL Store procedure please assist 🙏
Yes, of course
You can use it
@@dataplatformcentral3036 where you can download the pbix file..?🙏
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 :)
can you please create video for multi select m parameter to data flow of power bi
Sorry how is it different from this concept, except for that fact that its used inside dataflow instead of PQ editor here?
does it work on power bi server?
Are you asking about the On-Premise component Power BI Report Server?
im facing Message=Conversion failed when converting date and/or time from character string.
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
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 :)
First question is whether your report is DirectQuery report or Import?
@@dataplatformcentral3036 It is a DirectQuery Report.
@@dataplatformcentral3036 How to resolve?
How is your function query written currently inside M code?
@@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
I cannot find the option on bind to parameter, why so ??
Are you using a directquery based datasource?
I have followed same steps as per vdo, but at the end data is not filtering as I filter the data
What is the issue you're facing?
Can you explain?
little complexity in the concept explained.
Which part is the complexity?
25 MINUTES is definitely not a quick tip
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
@@dataplatformcentral3036 You did a great job!
Thanks @Patiencelad