Create dynamic parameter(s) with values stored in sql server (stored proc)

[tahmid-bari]

Hi PBI Team,

My dataset is linked using 'import' and a stored procedure in SQL server. I have created dynamic parameters (start date, end date, number) where I can enter values that change the result data in 'edit parameter'. However, I am having trouble (cannot find 'Advanced' option) binding those dynamic parameters to the newly created tables.

Furthermore, can I include another stored procedure in the dynamic parameter - number so that the end user can select from a drop-down list? In fact, because the (Dynamic M query parameters) feature does not support RLS, I am attempting to implement row-level security by creating a table in SQL Server and using DAX on the table (dynamic parameter - number).

I also understand that creating a dynamic user filter using a security field in the data can be done in Tableau, however, we would prefer to find a solution in the Power BI app. Thank you.

[tahmid-bari]

Hi Alexis,

Thank you so much for your advise. I apologise for the delay in responding. When I tried to use DirectQuery (exec sp proc), I received the following error message: Incorrect syntax near the keyword 'exec' in Microsoft SQL. Syntax error near ')'. Then I took the following reference from the forum: Thanks.
https://community.powerbi.com/t5/Power-Query/Microsoft-SQL-Incorrect-syntax-near-the-keyword-exec-In...

[AlexisOlson · Accepted solution]

Dynamic parameters only work with DirectQuery, that is, not in Import mode.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters