Hello,
I'm working on a report that gets the data from a Table-Valued Function hosted on an Azure SQL server. That function has four parameters being "TeamList", "OwnerNameList", "AnalysisBeginDate" and "AnalysisEndDate".
I'd like to use dynamic M query parameters so when users change a value in the controls displayed on the report it'll call the function with the updated parameter values. Well, just what the functionnality seems to be offering.
I succeeded in doing so for the first two Text parameters ("TeamList" and "OwnerNameList") but I'm unable to do the same for the last two. Apparently it's because they have a Date format. I'll try to share as much details as possible not only for the sake of presenting my issue but also for people trying to use dynamic M parameters because, to me, that was a struggle.
Report presentation
I'll focus on the "Team" dropdown list, one of the two that actually works.
Layout
You can see, on top of the table, I added four controls that allow the user to change the parameters sent to the SQL function. For now it only works for "Team" and "Control Form Owner".
Team table values and link to parameter
The "Team" dropdown is populated using a function call in DirectQuery mode (I've made that DirectQuery because I thought it was mandatory for dynamic M parameters though I think it only applies for the main data function call) :
let
Source = Sql.Database("<server>", "<database>", [Query="SELECT TOP 100 PERCENT *#(lf)FROM dbo.GetTeamList()#(lf)ORDER BY 1"])
in
Source
The "Team" dropdown list is a Slicer linked to the "Team" column of the "TeamListQuery" table.
Main data function
And finally here is how the value of the "TeamParameter" is sent to the main data function (also DirectQuery).
let
selectedTeams = if TeamParameter = null then
"default"
else
if Type.Is(Value.Type(TeamParameter), List.Type) then
Text.Combine({"'", Text.Combine(TeamParameter, "|"), "'"})
else
Text.Combine({"'", TeamParameter, "'"}),
selectedUsers = if UserNameParameter = null then
"default"
else
if Type.Is(Value.Type(UserNameParameter), List.Type) then
Text.Combine({"'", Text.Combine(UserNameParameter, "|"), "'"})
else
Text.Combine({"'", UserNameParameter, "'"}),
selectedAnalysisStartDate = if AnalysisBeginDateParameter = null then
"default"
else
Text.Combine({"'", DateTime.ToText(AnalysisBeginDateParameter, "yyyy-MM-dd"), "'"}),
selectedAnalysisEndDate = if AnalysisEndDateParameter = null then
"default"
else
Text.Combine({"'", DateTime.ToText(AnalysisEndDateParameter, "yyyy-MM-dd"), "'"}),
finalQuery = Text.Combine({"SELECT * FROM ",
"dbo.GetControlFormStatus(",
selectedTeams, ",",
selectedUsers, ",",
selectedAnalysisStartDate, ",",
selectedAnalysisEndDate, ")"}),
Source = Sql.Database("<server>", "<database>", [Query=finalQuery])
in
Source
The issue
If I follow the exact same steps for the "AnalysisBeginDate" and "AnalysisEndDate", by that I mean : gather the list of dates through a DirectQuery mode function call (that returns an SQL DATE column) and create a Date parameter. The "Bind to parameter" dropdown list is not displayed on the corresponding column.
let
Source = Sql.Database("<server>", "<database>", [Query="SELECT *#(lf)FROM dbo.GetAnalysisBeginDateList()"])
in
Source
And it seems to be only because the column is of "Date" type, because, if I switch the type to "Text", the "Bind to parameter" dropdown list appears in the "Advanced" section :
I've followed this article on dynamic M query parameter :
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
There is something about "Date/Time" data type in the "Limitations" section but I think it applies to the data function call, when I'm stuck upstream.
I'd also like to state that I've followed the example in this article that's using a Date table and this does not work for me. "Bind to parameter" doesn't appear as well.
I've throughly search the Web and asked a couple of AIs for help. No success.
If someone can help me make it work that'll be awesome. Otherwise I'll have to reduce my expectations.
Cheers,
L
Helpful resources
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
