cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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!
1 ACCEPTED SOLUTION
Super User

@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 4
Super User

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

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

Anonymous
Not applicable

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.

Super User

@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]))

Anonymous
Not applicable

Many thanks @amitchandak . Worked like a charm!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors