Forum Discussion
Using SQL declared variables into Power BI
Thankyou, MohamedFowzan1, CPCARDOSO, kushanNa for your responses.
Hi cheid_4838,Based on my understanding, in Import mode parameters can only be applied during refresh and cannot be bound to slicers. Consequently, the Bind to Parameter option does not appear. In DirectQuery mode, dynamic M parameters are supported; however, the current SQL query contains unsupported constructs such as correlated subqueries and ORDER BY in subqueries. These constructs break query folding, preventing DirectQuery from executing the query in its present form.
If you remain in Import mode, parameters will work only at refresh time and will not be dynamic. If you require slicer driven parameters, switch to DirectQuery and simplify the SQL by using a database view or stored procedure that accepts parameters. This will enable query folding and allow binding slicers to parameters.
Additionally, please refer to the following links:
Value.NativeQuery - PowerQuery M | Microsoft Learn
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft LearnWe hope this information helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
I got the query to accept the parameter using the below M-code. I have two questions:
1. The parameters are showing up in my facts table and also two seperate queries (Start/End Dates). Does that sound correct?
2. I was expecting to see a see a start and end table in the model view, but it's not there. Why would the two parameters not show up as tables in model view?
Thanks.
let
// Define Start and End dates as Power Query parameters or fixed values
StartDate = Date.ToText(#date(2024, 8, 4), "MM/dd/yyyy"),
EndDate = Date.ToText(#date(2024, 8, 10), "MM/dd/yyyy"),
Source = Sql.Database("VLCCI-TMWSQL03", "TMWSuite_Transcorr", [Query="SELECT#(lf) ih.ord_hdrnumber as OrderNum#(lf), CAST(ih.ivh_deliverydate as Date) as Delivery#(lf), orig.cty_name as Origin#(lf), orig.cty_state as OSt#(lf), orig.cty_region2 as OReg#(lf), ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_name else legdest.cty_name end,'Temple') as Dest#(lf), ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_state else legdest.cty_state end, 'TX') as DSt#(lf), ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_region2 else legdest.cty_region2 end, 'TX-TEM') as DReg#(lf), l.lgh_split_flag as SplitFlag#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 200 then 200 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end as MTMilesOG#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end as MTMiles#(lf), (select top 1 ih2.ivh_rate from invoiceheader ih2 (nolock) where ih2.ivh_billto = 'STATEM' and ih2.ivh_deliverydate >= '" & StartDate & "' and ih2.ivh_deliverydate < '" & EndDate & "' and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5 order by CAST(ih2.ivh_deliverydate as Date) desc) as Rate#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end #(lf) *#(lf) (select top 1 ih2.ivh_rate from invoiceheader ih2 (nolock) where ih2.ivh_billto = 'STATEM' and ih2.ivh_deliverydate >= '" & StartDate & "' and ih2.ivh_deliverydate < '" & EndDate & "' and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5) as BillMTMi#(lf), (select avg(id.ivd_rate) from invoicedetail id (nolock) inner join invoiceheader ih3 (nolock) on ih3.ivh_hdrnumber = id.ivh_hdrnumber where id.cht_itemcode like 'FSCMI%' and ih3.ivh_billto = 'STATEM' and ih3.ivh_deliverydate >= '" & StartDate & "' and ih3.ivh_deliverydate < '" & EndDate & "') as FSCRate#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end#(lf) *#(lf) (select avg(id.ivd_rate) from invoicedetail id (nolock) inner join invoiceheader ih3 (nolock) on ih3.ivh_hdrnumber = id.ivh_hdrnumber where id.cht_itemcode like 'FSCMI%' and ih3.ivh_billto = 'STATEM' and ih3.ivh_deliverydate >= '" & StartDate & "' and ih3.ivh_deliverydate < '" & EndDate & "') as BillMTFSC#(lf), ih.ivh_totalcharge as BHGrossAmt#(lf), (select max(stp_mfh_sequence) from stops s (nolock) where s.mov_number = ih.mov_number) as LastStopID#(lf), isnull(s.stp_lgh_mileage,25) as LastStopMiles#(lf), .75 as RPM#(lf), .75 * s.stp_lgh_mileage as ReduceBHAmount#(lf), case when orig.cty_name = 'Brownwood' and dest.cty_region2 = 'TX-DAL' then ih.ivh_totalcharge else ih.ivh_totalcharge - (.75 * isnull(s.stp_lgh_mileage,25)) end as BHTotal#(lf), case when orig.cty_name = 'Brownwood' and dest.cty_region2 = 'TX-DAL' then ih.ivh_totalcharge*-1 else ((ih.ivh_totalcharge - (.75 * isnull(s.stp_lgh_mileage,25)))*.8)*-1 end as BHCredit#(lf), IH.IVH_BILLTO#(lf)FROM invoiceheader ih (NOLOCK)#(lf)INNER JOIN city orig (NOLOCK) ON orig.cty_code = ih.ivh_origincity#(lf)INNER JOIN city dest (NOLOCK) ON dest.cty_code = ih.ivh_destcity#(lf)LEFT JOIN legheader l (NOLOCK) ON l.ord_hdrnumber = ih.ord_hdrnumber#(lf)LEFT JOIN city legdest (NOLOCK) ON legdest.cty_code = l.lgh_endcity#(lf)LEFT JOIN stops s (NOLOCK) ON s.mov_number = ih.mov_number and s.stp_mfh_sequence = (select max(stp_mfh_sequence) from stops s (nolock) where s.mov_number = ih.mov_number) #(lf)WHERE ih.ivh_revtype3 = 'SC-BH'#(lf)AND ih.ivh_billto NOT LIKE 'STATEM%'#(lf)AND ih.ivh_deliverydate>= '" & StartDate & "' and ih.ivh_deliverydate< '" & EndDate & "' #(lf)AND (l.lgh_split_flag IN ('N','S') OR l.lgh_split_flag is null)#(lf)AND ih.ivh_invoicenumber = (SELECT max(ih2.ivh_invoicenumber) FROM invoiceheader ih2 (NOLOCK) WHERE ih2.ord_hdrnumber = ih.ord_hdrnumber and right(ivh_invoicenumber,1) <> 'B')#(lf)ORDER BY MTMilesOG desc"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Delivery", type date}})
in
#"Changed Type"
When you create parameters in Power BI using Power Query, they behave like mini queries — so yes, they’ll show up in the Queries pane and might sneak into your data model if you’ve accidentally left “Enable Load” turned on. If you see them in your facts table, it’s likely because they were loaded as queries. To fix that, just right-click on each parameter (like StartDate and EndDate) in Power Query and untick “Enable Load” — that’ll keep them out of your report visuals and model clutter.
Now, if you’re wondering why these parameters don’t show up in the model view as tables, it’s because they’re not tables at all — they’re just single values (a.k.a. scalars). Power BI’s model view only shows proper tables and relationships, not these behind-the-scenes helpers. Think of parameters like salt in a recipe: absolutely essential, but you don’t see it sitting on the plate.
Look that: Managing query refresh - Power BI | Microsoft Learn
I am getting closer to getting this to work. Now the problem I am having is that Bind to Parameter is not showing up after I follow all instructions in this link (https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerations-and-limitations). Below is the current M-code that includes the parameters I created. Am I doing something wrong?
let
StartDate = Date.ToText(#date(2024, 8, 4), "MM/dd/yyyy"),
EndDate = Date.ToText(#date(2024, 8, 10), "MM/dd/yyyy"),
Source =
Sql.Database("VLCCI-TMWSQL03", "TMWSuite_Transcorr",
[Query="SELECT#(lf) ih.ord_hdrnumber as OrderNum#(lf), CAST(ih.ivh_deliverydate as Date) as Delivery#(lf), orig.cty_name as Origin#(lf), orig.cty_state as OSt#(lf), orig.cty_region2 as OReg#(lf), ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_name else legdest.cty_name end,'Temple') as Dest#(lf), ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_state else legdest.cty_state end, 'TX') as DSt#(lf), ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_region2 else legdest.cty_region2 end, 'TX-TEM') as DReg#(lf), l.lgh_split_flag as SplitFlag#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 200 then 200 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end as MTMilesOG#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end as MTMiles#(lf), (select top 1 ih2.ivh_rate from invoiceheader ih2 (nolock) where ih2.ivh_billto = 'STATEM' and ih2.ivh_deliverydate >= '" & StartDate & "' and ih2.ivh_deliverydate < '" & EndDate & "' and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5 order by CAST(ih2.ivh_deliverydate as Date) desc) as Rate#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end #(lf) *#(lf) (select top 1 ih2.ivh_rate from invoiceheader ih2 (nolock) where ih2.ivh_billto = 'STATEM' and ih2.ivh_deliverydate >= '" & StartDate & "' and ih2.ivh_deliverydate < '" & EndDate & "' and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5) as BillMTMi#(lf), (select avg(id.ivd_rate) from invoicedetail id (nolock) inner join invoiceheader ih3 (nolock) on ih3.ivh_hdrnumber = id.ivh_hdrnumber where id.cht_itemcode like 'FSCMI%' and ih3.ivh_billto = 'STATEM' and ih3.ivh_deliverydate >= '" & StartDate & "' and ih3.ivh_deliverydate < '" & EndDate & "' ) as FSCRate#(lf), case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end#(lf) *#(lf) (select avg(id.ivd_rate) from invoicedetail id (nolock) inner join invoiceheader ih3 (nolock) on ih3.ivh_hdrnumber = id.ivh_hdrnumber where id.cht_itemcode like 'FSCMI%' and ih3.ivh_billto = 'STATEM' and ih3.ivh_deliverydate >= '" & StartDate & "' and ih3.ivh_deliverydate < '" & EndDate & "' ) as BillMTFSC#(lf), ih.ivh_totalcharge as BHGrossAmt#(lf), (select max(stp_mfh_sequence) from stops s (nolock) where s.mov_number = ih.mov_number) as LastStopID#(lf), isnull(s.stp_lgh_mileage,25) as LastStopMiles#(lf), .75 as RPM#(lf), .75 * s.stp_lgh_mileage as ReduceBHAmount#(lf), case when orig.cty_name = 'Brownwood' and dest.cty_region2 = 'TX-DAL' then ih.ivh_totalcharge else ih.ivh_totalcharge - (.75 * isnull(s.stp_lgh_mileage,25)) end as BHTotal#(lf), case when orig.cty_name = 'Brownwood' and dest.cty_region2 = 'TX-DAL' then ih.ivh_totalcharge*-1 else ((ih.ivh_totalcharge - (.75 * isnull(s.stp_lgh_mileage,25)))*.8)*-1 end as BHCredit#(lf), IH.IVH_BILLTO#(lf)FROM invoiceheader ih (NOLOCK)#(lf)
INNER JOIN city orig (NOLOCK) ON orig.cty_code = ih.ivh_origincity#(lf)
INNER JOIN city dest (NOLOCK) ON dest.cty_code = ih.ivh_destcity#(lf)
LEFT JOIN legheader l (NOLOCK) ON l.ord_hdrnumber = ih.ord_hdrnumber#(lf)
LEFT JOIN city legdest (NOLOCK) ON legdest.cty_code = l.lgh_endcity#(lf)
LEFT JOIN stops s (NOLOCK) ON s.mov_number = ih.mov_number and s.stp_mfh_sequence = (select max(stp_mfh_sequence) from stops s (nolock) where s.mov_number = ih.mov_number) #(lf)
WHERE ih.ivh_revtype3 = 'SC-BH'#(lf)AND ih.ivh_billto NOT LIKE 'STATEM%'#(lf)
AND ih.ivh_deliverydate>= '" & StartDate & "' and ih.ivh_deliverydate< '" & EndDate & "' #(lf)
AND (l.lgh_split_flag IN ('N','S') OR l.lgh_split_flag is null)#(lf)AND ih.ivh_invoicenumber = (SELECT max(ih2.ivh_invoicenumber) FROM invoiceheader ih2 (NOLOCK) WHERE ih2.ord_hdrnumber = ih.ord_hdrnumber and right(ivh_invoicenumber,1) <> 'B')#(lf)
ORDER BY MTMilesOG desc"])
in
Source
I think the reason for not seeing "Bind to Parameters" is because I am not using DirectQuery Mode. When I load the source SQL query in DirectQuery Mode I get a message that "This query contains transportations that can't be used for DirectQuery. I am not that familiar with DirectQuery. Everything I do is imported. What about this logic doesn't DirectQuery like? Is there something I should be looking for?
SELECT
ih.ord_hdrnumber as OrderNum
, ih.ivh_deliverydate as Delivery
, orig.cty_name as Origin
, orig.cty_state as OSt
, orig.cty_region2 as OReg
, ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_name else legdest.cty_name end,'Temple') as Dest
, ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_state else legdest.cty_state end, 'TX') as DSt
, ISNULL(case when l.lgh_split_flag = 'N' then dest.cty_region2 else legdest.cty_region2 end, 'TX-TEM') as DReg
, l.lgh_split_flag as SplitFlag
, case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 200 then 200 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end as MTMilesOG
, case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end as MTMiles
, (select top 1 ih2.ivh_rate from invoiceheader ih2 (nolock) where ih2.ivh_billto = 'STATEM' and ih2.ivh_deliverydate >= '08-01-2025' and ih2.ivh_deliverydate < '08-20-2025' and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5 order by CAST(ih2.ivh_deliverydate as Date) desc) as Rate
, case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end
*
(select top 1 ih2.ivh_rate from invoiceheader ih2 (nolock) where ih2.ivh_billto = 'STATEM' and ih2.ivh_deliverydate >= '08-01-2025' and ih2.ivh_deliverydate < '08-20-2025' and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5) as BillMTMi
, (select avg(id.ivd_rate) from invoicedetail id (nolock) inner join invoiceheader ih3 (nolock) on ih3.ivh_hdrnumber = id.ivh_hdrnumber where id.cht_itemcode like 'FSCMI%' and ih3.ivh_billto = 'STATEM' and ih3.ivh_deliverydate >= '08-01-2025' and ih3.ivh_deliverydate < '08-20-2025') as FSCRate
, case when ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) > 100 then 100 else ISNULL((SELECT sum( ISNULL( stp_lgh_mileage, 0 ) ) FROM stops s1 (NOLOCK) WHERE ih.ord_hdrnumber = s1.ord_hdrnumber AND s1.stp_loadstatus in ('MT','BT') ),0) end
*
(select avg(id.ivd_rate) from invoicedetail id (nolock) inner join invoiceheader ih3 (nolock) on ih3.ivh_hdrnumber = id.ivh_hdrnumber where id.cht_itemcode like 'FSCMI%' and ih3.ivh_billto = 'STATEM' and ih3.ivh_deliverydate >= '08-01-2025' and ih3.ivh_deliverydate < '08-20-2025') as BillMTFSC
, ih.ivh_totalcharge as BHGrossAmt
, (select max(stp_mfh_sequence) from stops s (nolock) where s.mov_number = ih.mov_number) as LastStopID
, isnull(s.stp_lgh_mileage,25) as LastStopMiles
, .75 as RPM
, .75 * s.stp_lgh_mileage as ReduceBHAmount
, case when orig.cty_name = 'Brownwood' and dest.cty_region2 = 'TX-DAL' then ih.ivh_totalcharge else ih.ivh_totalcharge - (.75 * isnull(s.stp_lgh_mileage,25)) end as BHTotal
, case when orig.cty_name = 'Brownwood' and dest.cty_region2 = 'TX-DAL' then ih.ivh_totalcharge*-1 else ((ih.ivh_totalcharge - (.75 * isnull(s.stp_lgh_mileage,25)))*.8)*-1 end as BHCredit
, IH.IVH_BILLTO
FROM invoiceheader ih (NOLOCK)
INNER JOIN city orig (NOLOCK) ON orig.cty_code = ih.ivh_origincity
INNER JOIN city dest (NOLOCK) ON dest.cty_code = ih.ivh_destcity
LEFT JOIN legheader l (NOLOCK) ON l.ord_hdrnumber = ih.ord_hdrnumber
LEFT JOIN city legdest (NOLOCK) ON legdest.cty_code = l.lgh_endcity
LEFT JOIN stops s (NOLOCK) ON s.mov_number = ih.mov_number and s.stp_mfh_sequence = (select max(stp_mfh_sequence) from stops s (nolock) where s.mov_number = ih.mov_number)
WHERE ih.ivh_revtype3 = 'SC-BH'
AND ih.ivh_billto NOT LIKE 'STATEM%'
AND ih.ivh_deliverydate>= '08-01-2025' and ih.ivh_deliverydate< '08-20-2025'
AND (l.lgh_split_flag IN ('N','S') OR l.lgh_split_flag is null)
AND ih.ivh_invoicenumber = (SELECT max(ih2.ivh_invoicenumber) FROM invoiceheader ih2 (NOLOCK) WHERE ih2.ord_hdrnumber = ih.ord_hdrnumber and right(ivh_invoicenumber,1) <> 'B')
ORDER BY MTMilesOG desc