topic Using SQL declared variables into Power BI in Desktop

Using SQL declared variables into Power BI

cheid_4838 — Wed, 20 Aug 2025 13:28:05 GMT

I have multiple SQL reports that declare more than one variable. The SQL query below has only one variable utilizing dates, but most have multiple variables being declared I would like to integrate these queries into Power BI so that the slicer whether it be date or something else show results that meet the criteria shown with the declared variable within the query. I have reviewed multiple websites and watched many videos and for some reason can't get this to work utilizing managed parameters. This is one of the few concepts of power bi that have yet to figure out. More than likely I am making this more difficult than it needs to be. Any thoughts on how I can get this to work in POwer BI? Thanks in advance for your help.

--DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = '8/4/2024' SET @EndDate = '8/10/2024'

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 >= @StartDate and ih2.ivh_deliverydate < @EndDate+1 and ih2.ivh_rate > 1.2 and ih2.ivh_rate < 2.5 order by ih2.ivh_deliverydate 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 >= @StartDate and ih2.ivh_deliverydate < @EndDate+1 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 >= @StartDate+3 and ih3.ivh_deliverydate < @EndDate+1) 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 >= @StartDate+3 and ih3.ivh_deliverydate < @EndDate+1) 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 >= @StartDate and ih.ivh_deliverydate < @EndDate+1
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

When I tried to change the M-code to reflect the date parameter I get the below message:

Expression.Error: We cannot apply operator & to types Text and Date.
Details:
Operator=&
Left=SELECT
ih.ord_hdrnumber as OrderNum
, CAST(ih.ivh_deliverydate as Date) 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_lo...
Right=8/19/2025

Re: Using SQL declared variables into Power BI

MohamedFowzan1 — Wed, 20 Aug 2025 15:25:09 GMT

Hi cheid_4838

Looks like the error is due to trying concate with a text and date.

Try this:

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"),

// Construct the full SQL query string with declared variables
SqlQuery =
"DECLARE @StartDate AS DATETIME; " &
"DECLARE @EndDate AS DATETIME; " &
"SET @StartDate = '" & StartDate & "'; " &
"SET @EndDate = '" & EndDate & "'; " &

"SELECT " &
"ih.ord_hdrnumber as OrderNum, " &
"CAST(ih.ivh_deliverydate as Date) 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, " &
"/* Add remaining columns here as in your full query, properly concatenated with & and enclosed in quotes */ " &
"ih.ivh_totalcharge as BHGrossAmt, " &
"(SELECT MAX(stp_mfh_sequence) FROM stops s (NOLOCK) WHERE s.mov_number = ih.mov_number) as LastStopID " &
"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 >= @StartDate " &
"AND ih.ivh_deliverydate < DATEADD(day, 1, @EndDate) " &
"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"
,

// Connect to SQL Server Database (change Server and Database names)
Source = Sql.Database("your_server_name", "your_database_name", [Query=SqlQuery])
in
Source

Re: Using SQL declared variables into Power BI

CPCARDOSO — Wed, 20 Aug 2025 15:30:33 GMT

How to Use Multiple SQL Variables with Power BI Filters (Without Losing Your Mind)

1. ❌ Stop Using DECLARE and SET Like It’s 1999

Look, Power BI isn’t your SQL Server mate who tolerates your old-school habits. If you try to slap in a DECLARE @StartDate or SET @EndDate = '2024-08-10', Power BI will throw a tantrum and spit out errors like a toddler denied sweets.

Instead, use Power BI’s own parameters — they’re like polite little boxes that hold your values and don’t complain.

2. 🧱 Build Your Parameters Like a Pro

In Power BI Desktop:

Go to Transform Data → Manage Parameters.
Create parameters like:
- StartDate (type: Date)
- EndDate (type: Date)
- Anything else you fancy — Customer, Region, Favourite Biscuit, whatever.

These are your new best friends. Treat them well.

3. 🧙‍♂️ Use the Parameters in Power Query (a.k.a. the M Code Dungeon)

Now, here’s where the magic happens. You need to build your SQL query dynamically using M code (look down).Think of it like crafting a spell — but instead of summoning dragons, you’re summoning data.

let
StartDate = Date.ToText(Parametros[StartDate], "yyyy-MM-dd"),
EndDate = Date.ToText(Parametros[EndDate], "yyyy-MM-dd"),
ConsultaSQL = "
SELECT * FROM YourTable
WHERE DeliveryDate >= '" & StartDate & "'
AND DeliveryDate < DATEADD(day, 1, '" & EndDate & "')"
in
Sql.Database("YourServer", "YourDatabase", [Query=ConsultaSQL])

4. Avoid NOLOCK and Subquery Chaos

Yes, I know — NOLOCK feels like a shortcut. But in Power BI, it’s more like trying to sneak into a nightclub with a fake ID. It might work, but it’s dodgy and could get you kicked out.

If your query looks like spaghetti code, consider moving the heavy lifting to a SQL view and let Power BI just do the filtering. Keep it lean, keep it clean.

5. 🧠 Advanced Trick: Custom Functions

Feeling brave? You can create a Power Query function that takes in parameters and builds your SQL on the fly. It’s like building your own robot butler — posh, powerful, and slightly over-engineered.

Perfect if you’ve got loads of variables and want to keep things tidy.

🧠 Final Thought

You’re not alone — loads of budding analysts overcomplicate this stuff. The trick is to ditch the old SQL habits, embrace Power BI’s way of doing things, and keep your queries clean like your nan’s kitchen.

🙌 Fancy Giving Me a Kudos?

If this guide helped you stop yelling at Power BI and start making it behave, I’d be well chuffed if you gave it a Kudos on the Microsoft forums. It helps others find the fix and makes me look clever. Win-win, innit? Cheers, legend! 🍻

Re: Using SQL declared variables into Power BI

cheid_4838 — Wed, 20 Aug 2025 15:58:15 GMT

Thanks for the response. The delcare statements were not meant for importing into power bi. They came from an SSRS report that I am working on converting to Power BI. I will let you know if I get it to work.

Re: Using SQL declared variables into Power BI

cheid_4838 — Wed, 20 Aug 2025 20:23:43 GMT

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"

Re: Using SQL declared variables into Power BI

CPCARDOSO — Thu, 21 Aug 2025 10:59:43 GMT

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

Re: Using SQL declared variables into Power BI

cheid_4838 — Thu, 21 Aug 2025 15:05:23 GMT

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"])

Source

Re: Using SQL declared variables into Power BI

cheid_4838 — Thu, 21 Aug 2025 15:23:29 GMT

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

Re: Using SQL declared variables into Power BI

kushanNa — Mon, 25 Aug 2025 03:29:05 GMT

Hi cheid_4838

I have tried to reproduce your scenarios on my local machine and got an error related to an ORDER BY clause. So, I asked some AI tools to rewrite the query suitable for Power BI, and they gave me this query. It looks like they just removed the ORDER BY clause, but anyway, I have copied the full code here.

SELECT -- Order Information ih.ord_hdrnumber AS OrderNum, ih.ivh_deliverydate AS Delivery, -- Origin Information orig.cty_name AS Origin, orig.cty_state AS OSt, orig.cty_region2 AS OReg, -- Destination Information (with split flag logic) 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, -- Split Flag l.lgh_split_flag AS SplitFlag, -- MT Miles Original (capped at 200) 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, -- MT Miles (capped at 100) 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, -- Rate from STATEM invoices (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, -- Billing MT Miles calculation 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, -- FSC Rate (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, -- Billing MT FSC calculation 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, -- Billing Information ih.ivh_totalcharge AS BHGrossAmt, -- Stop Information (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, -- Revenue Calculations 0.75 AS RPM, 0.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 - (0.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 - (0.75 * ISNULL(s.stp_lgh_mileage, 25))) * 0.8) * -1 END AS BHCredit, ih.ivh_billto FROM invoiceheader ih (NOLOCK) -- Join with origin city INNER JOIN city orig (NOLOCK) ON orig.cty_code = ih.ivh_origincity -- Join with destination city INNER JOIN city dest (NOLOCK) ON dest.cty_code = ih.ivh_destcity -- Optional join with leg header LEFT JOIN legheader l (NOLOCK) ON l.ord_hdrnumber = ih.ord_hdrnumber -- Optional join with leg destination LEFT JOIN city legdest (NOLOCK) ON legdest.cty_code = l.lgh_endcity -- Optional join with last stop 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 -- Filter conditions 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 -- Removed for Power BI compatibility

It looks like just removing the ORDER BY clause makes DirectQuery mode work.

Re: Using SQL declared variables into Power BI

v-pnaroju-msft — Mon, 25 Aug 2025 04:26:33 GMT

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 Learn

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