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?

[v-pnaroju-msft · Accepted solution]

Thank you, @lbendlin , for your response.

Hi jmunn,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, it appears that the filter context from Tables A to C is not fully reaching Table E due to either the direction of relationships, inactive links, or filtering limitations in your data model.

Please follow the steps below, which may help to resolve the issue:

1. Ensure that there is an active one-to-many relationship from Table D (Students) to Table E (Assessments).
2. Filters should flow from Tables A to B to C to D to E. You may need to set bi-directional filtering between the intermediate tables with caution.
3. Confirm that no inactive or ambiguous relationships exist. Remove any unnecessary links that may obstruct the flow of filters.
4. The slicers work when a specific Student ID is selected, indicating that the context is not reaching Table E globally. Ensure that the slicers and visuals are not overriding this context.
5. Keep student and course information in dimension tables, with assessments as your fact table, to ensure effective and clear filtering.

If you find our response helpful, kindly mark it as the accepted solution and provide your feedback. This will assist other community members facing similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thank you, @lbendlin , for your response.

Hi jmunn,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, it appears that the filter context from Tables A to C is not fully reaching Table E due to either the direction of relationships, inactive links, or filtering limitations in your data model.

Please follow the steps below, which may help to resolve the issue:

1. Ensure that there is an active one-to-many relationship from Table D (Students) to Table E (Assessments).
2. Filters should flow from Tables A to B to C to D to E. You may need to set bi-directional filtering between the intermediate tables with caution.
3. Confirm that no inactive or ambiguous relationships exist. Remove any unnecessary links that may obstruct the flow of filters.
4. The slicers work when a specific Student ID is selected, indicating that the context is not reaching Table E globally. Ensure that the slicers and visuals are not overriding this context.
5. Keep student and course information in dimension tables, with assessments as your fact table, to ensure effective and clear filtering.

If you find our response helpful, kindly mark it as the accepted solution and provide your feedback. This will assist other community members facing similar queries.

Thank you.

[jmunn · Accepted solution]

Thanks @v-pnaroju-msft very much for these thorough and specific suggestions.

I've gone through each of them, and in the course of doing so discovered that the incorrect filter propagation was actually an issue with blanks.

Table D doesn't have any blanks the field being used in the relationships with other tables, but there can be values in Table B or Table C that don't exist in Table D. It seems that, when this happens, a blank is somehow introduced, which then interferes with the expected # of records coming from Table E. I don't totally understand how this happens, but I added a "Filter on all pages" to filter out blanks in Table D, and that seems to have solved the issue. 

Again, thank you for the nudge and assistance!

[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.