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

[Anonymous]

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.

[v-shex-msft]

Hi @Anonymous,

#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

[hunter]

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.