Forum Discussion

Anonymous

Not applicable

3 years ago

Complicated Filter - PLEASE HELP!

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, s...

Icon for Community Champion rank

Community Champion

3 years ago

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
Not applicable
3 years ago
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
  Community Champion
  3 years ago
  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