Forum Discussion
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
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
Hi Anonymous,
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.
