You know a relationship links a dimension to the fact. That link does work through cross-filtering. When you filter one table, the filter propagates along the relationship to the related table. Filter Product[Category] = "Peripherals", and the filter flows down the one-to-many relationship into Sales, so [Total Sales] re-sums only peripheral rows. That propagation is what lets a single fact-table measure respond correctly to a slicer on any dimension. In effect, the filter travels the star's spokes into the centre.
Direction matters, and the default is single. Out of the box, a relationship cross-filters in one direction only, from the "one" side to the "many" side, which means from dimension to fact. That's the direction you almost always want, because dimensions filter facts. The reverse, filtering a dimension by what appears in the fact, doesn't happen automatically. Put Region[Name] on rows and [Total Sales] measures each region correctly, with the filter running one way, from dimension to fact. That default is deliberate. It keeps filter paths short, predictable, and fast.
Advertisement
Should a relationship filter one way or both?
You can set a relationship to bidirectional, cross-filter "Both", so filters flow from fact to dimension too. Now and then you'll have to. Certain many-to-many patterns need it, and so do some "did this customer buy anything?" filters. But it's a power tool with a recoil. Bidirectional filters can create ambiguous filter paths, where there are two routes from table A to table B and the engine can't decide which to honour. They can also cause unexpected totals and hurt performance. The professional default is to leave every relationship single-directional. Switch a specific one to bidirectional only with a clear reason, and test that the totals still make sense. Flipping to bidirectional by reflex is common mistake #5.
Two relationship pathologies are worth learning to spot and avoid. Both come from straying off the star schema:
- Many-to-many / ambiguous relationships. Say neither side of a would-be relationship has unique keys, as with two tables that both list regions with duplicates. Then you have a many-to-many and an ambiguous filter path. The engine either refuses the relationship or, via a bridge table, behaves in ways that surprise you. The fix is almost always a proper dimension table with unique keys between them, which restores a clean one-to-many on each side.
- Multiple paths between two tables. If table A can reach table B by two different chains of relationships, filters become ambiguous. Excel forces you to make one path inactive, shown as a dashed line. You then activate it deliberately for a specific measure using
USERELATIONSHIP, which Power Pivot & DAX II covers. As a brown-1 default, design so there's one path.
Think of relationships as pipes and cross-filtering as the water. The star schema and the single-direction defaults keep the plumbing simple enough to reason about.