PowerBi report for interdependent row

[Naseemsk]

Hi All, Which option should i use when the data is in the below structure. Work item "Test case" is the parent and the "Bug" is the child links. One test case work item might have multiple child links. I want to filter the test cases based on the state of child links, if the state of child link is "Done" or "Removed", then the parent test case should be identified and reported.

In the below data structure, test case 1 and 3 need to be shown, pls suggest.

ID	Work Item	Title	State
1	Test case	Test case 1	Completed
10	Bug	Issue1	Done
11	Bug	Issue2	Removed
2	Test case	Test case 1	Completed
12	Bug	Issue12	Done
13	Bug	Issue13	New
3	Test case	Test case 1	Completed
20	Bug	Issue20	Done
21	Bug	Issue21	Removed

[v-jayw-msft · Accepted solution]

Hi @Naseemsk

Create an index column in Power Query editor and create columns like below:

group = CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[State]="completed"&&'Table'[Index]<=EARLIER('Table'[Index])))

mark = IF('Table'[State] in {"completed","removed","Done"},1,0)

_min = CALCULATE(MIN('Table'[mark]),ALLEXCEPT('Table','Table'[group]))

At last, create a measure like below:

Measure = IF(SELECTEDVALUE('Table'[Index])=SELECTEDVALUE('Table'[group])&&SELECTEDVALUE('Table'[_min])=1,1,BLANK())

Best Regards,

Jay