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

Helper I

## Count rows in another table that match the current row

I've spend a healthy amount of time looking for a solution and am posting here as a last hope.

I have a table of offices and a table of employees, and very simply need to find a count of all employees in an office.

I'd like to add the employee count as a calculated column to my Offices table.

I've tried a number of things but nothing has gotten me the results I expect (red font below).

Any suggestions?

Table: Offices

 Office_ID Employee_Count (calculated column) NY656 3 NJ999 1 CT001 1

Table: Employees

 Employee_ID Office_ID 1001 NY656 1002 NY656 1003 NY656 1004 NJ999 1005 CT001

1 ACCEPTED SOLUTION
Resident Rockstar

Looks like it works as a measure too. I did have to tell the column to SUM though.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

4 REPLIES 4
Resident Rockstar

A column could have been:

I presume your desired solution is a measure then.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Helper I

Thanks Chris - that is so simple! I'm still a little lost on a measure versus a column, but will take a look.

TY!

Resident Rockstar

Looks like it works as a measure too. I did have to tell the column to SUM though.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Helper I

The rule of Geoff is within 5 minutes of asking a question I figure it out on my own. I believe the answer is:

``````EmployeeCount =
var _CurrentRowValue = Office[Office_ID]
RETURN
COUNTROWS(
Filter(Employees,
_CurrentRowValue=Employees[Employee_ID]
))``````

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.