Forum Discussion
Helper ICount 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 |
Looks like it works as a measure too. I did have to tell the column to SUM though.
4 Replies
geoffgarciaThe 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] ))- ChrisMendoza
Resident Rockstar
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
Looks like it works as a measure too. I did have to tell the column to SUM though.