Need assistance to pull data dynamically using Emp Id in SQL query where clause to Power BI.

[Ubedulla]

Hi,

I have a situation where I'm using a recursive SQL query to get the direct and indirect reportees of an employee based on their Employee ID. This works when we use a fixed (static) Employee ID in the query's WHERE clause.

Now, we want to use this same query in Power BI. The requirement is that the end user should be able to choose an Employee ID from a slicer, and based on that selection, Power BI should show all direct and indirect reportees for that employee.

We tried loading all the data into Power BI, but since the query is recursive and goes up to 7 levels deep, it's too heavy. Even PostgreSQL can't run the query unless we provide a specific Employee ID.

Please suggest any workaround to complete this requirement.

Thank you..

[v-kpoloju-msft]

Hi @Ubedulla,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @Jai-Rathinavel, @lbendlin,, for his inputs on this thread. Here I provided workarounds below that might be helpful to you to resolve the issue. Please go through to them.

You are right. Power BI does not support passing slicer values directly into SQL queries at runtime in Import mode. Since your recursive SQL logic depends on a specific Employee ID and is resource-intensive, importing the full hierarchy isn't feasible.

To meet your requirement (filtering by selected Employee ID and fetching direct/indirect report), I suggest the following approach using Direct Query with Dynamic M Parameters. Use Direct Query mode to connect to your database. This allows Power BI to push slicer values directly into the SQL query at runtime.

Create a parameter in Power Query (e.g., EmployeeIDParam), and integrate it into your recursive SQL CTE like:

WHERE EmpID = '" & Text.From(EmployeeIDParam) & "'

Create a slicer table in Power BI based on distinct Employee IDs and bind it to your parameter using Dynamic M Query Parameters. When the user selects an Employee ID in the slicer, Power BI re-executes the SQL query with that value, and fetches the correct report hierarchy efficiently.

Also, please refer to the below mentioned documentation links for better understanding:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Direct Query in Power BI - Power BI | Microsoft Learn

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[Jai-Rathinavel]

Hi @Ubedulla ,

Instead of using Import mode, have you tried DirectQuery storage mode? In DirectQuery, slicer selections are translated into SQL  where clauses and sent directly to the database to retrieve the relevant information.

Thanks,

Jai

[Ubedulla]

Hi Rathinavel thanks for responding on issue.
I have tried with DIrectQuery as well. I created a parameter with distinct emp ID (Query Parameter) and The Sql query that retrieves data connected to Directquery mode and tried to pass the paramenter in Bind Query parameter. I could not see that Bind Query Option in modeling screen.

https://www.google.com/search?q=pass+a+slicer+filter+value+as+the+input+filter+value+into+query+powe...

[lbendlin]

Ask your IT department to give you access to their Enterprise Directory extract.  That has the necessary information so you can use the PATH functions.

Trying to recompute this data dynamically is not optimal, as the data is generally immutable (or at least changing very slowly).