Filters from many-to-many to one-to-many Power Pivot

[AEAA]

Hi all,

I am fairly new to Power Pivot.

I am having some trouble understanding filtering between related tables.

I have my main table "Demographics", in which "Index" is Unique so:

Index Role
1 Engineer
2 Engineer
3 Manager

Then I have my "Automation Potential" table which has various info for Index

Index Category Potential
1 Calculation 10
1 Documentation 7
2 Calculation 5
2 Documentation 3
3 Calculation 6
3 Documentation 8

And finally I have "Type_Of_Structure" that has repeated values for index

Index Structure
1 Bridges
1 Tunnels
2 Other
2 Tunnels
3 Bridges

So both "Automation Potential" and "Type_Of_Structure" have a Many to One relationship with "Demographics". Meaning that every filter I apply to Demographics works properly on both tables. But I would like to filter "Type_Of_Structure", for example, "Bridges" and get "Automation Potential" to be filter by "Bridges". From what I understand the filter does not flow upstream, so I do not know how to bypass this.

FILTER = "Bridges"

---> "Automation Potential"

Index Category Potential
1 Calculation 10
1 Documentation 7
3 Calculation 6
3 Documentation 8


------


Furthermore, If I add three columns in my "main" Demographics table with 1s and 0s and then create separate slicers for Bridges, Tunnels and Other the calculation works fine, so not sure how to do this in my PowerQuery or Data Model:

Index Role Bridges Tunnels Other
1 Engineer 1 1 0
2 Engineer 0 1 1
3 Manager 1 0 0

 

[Matt Allington]

Write a measure with calculate

calculate(original measure that doesn’t work as needed, CROSSFILTER(Demog[column], typeOfStr[column],BOTH)

do the same for the other table.