Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Countrows - Counting all rows of table, although filtered

Hi, 

I'm trying to replicate the Excel function COUNTIF, to count how many times a manager's name appears in a table. I have read numerous guides on this, and tried a few different ways of solving it, but all end up in the same way. Counting all rows, instead of the ones filtered. 

Here is one of the measures I use in a column:

 

Team size = CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[Manager]=Table2[Manager]))
 
I want it to output a number saying how many that shares that manager, but it outputs the total number of rows in the entire table. 

What am I doing wrong? 

Many thanks! 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , the Above formula, should work for what you shown in excel

    Team size = CALCULATE(COUNT(Table2[Manager]),FILTER(allselected(Table2),Table2[Manager]=max(Table2[Manager])))

     

    or

    Team size = CALCULATE(COUNT(Table2[Manager]),Allexcept(Table2,Table2[Manager]))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , the expected output is not clear . But try like 

     

    Team size = CALCULATE(COUNTROWS(Table2),FILTER(allselected(Table2),Table2[Manager]=max(Table2[Manager])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks amitchandak. It did not work, unfortunately. It outputed the same value in all rows as well. 

      I'm adding a print screen of an excel with my wanted output, hope it explains. 

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        Anonymous , the Above formula, should work for what you shown in excel

        Team size = CALCULATE(COUNT(Table2[Manager]),FILTER(allselected(Table2),Table2[Manager]=max(Table2[Manager])))

         

        or

        Team size = CALCULATE(COUNT(Table2[Manager]),Allexcept(Table2,Table2[Manager]))