Filtering a parent based on the child

[Anonymous]

I have two tables - one named Issues and one named CorrectiveActions.  Each issue can contain 0 or more corrective actions.  I have the relationship established in Power BI and displaying the issues and their associated corrective actions is working fine.  

Each corrective action has an owner tied to it.  A new requirement came in that only the corrective actions belonging to a specific person be displayed. Without changing the existing relationship, how do I filter the corrective actions to a specific person so that only the relevant issues are displayed?  Right now, when I filter to the corrective action owner, I still see all of the issues, regardless of relevancy.

Thank you,

Brian

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Issuestable:

CorrectiveActionstable:

Here are the steps you can follow：

1. Create calculated table.

Slice_Table = DISTINCT('CorrectiveActionstable'[CorrectiveActions])

2. Create measure.

Flag =
var _corrselect=SELECTEDVALUE('Slice_Table'[CorrectiveActions])
var _own=CALCULATE(MAX('CorrectiveActionstable'[Own]),FILTER(ALL(CorrectiveActionstable),'CorrectiveActionstable'[CorrectiveActions]=_corrselect))
return
IF(
    MAX('CorrectiveActionstable'[Own])=_own,1,0)

3. Use [CorrectiveActions] of Slice_Table as the slicer, Place [Flag] in Filters, set is=1, apply filter.

4. Result:

When selecting a slicer, all Issues corresponding to Own will be displayed

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Assume issue is used alone in slicer or table. take a measure from correctiveactions and use that visual level filter and check that it is not blank

countrows(correctiveactions)

[Anonymous]

@amitchandak -

Issues are in a table visualization at the top of the screen.  Corrective Actions are in a table visualization at the bottom of the screen.  When I filter the Corrective Actions table visualization on the owner, I want the Issues table visualization to display only the relevant associated issues.  By default, when I apply a filter to the Corrective Actions table visualization, I am still seeing every single issue.