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