Dynamic Stored Procedure Call based on user input parameters

[DeepaS]

I want to be able to execute a stored procedure based on the input parameters selected by end user. 

1.  DirectQuery Mode : I'm able to use dynamic M query parameters for SQL queries using   -  https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
2.  Import Mode : Also, I'm able to execute a stored Procedure  but cannot pass end user input parameters here

If we have to create a table , it is going to generate several million rows. Hence, we want to dynamically get user input and fetch only a subset of this. Please suggest.

Thanks for your help in advance! 

[ChadS]

Is this solution still viable?  I've followed the steps, and I can get a DirectQuery to a stored procedure that takes no parameters.  As soon as I switch to a parameterized sproc (same functionality, just includes the param in the select along with the rest of the data), the preview shows appropriate data, but a warning indicates I must change all my sources to Import mode.

[DeepaS]

@Anonymous 

We are having function1 to feed data for Page1 and function2 to feed data for Page2. When we drill through from Page1 to Page2, the transient filters are displaying correct values but the data is not filtered in Page2 based on Page1 filters ( transient filters). Please suggest.

Thanks,
Deepa

[DeepaS]

Hello Daryl,

Thank you very much for your response. The Function worked for us. Appreciate your time 🙂

We used this site as suggested by you.

Chris Webb's BI Blog: TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery C...

[Anonymous · Accepted solution]

Hi @DeepaS  - the first option is possible using the correct syntax.  Here are couple of blogs to consider.

Power BI Dynamic M Query Parameters Reloaded – Prologika

Chris Webb's BI Blog: TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery C...