Forum Discussion
Resolver Ihow to pass parameter to database
I have a situation where a table or a query result set needs to be generated at user-interaction time in a "normal" Power BI dataset (created in Power BI Desktop). For example, a user enters a value in some visual in a Power BI report, and that value executes a stored procedure, which returns a result set to some other visual in the report. In a paginated report (Power BI Report Builder), this would be fairly simply -- create a parameterized stored procedure that returns a result set and call that stored procedure in the report. However, I don't have Power BI Report Builder (paginated reports) available to me in my environment. And, I can't find a way to achieve this in Power BI Desktop, even in DirectQuery mode. To be sure, I can't "pre-calculate" a table in the database that contains all possible rows and have the user simply drive the filtering via a slicer, as the pre-calculated table will be gigantic. So, is this possible to do in Power BI Desktop?
3 Replies
- amitchandak
Super User
mr_wizard , Explore Dynamic M parameters in Direct Query
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/
mr_wizardamitchandak Thank you for your response. Unfortunately, I don't think dynamic M query parameters meets my requirement. To be sure, because of data volume constraints, the data that report users need to query can't be "pre-calculated" at the database level. That means that it must be generated on-the-fly by the report user via a stored procedure and that the Power BI report must run in DirectQuery mode, not Import mode. However, the following MS documentation states that DirectQuery does not support stored procedures:
DirectQuery in Power BI - Power BI | Microsoft LearnWhen I attempt to execute the stored procedure in DirectQuery mode, I get the same error as discussed on this post:
Solved: Microsoft SQL: Incorrect syntax near the keyword '... - Microsoft Fabric CommunitySo, can I actually achieve this requirement in Power BI Desktop?
- mr_wizard
Both testing and the MS documentation confirm that DirectQuery in Power BI Desktop does not support stored procedures. Since DirectQuery seems to be the only mode that can meet my requirements, then it seems that this use case (a report viewer executes stored procedures) is not possible in Power BI Desktop.
