Forum Discussion
Slicer-Bound Parameter DirectQuery
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
retProblem:
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:
Is it even possible to use a parameter like this inside a Value.NativeQuery block in DirectQuery mode?
What is the correct syntax for binding a Power BI parameter to a SQL Server scalar variable in a native SQL query?
Are there limitations or known issues with Power BI parameters and DirectQuery on SQL Server?
Any help is appreciated.
Thanks in advance.
let
source = Sql.Database("foo.bar.com", "wh",[Query="SELECT * FROM [tfm].[so.input.budget/forecast] WHERE pk = " & Text.From(#"snapshot_pk")])
in
source
7 Replies
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.
ShortAnswer: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.
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
