Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.