Forum Discussion

Anonymous's avatar
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...
Need Help
Anonymous's avatar
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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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