Filter two tables with the same file but different criteria

[KatheB]

Hello

I have two twin tables connected with a table called hierarchy, the first table (table1) is connected to the hierarchy table with a field called BU, so we have Table1.BU = Hierarchy.BU.

On the other hand in table two the connection is with the field Co Lead, i.e. Table2.Colead = Hierarchy.BU

What I want is that when filtering by Hierarchy.BU

Table 1 shows me all the rows related to the BU, but table 2 shows me everything related to the Hierarchy to which the Selected BU belongs.

How can I do that? Thanks

[KatheB]

TI used option 1, I created a Hierarchy column in Table 2 and changed the relationship as you mentioned. Thank you very much!

[Martin_D · Accepted solution]

You can add a column "Hierachy" to Table 2 that contains the corresponding Hierarchy to Table 2.Co Lead and change the relationship to Hierarchy.Hierarchy=Table 2.Hierachy. If you want to avoid a many to many relationsip, build a table inbetween that contains just a distinct list of Hierarchy. 
You could also create a measure that returns a blank value for all Table 2 rows that belong to excluded rows and a non-blank for all rows that are included and use it e.g. as a visual level filter that you test for "is not blank". But the relationship solution is probably faster.