Using parameters from SQL Table

[JohnLow]

Hi,

I have a direct connection to an SQL Server via my powerbi report. In advanced editor I have the following: 

let
Source = Sql.Database("Server\Database", "Database", [Query="Select * from queryinsql(" & Number.ToText(PeriodParameter) & "," & Number.ToText(YearParameter) & ")"])
in
Source

I've 2 parameters I'm passing to the query, period and year. I don't want the users to enter these parameters themselves in the query editor and would like if these parameters were available on the report level.

I followed this link, creating a table for period and another one for year but it seems it doesn't support SQL queries. https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

Has anyone any advice please?

Thanks. 

[v-yingjl · Accepted solution]

Hi @JohnLow ,

If you have a sql connection using direct query mode, as the document mentioned the dynamic M query could not suppport in this comection.

The feature is only supported for M based data sources. The following DirectQuery sources are not supported:

• T-SQL based data sources: SQL Server, Azure SQL Database, Synapse SQL pools (aka Azure SQL Data Warehouse), and Synapse SQL OnDemand pools.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@JohnLow , There was feature in OCT 2020- https://powerbi.microsoft.com/en-us/blog/power-bi-october-2020-feature-summary/#_Toc51665421

https://blog.crossjoin.co.uk/2020/12/06/using-power-bi-dynamic-m-parameters-in-dax-queries/

See if that can help

[JohnLow]

@amitchandak, This all refers to dynamic m parameters though, I have an SQL query?