Forum Discussion
Project Online - timephase assignment attribute
Hi v-huizhn-msft,
Here's the structure of my records. Each project has multiple resources assigned. Each resource has a "Crew ID" associated and specific Start/Finish dates.
| Project | Resouce | Crew ID | Start | Finish |
| Project A | Resource A | 1 | 3/5/2018 | 3/16/2018 |
| Resource B | 2 | 3/5/2018 | 3/16/2018 | |
| Resource C | 3 | 3/19/2018 | 3/30/2018 | |
| Project B | Resource D | 1 | 3/12/2018 | 3/23/2018 |
| Project C | Resource E | 1 | 3/5/2018 | 3/30/2018 |
| Resource F | 1 | 3/5/2018 | 3/30/2018 | |
| Resource G | 2 | 3/19/2018 | 4/13/2018 | |
| Resource H | 2 | 3/19/2018 | 4/13/2018 |
What I would like to accomplish is a Power BI report which lists each of the projects and a count of how many distinct Crew ID values are associated on a weekly basis. For the above, it would look like:
| 3/5/2018 | 3/12/2018 | 3/19/2018 | 3/26/2018 | 4/2/2018 | 4/9/2018 | |
| Project A | 2 | 2 | 1 | 1 | ||
| Project B | 1 | 1 | ||||
| Project C | 1 | 1 | 2 | 2 | 1 | 1 |
Does this make sense?
Thanks so much for any help you can provide!
-Stacey
Please provide the name of the tables you are using in Project online while attempting the solution.
Hello,
I am pulling the Project name and dates from [AssignmentTimephasedDataSet] and the Crew ID from [Assignments].
Thank you,
-Stacey
I have attempted to create this as a Matrix with the Project Name for the rows, week beginning date for the columns, and DistinctCount of Crew ID's as the values. However, it is giving the count of distinct crews across all projects, rather than by each project (see below).
Unfortunately, I dont have access to those tables at my end. Have you tried using Resourcedemanddata table along with matrix visualization. It seems to have all information like day, project name and resource.