Count rows in another table that match the current row

[geoffgarcia]

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

[ChrisMendoza]

A column could have been:

I presume your desired solution is a measure then.

[geoffgarcia]

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

TY!

[ChrisMendoza · Accepted solution]

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

[geoffgarcia]

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