Count occurrences in a row

[elakrz]

Here is my data base:

Name| 1st  | 2nd |  3rd |  4th | 5th

Ann   | five | five  | four | five | one

Tom  | four | one | four | five | four

and what I want to do is to create columns that would contain the number of occurrences in a row, so in this case what I want to achieve:

Name| 1st   | 2nd |  3rd |  4th | 5th  | Five | Four | One

Ann   | five  | five  | four | five | one |   3    |   1    | 1

Tom   | four | one | four | five | four |   1    |   3    | 1

How can I do that? 

[v-danhe-msft]

Hi @elakrz,

Based on my test, you can refer to below steps:

1.I have entered some sample data like the picture below:

2.Create three calculated columns.

Five = CALCULATE(COUNT('Table1 (2)'[Column2]),'Table1 (2)'[Column2]=5)+CALCULATE(COUNT('Table1 (2)'[Column3]),'Table1 (2)'[Column3]=5)+CALCULATE(COUNT('Table1 (2)'[Column4]),'Table1 (2)'[Column4]=5)+CALCULATE(COUNT('Table1 (2)'[Column5]),'Table1 (2)'[Column5]=5)+CALCULATE(COUNT('Table1 (2)'[Column6]),'Table1 (2)'[Column6]=5)

Four = CALCULATE(COUNT('Table1 (2)'[Column2]),'Table1 (2)'[Column2]=4)+CALCULATE(COUNT('Table1 (2)'[Column3]),'Table1 (2)'[Column3]=4)+CALCULATE(COUNT('Table1 (2)'[Column4]),'Table1 (2)'[Column4]=4)+CALCULATE(COUNT('Table1 (2)'[Column5]),'Table1 (2)'[Column5]=4)+CALCULATE(COUNT('Table1 (2)'[Column6]),'Table1 (2)'[Column6]=4)

Four = CALCULATE(COUNT('Table1 (2)'[Column2]),'Table1 (2)'[Column2]=1)+CALCULATE(COUNT('Table1 (2)'[Column3]),'Table1 (2)'[Column3]=1)+CALCULATE(COUNT('Table1 (2)'[Column4]),'Table1 (2)'[Column4]=1)+CALCULATE(COUNT('Table1 (2)'[Column5]),'Table1 (2)'[Column5]=1)+CALCULATE(COUNT('Table1 (2)'[Column6]),'Table1 (2)'[Column6]=1)

Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/0aqm5nnkro495go/Count%20occurrences%20in%20a%20row1.pbix?dl=0

Regards,

Daniel He

[Presti73]

Hi @v-danhe-msft ,

I couldn't find anything like this for some time.

My tables are similair to @elakrz , only there need to be a lot of extra calculations after those, that were (as far as i could (not?) find) not possible without your help.

As counting text is doable, i still started just making a duplicate of the table, replaced the values to be counted, by numbers, (i.e. yes=1, no=2, n/a=3 (this can be easily renamed in visuals again))  and then used the formula. It works perfectly and my other calculations and measures are now perfectly alined.

It is a lot of work (i need to do about 20 tables and still counting, where there will be up to 20 counted columns per table) but it is worth while. Creating the calculation in a notepad like environment makes things a lot faster by bulk replacing things like tablename or culumn number/name.

Thanx!

[elakrz]

I need to make it on string values and a lot of colums. So I was thinking of some functin, meassure or I don't know.