Count of names for team members

[Graceshirley]

Hello,

I have a spreadsheet of data, where each project has a team lead and may have team members. There may be 0, 1, 2, etc team members for each project. Not all team leads are team members, and not all team members are team leads. 

How can I build a chart with the count of names that are in a project, regardless if they are a team lead or team member? 

here is an excerpt from my data:

team lead          Team 

John                   Barry, Charles 

sue                     Chris                  

Barry

For example, based on my example above I want power BI to show:

name            #
Barry            2

charles         1

sue              1

chris            1

John            1

[Anonymous]

Hi  @Graceshirley ,

Here are the steps you can follow：

1. Power query – Split column by delimiter.

2. Select [team.members.1] and [team.members.2] – Transform – Unpivot column.

3. Create calculated table.

Table 2 =
var _table1=
DISTINCT('Table'[team lead])
var _table2=
DISTINCT('Table'[Value])
var _table3=
UNION(
    _table1,_table2)
return
FILTER(
    _table3,[team lead]<>BLANK())

4. Create measure.

Measure =
CALCULATE(DISTINCTCOUNT('Table'[team lead]),FILTER(ALL('Table'),
        'Table'[team lead] =MAX('Table 2'[team lead])))
+
COUNTX(
    FILTER(ALL('Table'),
         'Table'[Value] =MAX('Table 2'[team lead])),[Value])

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

If those 2 columns are in the same table, then try this approach

1. Remove the second column from the table and put that in a new table
2. In the second table, split the date by , and split into rows
3. Append the two tables
4. Click on Close and Apply
5. To your visual, drag the column and write this measure

Measure = countrows(Data)

Hope this helps.

[Daniel29195]

@Graceshirley 

having this structure of data,  you need to edit in a way that would make it easier for you to work with . 

team lead          Team 

John                   Barry, Charles 

sue                     Chris                  

Barry

what i think you should do is to have 3 tables : ( this is base on the table im mentioning in my reply ) 

dimteamlead with column 

--> teamlead name

john

sue

barry

dimteam -->  with column

--> leadname

barry

charles

chris

teamlead_lead_bridge table --> with columns

teamlead   lead

john            barry

john            charles

sue               chris

barry   

NB: the third table is your table transformed in power query so you can expand the barry, charles into two rows. 

this can be done easily using the split by columns in power query -->  you choose the delimiter -->  comma and in the advanced options you choose by rows instead of columns .

now that you created the 3 tables, you link both dimteamlead and dimteam to the third table . 

now to get the count of names regardless if they are teamlead or team , 

you simply count the names in dimteamlead  +  count of names in dimteam .

hope this make sense. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Graceshirley]

Thank you for the suggestion and pictures. When you say "link" what do you mean? 

[Daniel29195]

@Graceshirley 

link means creating a relationship between the tables .