Forum Discussion
Matrix filter not working when using a single direction relationship
Hi, I have a Matrix which displays data from "Availability Sheet", however I want to filter (via slicer) based on values from another table. So since these 2 tables have repeating 'Full Name' values, I have created a lookup table ("FullNames") with unique list of name values from both of these tables. Then I create 1:* relationships from look up table to both of my other tables.
However, when I try to filter my matrix by data from Advanced table, it does not works.
However, as soon as I do FullName to Advanced relationship Bidirectional is immediately works again. What could I be missing here? Or in this scenario since my filtered data flows from Advanced table, I do in fact need bidirectional relationship? I read that it's a good practice to avoid using Bidirectional so I am trying to do that.
Hii Aliceeee
With single-direction relationships, filter flow only travels from the lookup (dimension) table to the fact tables, not between fact tables. In your model, the slicer is coming from the Advanced table, so its filter cannot propagate through the FullNames lookup to the Availability Sheet. When you switch to bidirectional, Power BI allows that fact-to-fact filter flow, which is why it works. To keep single direction (best practice), create the slicer from the FullNames lookup table or use a measure with TREATAS() to explicitly pass the filter from Advanced to Availability.
6 Replies
Hii Aliceeee
With single-direction relationships, filter flow only travels from the lookup (dimension) table to the fact tables, not between fact tables. In your model, the slicer is coming from the Advanced table, so its filter cannot propagate through the FullNames lookup to the Availability Sheet. When you switch to bidirectional, Power BI allows that fact-to-fact filter flow, which is why it works. To keep single direction (best practice), create the slicer from the FullNames lookup table or use a measure with TREATAS() to explicitly pass the filter from Advanced to Availability.
Thanks! The TREATAS option indeed worked. Then slicer from the FullNames lookup table didn't make sense to me as I don't want to filter based on name, but perhaps I misunderstood your idea.
How do I mark answer as solved? I click 3 dots next to your post but such option does not exist.
Hi Aliceeeee
you can get the option like below , so you can accepte the users response as a solution. If you don’t see it, please try opening it in another browser or in a new window.
With *single-direction (1 → ) relationships, filters only flow from the lookup table down to the fact tables, not from one fact table to another. When you put a slicer from Advanced, that filter cannot travel back up to FullNames, and therefore cannot reach Availability. That’s why your matrix doesn’t react.
The recommended pattern is keeping relationships single direction and put slicers on the lookup table, not on fact tables.
If you insist on using a slicer from Advanced table, keep your single relations instead creating bi-directional relations and use a measure four your specific case:
Availability (Filtered) = VAR SelectedNames = VALUES ( Advanced[Full Name] ) RETURN CALCULATE ( [Availability Base Measure], TREATAS ( SelectedNames, Availability[Full Name] ) )Bidirectional relationships are risky because they let filters flow both ways, which can create ambiguous filter paths, unexpected results, and harder-to-debug reports. They can also hurt performance, especially in larger models, because Power BI has to resolve more complex filtering logic.
Hi Aliceeee,
Why the slicer does not work with single-direction relationshipsWhen you create a lookup table (FullNames) and set single-direction relationships (from FullNames to Availability Sheet and Advanced), the filter context flows from the lookup table down to the fact tables.
However, when you apply a slicer based on a field from the Advanced table, the filter context does not propagate back to the lookup table because the relationship is one-way. As a result, the matrix (which uses Availability Sheet) does not receive the filter.Why bidirectional works
When you switch the relationship to bidirectional, the filter context can flow both ways:- From Advanced → FullNames → Availability Sheet. This is why the slicer starts working immediately.
Do you really need bidirectional?
Not necessarily. Best practice is to avoid bidirectional relationships unless absolutely required, because they can:- Increase complexity.
- Cause ambiguous filter paths.
- Impact performance on large models.
Alternative approaches without bidirectional
- Use a measure with TREATAS
Instead of relying on bidirectional relationships, you can create a measure that applies the filter explicitly:
Filtered Measure = CALCULATE( [Your Existing Measure], TREATAS( VALUES('Advanced'[Full Name]), 'Availability Sheet'[Full Name] ) )- Consider a proper star schema
If both Availability Sheet and Advanced share the same dimension (FullNames), the slicer should ideally come from the lookup table, not from the fact tables.- Place the slicer on FullNames[Full Name].
- Ensure both fact tables have single-direction relationships to FullNames.
This is the cleanest and most scalable approach.If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
