COUNT function for multiple values

[Anonymous]

Hello! 

I have a data set and would like to use a simple COUNT function to return me a total of each row in column 2. For example, some of the rows contain more than 1 "BC", so I would like it to return that value to me. When I use the COUNTA functionality, it only returns the number "1" for each output. 

Any help would be appreciated, thanks! 

[MFelix]

Hi @Anonymous,

If I understand you correctly you want to count how many values are on the BC column so in this case on 1, 4, 5 and 6 would be 1 and on row 2 and 3  the result would be 3  and so on.

Using the Power query create the following column:

List.Count(Text.Split([BC],","))

Using Dax you should use the following code:

DAX COUNT = LEN(BCCOUNT[BC])-LEN(SUBSTITUTE(BCCOUNT[BC];",";""))+1

Regards,

MFelix