Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 @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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |