Iterate by row of 1 table to search another table for match, then sum up

[paxton]

I have two tables: one that pulls in data from a sql server (contains the tasks that each team is working on as well as the team name and other task data), and then one that I created that I want to use to sum up the total amount of tasks each team has in the other table based on a few filters.

I am trying to create a calculated column that takes the team name in table 2, looks for that team name in table 1, filters out the tasks to status "done", and then returns the amount of finished tasks that specific team has. I have been trying to figure this out for days, but am having trouble getting the calculated column to iterate through each row of table 2 and do it for each different team. Right now it is just summing up all tasks regardless or the team and returning that value. 

Here is the calculated column I have so far" 

Completed_Tasks = 
IF(
   SUMX(
        Table1, FIND(Table1[Team_Name], Table2[Team_Name],,0)
       )>0,
   CALCULATE(
            COUNTROWS(Table1), Table1[Status] = "Done"
            ),
)

Here's simplified examples of the tables:

Table1:

Task_Name | Team_Name  | Status        |

Task 1         | Data Team    | Done         | 

Task 2         | Design Team| Not Done  |

Task 3         | Data Team    | Done         |

Task 4         | Design Team| Not Done  |

Task 5         | Design Team| Done         |

Task 6         | Design Team| Done         |

Table2:

Team_Name   | Tasks_Completed | Tasks_Incomplete |

Data Team     |                              |

Design Team |                              |

Not every team in Table2 will have a task in Table1. 

Any thoughts? I'd appreciate any help!!

[AnkitBI · Accepted solution]

Hi - You can use below if you are creating a relationship b/w tables based on team name.

Tasks_Completed = countx(filter(RELATEDTABLE(table1),Table1[Status] = "Done"),Table1[Status])

If no relationship, use below

Tasks_Completed = countx(filter((table1),Table1[Status] = "Done" &&  Table1[Team_Name] = Table2[Team_Name ]),Table1[Status])

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[jdbuchanan71]

@paxton 

If you join the two tables on [Team Name] then you will only need this measure.

Completed = 
CALCULATE(
            COUNTROWS(Table1), Table1[Status] = "Done"
            )

Then you pull in the team and that measure to get the count, no need to add it as a column and you can use the meaure downstream in other measures [completed] / [open] or whatever.