Forum Discussion
Slicer-Bound Parameter DirectQuery
let
source = Sql.Database("foo.bar.com", "wh",[Query="SELECT * FROM [tfm].[so.input.budget/forecast] WHERE pk = " & Text.From(#"snapshot_pk")])
in
source
Super UserShort 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.
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
Here is what my parameter looks like, maybe something is wrong here:
- lbendlin
let
source = Sql.Database("foo.bar.com", "wh",[Query="SELECT * FROM [tfm].[so.input.budget/forecast] WHERE pk = " & Text.From(#"snapshot_pk")])
in
sourceThat is pulling the values I expect! Thank you!
I will see a performace improvement by using value.nativequery though, for folding. Is that right?