Complicated Filter - PLEASE HELP!

[Anonymous]

Hi there,

I come to you in desperation - I have been pulling my hair out for days and I am sure there is a relatively simple solution, but I do not see it. It is quite tricky for me to explain, so please bear with!

I have two data sources, both from Azure Devops Analytics - one that displays all of the parent work items and one that displays the child work items. Each parent work item could have up to 16 child items, as each child item relates to a different team. My data looks like this:

Data Source 1: Parent
Work Item ID
1
2
3
4
5
6

Data Source 2: Child
Parent Work Item ID (same ID as work item ID in Data Source 1)
1	Team A	Impacted
1	Team B	Impacted
2	Team C	Impacted
3	Team D	Impacted
3	Team D	Impacted
6	Team A	Impacted

I need a way of checking if the parent work item ID from Data Source 2 also exists in work item ID from Data Source 1, but by individual team.

In essence, I want to be able to tell if there is a child work item or not for each team!

[Greg_Deckler]

@Anonymous So, if you create a relationship between DS1 and DS2 based on Work Item ID 1 -> * DS2 Parent Work Item ID. Then you could simply do a distinct count of your 2nd column in DS2 and if that number equals this measure (below) then you have a work item for each team:

Distinct Count of Teams Measure = 
  COUNTROWS(ALL('Data Source 2: Child'[Team Name Column]))

[Anonymous]

@Greg_Deckler Hi Greg, 

Thank you for your response! I should have elaborated on what I needed the output for - there definitely won't be a work item for each team - I need to be able to display a list for each individual teams of all parent work items that do not have a child related specifically to that team, i.e. for Team C I would have the list of Work Item ID 1, 3, 4, 5, 6 or for Team A, I would have a list of work item 2, 3, 4, 5 as there is no child item associated with that team to the parent work item.

This is a way to show give the teams a view of what they have NOT been marked as impacted on

[Greg_Deckler]

@Anonymous OK, well you could do that with a 2 way relationship and a CONCATENATEX('Table 1', [Work Item ID], ", ") or if you don't want a 2 way relationship or any relationship you could do this:

Measure = 
  VAR __ChildParentIDs = 
    SELECTCOLUMNS(DISTINCT('DS2'[Parent Work Item ID]),"Work Item ID", [Parent Work Item ID] 
    // assumes team name is in visual from DS2
  VAR __ParentIDs = DISTINCT('DS1'[Work Item ID])
  VAR __Result = CONCATENATEX(INTERSECT(__ChildParentIDs, __ParentIDs), [Work Item ID], ", ")
RETURN
  __Result