Calculate values one row / one columns

[Anonymous]

Hi everyone, I am working with a projectplanning dataset. We have a list of possible opportunities with different candidates dedicated to them. For each opportunity I would like to calculate the number of candidates. The data is something like this:

Project name	Candidates
Datalake at X	10000005, 10000012, 10000008
Data ingestion at X	10000005, 10000006
Impelementation at X	10000007
business Intelligence at X	10000009, 10000008, 10000011, 10000010

The desired outcome would be:

Project name	Candidates	Number of candidates
Datalake at X	10000005, 10000012, 10000008	3
Data ingestion at X	10000005, 10000006	2
Impelementation at X	10000007	1
business Intelligence at X	10000009, 10000008, 10000011, 10000010	4

Anyone know a way to do this?

Thanks in advance!

[az38 · Accepted solution]

Hi @Anonymous 

create a new column

Number of candidates = PATHLENGTH(SUBSTITUTE('Table'[Candidates], ",", "|"))

[Anonymous]

Hi thanks for your quick answer, this worked like a charm!

[AlB]

Hi @Anonymous 

I guess you could create a calcualted column that counts the number of commas (and add 1), since that seems to be the separator:

Number of candidates =
LEN ( Table1[Candidates] ) - LEN ( SUBSTITUTE ( Table1[Candidates], ",", "" ) ) + 1

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Anonymous]

Hi @AlB I am going with the solution of @az38 because your calcuation also returns a "1" when the candidate field is empty and the other solution doesn't. Would eb a good solution if everythign is fileld in tho, but I didn't provide u the information that there were missing values.