Slicer-Bound Parameter DirectQuery

[nmay]

Hi all,

I'm trying to use a DirectQuery in Power BI (Desktop, latest version) to filter a SQL Server 2019 table based on a parameter value selected via slicer.

Scenario:

• I am using a SQL Server 2019 database.

• I have a table or query called inputs, which is used in a slicer. It uses import mode. Each row in inputs has a distinct snapshot.pk value.

• I created a Power BI parameter named snapshot_pk and bound it to the slicer via the snapshot.pk field from the inputs table.

• I’m attempting to use that parameter in a DirectQuery query called forecast, where I want to dynamically filter the table [wh].[tfm].[so.input.budget/forecast] f by f.pk = snapshot_pk.

  Current Attempt:

  Here's what the Power Query M code currently looks like:

  let
    var_snapshot_pk = #"snapshot_pk",
    Source = Sql.Database( "foo.bar.com\baz_bap", "greatestdb" ),
    ret = Value.NativeQuery (
      Source
      , "DECLARE @snapshot_pk as INT SET @snapshot_pk = '" & var_snapshot_pk & "'

      SELECT * FROM [wh].[tfm].[so.input.budget/forecast] WHERE pk = @snaphot_pk"
      , [snapshot_pk = var_snapshot_pk]
  )
  in
  ret

  Problem:

  No matter what syntax I try (dynamic SQL, embedded values, or parameterized queries), I have gotten various errors. As I have been trying to figure out the correct syntax, currently with the query listed above:
  
  

    DataSource.Error: Microsoft SQL: The variable name '@snapshot_pk' has already been          declared. Variable names must be unique within a query batch or stored procedure.
    Must declare the scalar variable "@snaphot_pk".

  
  I've also tried simpler variants without the DECLARE, or injecting the value inline, e.g., WHERE pk = " & snapshot_pk & "—still no success.

  ---

  Key Questions:

  1. Is it even possible to use a parameter like this inside a Value.NativeQuery block in DirectQuery mode?

  2. What is the correct syntax for binding a Power BI parameter to a SQL Server scalar variable in a native SQL query?

  3. Are there limitations or known issues with Power BI parameters and DirectQuery on SQL Server?

     ---

     Any help is appreciated.

     Thanks in advance.

[Amar_Kumar]

Short Answer:

1, YES, you can use Power BI parameters with Value.NativeQuery in DirectQuery mode.

2, But NO, you can't declare SQL variables (DECLARE @...) inside Value.NativeQuery. It's already parameterized outside.

3, And YES, parameter must be passed via the 3rd argument of Value.NativeQuery, not embedded manually.

Correct Syntax to Use Power BI Parameter in DirectQuery:

You should not declare variables inside the query when using the parameter binding of Value.NativeQuery. Here’s how you can rewrite your code:

let

    var_snapshot_pk = snapshot_pk, // This uses your Power BI parameter

    Source = Sql.Database("foo.bar.com\baz_bap", "greatestdb"),

    ret = Value.NativeQuery(

        Source,

        "SELECT * FROM [wh].[tfm].[so.input.budget/forecast] WHERE pk = @snapshot_pk",

        [snapshot_pk = var_snapshot_pk]

    )

in

    ret

Key Rules You Must Follow:

Do NOT declare @snapshot_pk inside the SQL string.

Power BI handles the declaration and binding for you.

Use the @snapshot_pk directly in the SQL WHERE clause.

Bind the value in the third parameter of Value.NativeQuery() as [snapshot_pk = var_snapshot_pk].

Ensure the Power BI parameter is a scalar value (not a list).

The Power BI parameter must be compatible in type (e.g., INT) with the column you're filtering.

[nmay]

This is pretty close to what I started with, I might be glossing over something important

I changed the query to
let
  var_snapshot_pk = #"snapshot_pk",
  source = Sql.Database("foo.bar.com", "wh"),
  ret = Value.NativeQuery(
    source
    , "SELECT * FROM [wh].[tfm].[so.input.budget/forecast] WHERE pk = @snapshot_pk"

    ,  [snapshot_pk=var_snapshot_pk]
)
in
ret

[nmay]

Here is what my parameter looks like, maybe something is wrong here:

[lbendlin · Accepted solution]

let
  source = Sql.Database("foo.bar.com", "wh",[Query="SELECT * FROM [tfm].[so.input.budget/forecast] WHERE pk = " & Text.From(#"snapshot_pk")])
in
 source

[nmay]

That is pulling the values I expect! Thank you!

I will see a performace improvement by using value.nativequery though, for folding. Is that right?

[lbendlin]

no. It's already a "native" query when you put it there.

[lbendlin]

Try a less fancy SQL.  Anything that goes beyond a simple SELECT statement is prone to give issues. There is no need to use DECLARE, really. Put the query directly into the Source step.

Power Query (M) – Passing Parameters dynamically to a SQL Server Query - FourMoo | Microsoft Fabric ...