cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
koushikmsbi Frequent Visitor
Frequent Visitor

Generate a data source from a SQL Stored Procedure with one parameter coming from drillthrough field

I have a Power BI report in DirectQuery Mode. In page 1, the user selects a field named PartNumberKey (which is an integer) and on drill-through, the report navigates to the next page which displays a table showing sales details for the selected PartNumberKey. Now there are around 8000+ PartNumberKeys and for each PartnumberKey, the table returns 4000-5000 rows. So populating the entire table is taking 2-3 hours. So I created a SQL SP with PartNumberKey as parameter and when I run it in SSMS it returns sales details in 1-2 seconds. 

 

The SP is like : EXEC Fact.usp_Average_Monthly_Demand 157994

 

Is there anyhow I can execute this data source dynamically and populate the table automatically as per the selected PartNumberKey in the first page? 

 

PS: If required, I can use this query in Import Mode as Composite Models is available as a preview feature.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Generate a data source from a SQL Stored Procedure with one parameter coming from drillthrough f

Hi @koushikmsbi,

 

#1, current power bi not support to invoke 'Stored procedure' in 'direct query' mode, please use import mode to instead.

 

#2, You can't extract value from data view level and use it as query parameter to generate new data model tables.

 

Data level: Left side can effect right side, right side can't effect left side.

Database -> query table(power query, query parameter, custom functions) -> data model(calculate table/column, RLS) -> data view(visual data, different level filter, slicers, measure).

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
hunter Frequent Visitor
Frequent Visitor

Re: Generate a data source from a SQL Stored Procedure with one parameter coming from drillthrough f

Hi @v-shex-msft,

 

Is there any support or movement on PowerBI's side to implement the ability to invoke a "Stored Procedure" in "Direct Query" mode in the near-future? This functionality would be well received by all if it could be done.