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: https://community.fabric.microsoft.com/t5/Desktop/Summary-measure-in-a-tree-model/m-p/871198
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 project.id
- “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 parent.id) 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
Greg
tickets_demo.pbix (1.6 MB)
tickets_demo.xlsx (3.3 MB)