Employee Manager Relationship - how to use PATH with monthly data

[Anonymous]

I want to use PATH() & related DAX functions (PATHITEM, PATHITEMREVERSE, etc.) however my data set from HR is expanded each month to include the latest updates (employees are added, lost, switch teams & report to new managers, etc.)

How can I use the PATH() function with the below type of data?

Thank you!

[Anonymous · Accepted solution]

FYI to everyone - I was able to solve this myself by creating 2 new columns that concatenate the Year-Month-ManagerID & Year-Month-EmployeeID and then feeding those 2 columns into the PATH statements.  Works great!

[Whitewater100]

Hi:

Your table can be set up like below.

Then here are some calc columns with path measures:

Hierarchy = PATH(Employees[EmployeeID],Employees[SupervisorID])

Hiearchy CT = PATHLENGTH(PATH(Employees[EmployeeID], Employees[SupervisorID]))

Path Item #2 = PATHITEM(PATH(Employees[EmployeeID],

Employees[SupervisorID]),2,1)

Path Item #2 = PATHITEM(PATH(Employees[EmployeeID],

Employees[SupervisorID]),3,1)   etc

Then your table can look like:

I hope this solves your question. Thanks..

[Anonymous]

Hi Whitewater - thanks for the reply but I know how the data "should" be setup for the PATH() function - problem is my data is structed correctly BUT it's monthly data in a single table so I need the PATH functions to only look at 1 month at a time...