Multiple Hierarchy Levels with Parents Including Children Values
Hi, been pulling my hair over this for a week now, please help 😄
I have two tables, Departments and Projects.
Projects looks like this
Departments looks like this
Some projects may be directly connected to a department through the department ID in the projects table, but they could also be connected to another department through the parent Id of its department in the department table. So when I count how many projects each department has, I must take into consideration the projects directly connected to it, and its children, and I need to do a drill down process as well.
Using the above example it will look something like this.
# of Projects
If I'm going to drill down through Department Type, it would be Sectors>General Departments>Base Departments, and the number of projects for each should be the same as in the measure table above.
I've tried using the hierarchy function, but because projects are directly connected to departments on different levels, you'd get general departments in the base departments levels, and sectors in the general departments level and so on.
I've attached an excel with the tables above. I'd really appreciate some help on this.
Hierarchy([Department ID], [Parent ID],TmpID)
LOAD [Department ID] as TmpID,* INLINE [
Department ID, Parent ID, Department Type
240, 150, General Department
550, 240, Base Department
150, Null, Sector
load TmpID as I,* resident Departments;
drop table Departments;
drop fields TmpID,[Department Type],[Parent ID] ,[Department ID];
load * resident CrossTmp;
drop table CrossTmp;
drop field Tmp1;
LOAD Department_ID as I,* INLINE [
Project ID, Department_ID
load Tmp2 as [Department ID],count([Project ID]) as NbrProject resident Crosstable group by Tmp2;
drop table Crosstable;
Regards, Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
thanks for your reply, I applied this HierarchyBelongsTo function, and its partly the solution I guess, because it helps me sum up the ancestors and those below them, but something needs to be added to it and I have no idea what. The problem is that the hierarchy isn't fixed, you could have a sector that's a parent to base department, and the projects could be linked directly to a sector, or to a general department. What I'm getting with this solution is a sector on each level of department types, but it should stay the same type. Here's the actual data set attached for your reference, pardon the names of the departments as they are in Arabic, but they are not necessary.
The confusing part lies in the levels of ancestry.