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.
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! 🍻
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.