Help Modelling This Data

[lennardsomera]

Hi 

Hoping someone can help. 

I have the following table: 

Project ID	Project Lead	Supporting Lead
Project 1	Staff 1	Staff 3
Project 2	Staff 3
Project 3	Staff 1	Staff 4
Project 4	Staff 2	Staff 3
Project 5	Staff 4

which I am hoping to put in a visual table that looks like this, which returns the number of changes each staff is Leading and Supporting

Staff ID	Number of Projects Led	Number of Projects Supporting
Staff 1	2
Staff 2	1
Staff 3	1	2
Staff 4	1	1

However, this is what I am getting - the 'Number of Project Led' column is returning the correct result; however, the 'Number of Projects Supporting' is returning the incorrect result.  It's counting the number of each staff's project which is being supporting by another staff, not the number of projects that they are supporting. 

	Number of Projects Led	Number of Projects Supported
Staff 1	2	2
Staff 2	1	1
Staff 3	1
Staff 4	1	1

So Staff one is leading 2 projects and is not supporting any projects.  I'm expecting 2 and 0 for Staff 1

However, I am getting 2 and 2. 

'Staff N' pulls the staff's names and email address from a dimension table. 

Any help on why power BI is pulling/calculting the wrong figures and how i can rectify this? 

[ray_aramburo · Accepted solution]

You need to unpivot your data in order for your staff column to be unique (or a single one at least).

I'm attaching you the file I reproduced with your data. I hope it helps 🙂 

[lennardsomera]

Thank you very much. I'm a newbie to power bi so will give this a go!