Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
paxton
New Member

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

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!!

1 ACCEPTED SOLUTION
AnkitBI
Solution Sage
Solution Sage

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.

 

View solution in original post

2 REPLIES 2
AnkitBI
Solution Sage
Solution Sage

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

@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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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