Creating Parent-child hierarchies with MS Project data

Hi Anonymous ,

There must be two columns [Employee] and [Manager] in your table. Then you can use PATH function to get hierachy level.

Path = PATH(Employee[EmployeeId], Employee[ManagerId])

My Sample:

Then use PATHITEM to catch value in each level.

Level 1 = PATHITEM(Employee[Path], 1, 1)

Level 2 = PATHITEM(Employee[Path], 2, 1)

...

Due to there are blank values in your levels, I think you will get result in blank level.

Try this code.

Measure = 
IF(ISINSCOPE(Employee[Level 4]) = BLANK(),SUM(Employee[Index]),
IF(ISINSCOPE(Employee[Level 3]) = BLANK(),SUM(Employee[Index]),
IF(ISINSCOPE(Employee[Level 2]) = BLANK(),SUM(Employee[Index]),
IF(ISINSCOPE(Employee[Level 1]) = BLANK(),IF(ISBLANK(MAX(Employee[Level 2])),BLANK(), SUM(Employee[Index])),BLANK()))))

Result is as below. Left one is like yours and right one is the result based on measure.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.