Project Online - timephase assignment attribute

[Anonymous]

Hello All,

I have a Power BI connection to MS Project Online.  I have successfully created numerous reports, but am struggling with this one.  I have a MS Project custom column titled "Crew ID" with numerical values (1, 2, 3, 4, etc).  Each assignment has a Crew ID value designated.  This is so that we can see which individuals are affiliated as a crew.  I would like to add a Power BI report which is timephased showing how many crews we have deployed on a weekly basis.  I have created timephased reports of resource 
work, but have been unable to use the distinct count of the crew ID as the table value.  Does anyone have a suggestion?

Thanks,

Stacey

[v-huizhn-msft]

Hi @Anonymous,

I am not specific about MS Project Online, you'd better share the sample data and list expected result, so that we can post detailed solution which is close to your requirement.

Best Regards,
Angelia

[Anonymous]

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

[Newbie1]

Please provide the name of the tables you are using in Project online while attempting the solution.

[Anonymous]

Hello,

I am pulling the Project name and dates from [AssignmentTimephasedDataSet] and the Crew ID from [Assignments]. 

Thank you,

-Stacey

[Anonymous]

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).

[Newbie1]

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.