Sum of hours based on a tree model

Hey everyone,

even I found a similar post in Microsoft community, I’m struggling a little bit and can’t fix it for my needs:

I have four tables:

  • “projects_demo”: an overview of all projects with “project_id”, “client_id” and concerning “parent_id” => The “parent_id” is my main problem.
  • “users_demo”: user information
  • “issues_demo”: an overview of all issues with concerning project ID => from this table I actually need only the
  • “time_entries_demo”: from this table I need the “issue_id”, “user_id”, “activity_id”, “hours” and “spent_on”.

What I need:
For which client, was spent how much time, on which project/sub-project and issues on which day.

General information:
a.) Every project can have n sub-projects (sub-projects have a and every sub-project can have further n sub-projects; the “client_id” is stored only in one of the projects on a higher level. Theoretically it should always be on the same level, but can’t guarantee at which one. The structure is something like:
Lvl1: Country
Lvl2: Department
Lvl3: Client
Lvl4: Subproject

b.) Every project (main and /or sub) can have n issues connected.

c.) Every issue can have n time_entries.

The main problem is point a.):
How can I summarize the tracked hours, based on time_entries, from the ‘lowest’ sub-project up to the main project, that I know the sum of the spent hours for each project/sub-project, client etc.

I hope, I could explain well enough.
Example data are attached.

Thank you all in advance,

best wishes
tickets_demo.pbix (1.6 MB)
tickets_demo.xlsx (3.3 MB)


You may want to take a look at the DAX Pattern Parent-child Hierarchies from SQLBI.