Power BI : execute stored procedure chosen parameter by user via web portal

[rkapukaya]

I want to execute a SQL Server stored procedure which takes 9 parameters and show results to user as a table view format.

My aim is user can select values of 9 parameters from Power BI web or can input values to boxes for 9 parameters, and when user hits apply button, the stored procedure is run and displays data as result.

The stored procedure calculates several columns values according to values of 9 parameters so report should be in direct query mode.

Is this possible? I've look around and there are several solutions but not what I want.

[parry2k]

@rkapukaya you need to use dynamic parameters and here are the details Dynamic M query parameters in Power BI Desktop (preview) - Power BI | Microsoft Docs

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[rkapukaya]

@parry2k , 

I've followed steps in url as mentioned. In my case, my stored procedure takes 2 text inputs.

I take error;

"Query (2, 14) The M parameter 'Par1' not defined in model or M type is not valid"


my steps;
* Created two text parameters (Par1, Par2)
* Added 2 queries from SQL Server tables to bind parameters. (each query has one column and in text format)
* Parameters are binded. (Query1 column to Par1, Query2 column to Par2)
* Added new query (Query3) which includes my stored procedure;

let

Source = Sql.Database("localhost", "MyDb", [Query="SELECT * FROM OPENROWSET('SQLNCLI','Server= localhost;Trusted_Connection=yes','EXECUTE MyDb.dbo.Sp_Calculete "&Par1&","&Par2&"')"])

in
Source

* I added columns of Query1 and Query2 columns into the slicers (drop down type) & add Query3 column to table view.

* When i choose a value from one of slicers, errors come up for other slicer and table view as i mentioned.
"Query (2, 14) The M parameter 'Par1' not defined in model or M type is not valid"

what am i missing?