Help with table relationships and filter propagation

[jmunn]

Hi, I have been wrestling with a filtering issue that I can't seem to solve, and I haven't been able to find the answer in other posts I've read, so posting for advice here...

I have the following model:

The primary problem I'm running into is that I can't seem to get visuals sourced from Table E to filter correctly according to values selected among tables A - C. (however, if I filter via a Table D slicer, Table E will filter correctly).

Tables A - C define the relationships among users (teachers), their associated course sections ("DBN_CC_SID" surrogate key values), and students in those course sections. From there, Table D is intended to provide demographic information about each of the students, and Table E contains student assessment results. Students in Table D might have 0, 1, or multiple assessment records in Table E. 

Power BI correctly detected a 1:many relationship between Table D and Table C, but the direction of the relationship was "wrong" and prevented filtering in Tables A - C from propagating to Table D. Changing the relationship direction to "both" allows Table D to be filtered by tables A-C, but Table E still doesn't seem to respond correctly.

As an experiment, I created a slicer based on Table D that is a) successfully filtered by selections in Table B (hooray!) and that b) successfully filters Table E (hooray!) but only when a specific student ID is selected in the slicer. When no specific student is selected, Table E-sourced visuals once again don't filter correctly. 

Any ideas how to solve this?

[lbendlin]

1. Follow the arrows. "Can you get there from here?"

2. Try to separate your data into dimensions (stuff you filter by) and facts (stuff you calculate).  Try not mixing these up too much.

3. Clean up your data model so that dimensions filter facts, not the other way round.

If this doesn't do what you need, slowly reconsider bidirectional relationships, but be aware of the consequences.