Calling TVF with parameter from slicer

[sriramanat]

I am creating a Power BI desktop report. I am having a dropdown to display Employee details, displaying employee name (Got employee dataset with a data source).
I taken another data source in the same report, calling a TVF with EmployeeId (number) as parameter. I want to pass EmployeeId from dropdown to the TVF and get the results to display in a table visual.

There is no direct relation between the slicer fields to the result.

Can someone help me. 

[V-yubandi-msft]

Hi @sriramanat ,
Please let me know if your issue is resolved or if you need any further assistance.
Happy to provide any additional details if needed.

[V-yubandi-msft]

Hi @sriramanat ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

[wardy912]

Hi @sriramanat 

In Power BI Desktop, go to Manage Parameters, New Parameter.

Name it something like EmployeeIdParam.

Set its type to Number.

Leave the default value blank or set a placeholder.

In Power Query, edit the query that calls the TVF:

SQLSELECT * FROM dbo.MyTVF(@EmployeeIdParam)

Replace @EmployeeIdParam with the parameter you created.

Go to Modeling, New Parameter, Fields.

Choose your Employee table and select the EmployeeId column.

Bind this field parameter to your slicer.

Add a slicer visual for Employee Name.

Configure the slicer to update the parameter (via Dynamic M Query Parameters).

When the user selects an employee, Power BI will pass the EmployeeId to the TVF query and refresh the data.

This requires DirectQuery mode for the TVF source.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[sriramanat]

@wardy912 Thank you for your reply.
I tried the steps. I am not able to replicate the exact steps. Is there anyway you can keep screenshots for all the steps!
Or, any doanloadable pbix file along with data models?
Or, is there anyway we can connect on Teams 🤔

[V-yubandi-msft · Accepted solution]

Hi @sriramanat ,

I know this feature can be a bit confusing at first. I can’t do a Teams call, but I’m happy to guide you through the steps as clearly as possible.

This is the official Microsoft article that explains exactly how to pass a slicer value into a SQL query or TVF using Dynamic M Query Parameters: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn.

If you’re still having trouble after reviewing it, please share more details or a screenshot of your setup. That will help in resolving the issue.

Regards,
Yugandhar.

[MasonMA]

Hi

You can try first creating an M parameter in Power Query like 

EmployeeId = 1

and use that M parameter inside a query that calls your table valued function. 

let
Source = Sql.Database("ServerName", "DBName",
[Query = "SELECT * FROM dbo.GetDirectReports(" & Text.From(EmployeeId) & ")"])
in
Source

then use EmployeeID in the slicer field, this way when select an employee in the dropdown, Power BI would requery the TVF, passing that EmployeeID to the SQL function (only under DirectQuery, for Import you'd need to refresh).

[parry2k]

@sriramanat Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490