In Power BI, what goes wrong when you set a relationship's cross-filter direction to Both?

## What the setting does Every Power BI relationship has a cross-filter direction. `Single` — the default on a one-to-many relationship — means filters travel only from the one side (the dimension) to the many side (the fact table). `Both` means they also travel back up. It is a one-click change in the relationship dialog and it is one of the most consequential clicks in the tool. The reason people reach for it is legitimate: they want a slicer on the fact table to shrink a dimension list, or a count of dimension rows that had activity — "how many products actually sold this month", "only show customers with an order". Those are real requirements. The problem is that the setting solves them **globally**, for every visual and every measure in the report, forever. ## Failure one: unintended cross-filtering between fact tables A typical model has two fact tables — `Sales` and `Budget` — both related to a shared `Date` and `Product`. Set `Product -> Sales` to Both, and a filter applied to `Sales` now climbs into `Product` and descends into `Budget`. A user slicing to a sales channel finds the budget number changing too, even though budgets were never captured by channel. Nobody asked for that, and nothing on the report page explains it. ## Failure two: ambiguity Add a second bidirectional relationship and there can be **more than one route** between the same two tables. Power BI actively defends against this: when a new relationship would make the active path ambiguous or circular it refuses to activate it and offers to create it as inactive. That refusal is often the first sign a modeller gets that their model has a loop. Where the engine cannot detect the problem statically, you end up with results that are correct for one interpretation and wrong for another — the hardest class of BI bug, because every individual number looks plausible. ## Failure three: performance Propagating a filter from a large fact table up to a dimension means resolving the distinct set of keys that survive the filter, then applying that set to the dimension. On a fact table with hundreds of millions of rows and a high-cardinality key this is materially more expensive than pushing a small dimension filter downward. Bidirectional relationships on high-cardinality keys are a common finding when a model that used to be snappy suddenly is not. ## Failure four: security Row-level security applies filters to tables through roles. Because bidirectional relationships change how filters propagate, they change what a restricted user can infer. The relationship carries an explicit option controlling whether the security filter is applied in both directions, and getting it wrong can either leak rows a role should not see or over-filter a report into emptiness. The detailed RLS design belongs to its own topic, but a modeller must know that turning on Both is not a display decision when roles exist — it is a security decision. ## The scoped alternative Most of the time the requirement is one measure, not the whole model. `CROSSFILTER` lets you change a relationship's direction inside a single `CALCULATE`: ```dax Products Sold = CALCULATE ( COUNTROWS ( Product ), CROSSFILTER ( Sales[ProductKey], Product[ProductKey], BOTH ) ) ``` Every other visual keeps the predictable single-direction behaviour; only this measure sees the upward propagation. `CROSSFILTER` can also set a relationship to `NONE` for a measure that must ignore a filter path entirely. A second alternative avoids propagation altogether: write the measure against the fact table — `DISTINCTCOUNT(Sales[ProductKey])` answers "how many products sold" without touching relationship direction at all, and is usually faster. ## Where bidirectional is genuinely reasonable It is not forbidden. The commonly accepted case is a **bridge table** resolving a many-to-many between two dimensions: the bridge sits between them, has no measures of its own, and needs a filter to pass through it to reach the other side. Even there, many modellers prefer `CROSSFILTER` in the specific measures. The test is whether the propagation is a permanent property of the model's semantics or a convenience for one visual. ## What interviewers listen for A strong answer names at least ambiguity and unintended cross-filtering between fact tables, offers the scoped `CROSSFILTER` alternative, and mentions the RLS interaction. A weaker one describes bidirectional filtering as simply "slower", or as a best practice for making slicers feel interactive, which is exactly the reasoning that produces models nobody can debug six months later.