Forum Discussion
Need assistance to pull data dynamically using Emp Id in SQL query where clause to Power BI.
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:
- Go to Power Query Editor
- Locate your Fact_Internet_Sales table query
- 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 SourceOr, 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.
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.
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.
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:
- Go to Power Query Editor
- Locate your Fact_Internet_Sales table query
- 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 SourceOr, 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.Thank you soo much it is working...