Forum Discussion
Dynamic M Parameter, SQL
Hi,
i was trying to connect the database (MS SQL Server) via direct query and therefore use two dynamic parameters (datetime) for the SQL query.
I have two parameters (Start and End) which are binded with the tables StartSlicer and EndSlicer.
Below is the query for my factdata, where I included the parameters Start and End. The Table is shown and no errors but...
let
filter = "
SELECT
belegid,
datum,
abteilung,
artikelid,
menge
FROM
form_belegposition
WHERE
datum
>=
'" & DateTime.ToText(Start, "dd.mm.yyyy") & "'
AND
datum
<
'" & DateTime.ToText(Date.AddDays(End,1), "dd.mm.yyyy") & "'",
Source = Sql.Database(
"db",
"slave",
[
Query = filter,
HierarchicalNavigation = true,
MultiSubnetFailover = true
]
)
in
Source...the Picture below shows the error message I get, while creating the visual with my factdata and using the SlicerStart and SlicerEnd tables.
This is my query for the table which I bind to the StartSlicer
let
filter = "
SELECT
d.Datum
FROM (
SELECT
CAST(DATEADD(DAY, n.n, b.MinDate) AS date) AS Datum
FROM (
SELECT
MIN(TRY_CONVERT(date, datum, 104)) AS MinDate,
MAX(TRY_CONVERT(date, datum, 104)) AS MaxDate
FROM
form_belegkopf
WHERE
datum IS NOT NULL
AND
belegtyp IN ('RG','GU','LV','LG')
) b
CROSS JOIN (
SELECT TOP (40000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM sys.all_objects a
CROSS JOIN sys.all_objects a2
) n
WHERE
DATEADD(DAY, n.n, b.MinDate) <= b.MaxDate
) d
",
Source = Sql.Database(
"db",
"slave",
[
Query = filter,
HierarchicalNavigation = true,
MultiSubnetFailover = true
]
)
in
Source
So far all tables are shown right and with direct query, even the binded slicer tables. Maybe someone has a hint or can correct me and my intent. You can answer in english or german.
The Model.
Thanks a lot and kind regards.
Samuel
9 Replies
Hi! s_schultes
The data can be seen because it's a DQ and issue is not recognized at PQ. But when we use the data in visuals it runs the query (DQ) again which looks out if query folding is happening. I don't have a scenario to reproduce, but I gave it a try. Please use below code if that doesn't work go through the below video in case that helps.
let
Source = Sql.Database(
"db",
"slave",
[
Query = "
SELECT belegid, datum, abteilung, artikelid, menge
FROM form_belegposition
WHERE datum >= @StartDate
AND datum < DATEADD(DAY, 1, @EndDate)
",
Parameters = [
StartDate = StartDate,
EndDate = EndDate
]
]
)
in
SourceAnkitKukreja Hi, thanks unfortunately that doesn't solved the problem. I typed the raw date values into the sql query. Now i get the data table and the visual right. Why don't these parameters have the same effect?
let filter = " SELECT belegid, datum, abteilung, artikelid, menge FROM form_belegposition WHERE datum >= '01.01.2025' AND datum < '31.12.2025' ", Source = Sql.Database( "db", "slave", [ Query = filter, HierarchicalNavigation = true, MultiSubnetFailover = true ] ) in SourceHi s_schultes , I recreated your issue with some sample data . I think the below line is causing the issue .
& DateTime.ToText(Start, "dd.mm.yyyy") & "'Usually mm stands for minutes and MM stands for Months.
I did a quick test in Power Query:
created 2 parameters :
PQuery for testing :let // ── issue SQL BuggySql = "SELECT * FROM form_belegposition WHERE datum >= '" & DateTime.ToText(Start, "dd.mm.yyyy") & "' AND datum < '" & DateTime.ToText(Date.AddDays(End,1), "dd.mm.yyyy") & "'", // ── FIXED SQL FixedSql = "SELECT * FROM form_belegposition WHERE datum >= '" & DateTime.ToText(Start, "dd.MM.yyyy") & "' AND datum < '" & DateTime.ToText(Date.AddDays(End,1), "dd.MM.yyyy") & "'", Debug = #table( {"Version", "Generated SQL"}, { {"BUGGY (dd.mm.yyyy)", BuggySql}, {"FIXED (dd.MM.yyyy)", FixedSql} } ) in Debug
OP:
you can see the mm is defaulting to 0
Change the mm to MM and check
MMmm issue.pbix
Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way fasterNatarajan_M Hi, I have tried the "MM" but I get the same error in the visuals like before. Could it be, that the parameter is automatically parsed into another datetime format yyyy-MM-dddd hh:mm:ss instead of the format from the database dd.MM.yyyy hh:mm:ss?
- Hi s_schultes,This behavior is expected with Dynamic M parameters in DirectQuery. Power BI re‑executes the source query for each visual, and Dynamic M parameters are evaluated only at that point. As documented by Microsoft, issues may not appear during Power Query preview but can surface when visuals execute the DirectQuery query. we recommend submitting a Microsoft Fabric Ideas request so the product team can review this scenario and consider improvements in future releasesThanks,Prashanth
Hi s_schultes you are able to see the data in the preview pane ?
ThanksHi s_schultes,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Natarajan_M & AnkitKukreja, Thanks for your prompt response.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportHi @s_schultes,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportWe are following up once again regarding your query. Could you please confirm if you have raised ideas with Microsoft? If not please raise idea and upvote it. Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
