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.

[Ubedulla]

Hi ,
Thank you! I was able to follow your suggestion, and I’m happy to see some progress. However, I’m facing another issue now. I’m using AdventureWorks data for testing. I created a parameter called Customer_Key_Parameter using the CustomerKey column from the Customer table and added it to a slicer. But when I change the value in the slicer, the table doesn’t update accordingly.
Please check the screenshots for your reference.

[v-kpoloju-msft · Accepted solution]

Hi @Ubedulla,

Great to hear you are making progress thanks for sharing the screenshots, they really help clarify the setup.

From what you have described, it looks like the parameter and slicer are set up correctly, and you are already using Direct Query mode, which is perfect. However, if changing the slicer value isn’t updating the visual, it’s likely due to one key piece missing.

Binding the field to a parameter in the model is just the first step the real magic happens when you tell Power BI to filter the data using that parameter inside the query.

Here is how you can do that:

1. Go to Power Query Editor
2. Locate your Fact_Internet_Sales table query
3. Update the query so that it filters using the parameter, like this:

If you are using a custom SQL statement:

let
    CustomerKeyParam = Customer_Key_Parameter,
    Source = Sql.Database("YourServer", "AdventureWorksDW2019", [
        Query = "SELECT * FROM FactInternetSales WHERE CustomerKey = " & Number.ToText(CustomerKeyParam)
    ])
in
    Source

Or, if you're using the default table navigation:

let
    Source = Sql.Database("YourServer", "AdventureWorksDW2019"),
    SalesTable = Source{[Schema="dbo", Item="FactInternetSales"]}[Data],
    FilteredRows = Table.SelectRows(SalesTable, each [CustomerKey] = Customer_Key_Parameter)
in
    FilteredRows

Make sure the slicer is set to Single Select = ON. This is a known limitation: Multi-select is not supported when using slicers bound to parameters.

Once the query is correctly using the parameter in Power Query, you should see that changing the slicer value triggers a new SQL query and updates the table visual. Dynamic M query parameters in Power BI Desktop - 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.

[Ubedulla]

Thank you soo much it is working...

[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).