Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I able to exec the SP with pramaters and created the invoked function . But i have question , How to pass the parameters as like a slicer,textbox and button from UI to call SP and display the data. I unable to see any guide on this.
I able to execute the invoked function and get the data but i need pass data from UI.
please guide.
Hi @bhaskar812 ,
What you are looking for is called Dynamic M Parameters for SQL Server https://powerbi.microsoft.com/en-us/blog/power-bi-february-2022-feature-summary/#post-18588-_Toc9541...
@DebbieE has written a blog post about how to implement it with SQL Server and a predefined list to select the parameter values from: https://debbiesmspowerbiazureblog.home.blog/2022/03/17/power-bi-february-2022-updates-dynamic-m-quer...
@cwebb has written a blog post about how to implement it with SQL Server and no predefined parameter values, i.e. the user enters any parameter value in the filter or slicer: https://blog.crossjoin.co.uk/category/dynamic-m-parameters/
Be aware that these two examples use normal SELECT queries, not stored procedures. It works with stored procedures as well, but this has a few more challenges:
SELECT *
FROM OPENQUERY(
[MyServer],
'SET FMTONLY OFF
EXEC [MyDatabase].[MySchema].[MyStoredProc]
@Parameter = ''Value'''
)
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_serveroption 'MyServer', 'DATA ACCESS', TRUE
GO
Don't give up if it gets challenging! I've tried in Power BI Desktop and published to Power BI service and it works.
BR
Martin
User | Count |
---|---|
5 | |
4 | |
3 | |
2 | |
2 |
User | Count |
---|---|
8 | |
6 | |
4 | |
4 | |
4 |