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

[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

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