Forum Discussion
PowerBi report for interdependent row
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 |
Hi Anonymous
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
1 Reply
Hi Anonymous
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