Direct Query & Dynamic Parameters

[Jeedddy88993]

Hi All,

After spending around 2 days looking for a solution, I'm left no option but to ask the help of the PBI Gurus.

Background : I'm trying to use a Stored Procedure in PBI which needs 2 Parameters (Date/Time & Number) to run successfully. Ideally, I would like to have 2 slicers on the report, so the user can select their criteria and it query the DB then return the results. 

I've now managed to get this to work within the Power Query editor however now I run into the issue. I then realised that Dynamic Parameters is not compatible with Import mode when I couldnt locate the 'Bind to parameter' option in the modelling window. I've since recreated the workbook ensuring that all queries are set as 'Direct Query'. 

Issue 1 :  Despite (all) data sources being set up as 'Direct Query', I'm unable to get the dataset to successfully load. I'm presented with the below error

I can see this was a bug a few years back but I can't see that it was ever confirmed that it was resolved. See below

https://community.fabric.microsoft.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/m-p/40983#U4098...

https://community.fabric.microsoft.com/t5/Desktop/Incorrect-syntax-in-Power-BI-but-query-works-fine-...  

P.S. Please dont suggest using the '() =>' method as this with change the query back to Import. 

Issue 2 : I ran a seperate query (set to Direct Query) which loads perfectly fine, however I still don't get the prompt to 'Bind to Paramter' option, making me think that there could be a wider issue at play. 

I'd greatly appriciate any assitance/guidance you can give. If more detail is required, please let me know.

[Jeedddy88993]

@Greg_Deckler Are you able to help with this?

[Greg_Deckler]

@Jeedddy88993 First question is whether you are following this article: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

[Jeedddy88993]

@Greg_Deckler @  I am following that guide(and others to fill in the gaps). All is fine until i want to bind the paremte