Forum Discussion

elakrz's avatar
elakrz
Frequent Visitor
8 years ago

Count occurrences in a row

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...
Need Help
v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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

  • elakrz's avatar
    elakrz
    Frequent Visitor
    8 years ago

    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. 

  • Presti73's avatar
    Presti73
    Frequent Visitor
    6 years ago

    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!