Power BI Bidirectional Cross-Filtering for DirectQuery - When to Use It and When to Avoid It

Every Power BI consultant has, at some point, been handed a DirectQuery model where the filters do not behave the way the report builder expected. A slicer on one table refuses to filter a related table. A measure that obviously should respect a relationship returns blanks or wrong numbers. The report builder has spent two hours rewriting DAX with CALCULATE and CROSSFILTER and is now wondering whether the whole thing was a mistake.

Nine times out of ten the answer is bidirectional cross-filtering. Either it is turned off and needs to be on, or it is turned on and is causing the wrong rows to flow through the model. It is one of those Power BI settings that solves problems and creates problems at roughly equal rates depending on how it is used.

Microsoft has a short reference page for enabling bidirectional cross-filtering on DirectQuery models which covers the mechanics. What it does not really cover is when you should and should not use it, and the trade-offs you are signing up for. That is what I want to do here, based on what we have seen across a few dozen client models.

The thing you have to understand first

Power BI relationships have a default filter direction. By default, when two tables are related, the filter flows from the "one" side to the "many" side. If you have a Customers table joined to an Orders table by CustomerID, filtering Customers will filter Orders. Filtering Orders will not filter Customers.

This default works fine for the classic star schema. Your dimensions filter your facts, your facts do not filter your dimensions back. Clean and predictable.

It stops working as soon as you have a model that does not look like a clean star. Maybe you have a junk table, a many-to-many relationship, a model with multiple fact tables sharing dimensions, or a security model that needs to filter sideways. Now you need filters to flow in both directions on at least some relationships. That is where bidirectional cross-filtering comes in.

The setting itself is straightforward. Open the Edit Relationship dialog, set Cross filter direction to "Both", and if you are using row level security tick "Apply security filter in both directions". Two clicks. The consequences of those two clicks are not always so simple.

Where bidirectional filtering genuinely helps

We use bidirectional cross-filtering deliberately in three situations.

The first is many-to-many relationships modelled through a bridge table. Classic example - you have a Products table, a Categories table, and each product can belong to multiple categories. The natural way to model this is a ProductCategories bridge table with a row per product-category pair. If you want a slicer on Category to filter the Products visual, you need the filter to flow from Categories through the bridge into Products. That requires bidirectional cross-filtering on at least one of the bridge relationships. Without it, the slicer does nothing.

The second is row level security models that need to filter sideways. We had a financial services client in Melbourne where each user could only see clients in their region. The user-region mapping lived in a separate table joined to a regions dimension, which was then joined to the clients fact. For the security filter to actually reach the clients, it had to propagate through two relationships in directions the default would not allow. Bidirectional cross-filtering with "Apply security filter in both directions" was the right answer. The DAX they had been writing before to work around it was unmaintainable.

The third is models with multiple fact tables that share a single dimension and where you need cross-fact filtering. This is rarer and you should think hard before doing it, because there are usually cleaner alternatives. But if you have, say, a Sales fact and an Inventory fact both joined to a Products dimension, and you want a Sales filter to constrain the Inventory visual, bidirectional cross-filtering can do it. So can a calculated table that pre-joins them. So can rewriting the model. The choice depends on size and refresh constraints.

If you would rather have someone else work through which of these patterns applies to your model, our Power BI consulting team does model architecture reviews as part of any engagement.

Where it bites you

The reason bidirectional cross-filtering is not the default is that it can produce ambiguous filter paths. When filters can flow both ways on multiple relationships, the engine has to decide which path to use, and the answer is not always what the report builder expects.

The most common symptom is wrong numbers in visuals that look correct at first glance. A total that should be ten million comes back as three million, or twenty million, depending on which path the engine chose. The report builder does not see an error message. They see a number that looks plausible and they ship it. Two weeks later finance notices and you are in an incident review.

The second most common symptom is performance collapse. Bidirectional relationships generate more complex SQL on DirectQuery models. We have seen queries that took two hundred milliseconds with default filter direction take fifteen seconds with bidirectional turned on. Sometimes it is the difference between a query plan that pushes work to the source and one that pulls data into memory and joins on the desktop. If you flip a relationship to bidirectional and your dashboard goes from fast to slow, that is almost always the cause.

The third issue is the one that catches people out latest in the project - row level security leaks. If you have set up RLS and a user can see rows they should not, the first place to check is whether you have bidirectional filtering turned on without "Apply security filter in both directions". The filter direction setting and the security setting are independent. You can have one without the other, and that is exactly how data ends up in the wrong hands. Always check both boxes together when security is involved.

The DAX context that the Microsoft docs flag

One thing in the Microsoft documentation that is easy to miss but matters in practice is the note about UserPrincipalName. When you are writing cross-filtering DAX that uses USERNAME or USERPRINCIPALNAME, the value you get back depends on the environment. In the Power BI service, USERPRINCIPALNAME usually returns the user's email-style login ([email protected]). USERNAME might return something different depending on whether the model is hosted in the service or running on-premise via the gateway.

If you have built a security model that joins on USERNAME and tested it locally, then deployed it to the service and watched it break, this is why. The fix is usually to standardise on USERPRINCIPALNAME and to maintain a mapping table that links the principal name to whatever your business identifiers are (employee ID, salesperson code, branch code). It is one extra table and a small bit of M to maintain, but it makes the whole thing portable between environments.

DirectQuery versus Import - why this matters more in DirectQuery

Bidirectional cross-filtering is available in both Import and DirectQuery models, but the performance and behaviour are quite different.

In Import mode, the data is loaded into VertiPaq, the in-memory columnar engine that Power BI uses. VertiPaq handles bidirectional filtering reasonably well. The query plans get more complex but the engine is purpose-built for this kind of work and the overhead is usually tolerable.

In DirectQuery, every visual triggers a real-time SQL query against your source database. Bidirectional cross-filtering can result in joins and subqueries that the source database struggles with. The same relationship that runs cleanly in Import might bring a SQL Server instance to its knees in DirectQuery. We had a manufacturing client in Sydney whose IT team called us about a "Power BI problem" that turned out to be DirectQuery generating thirty-way joins because of layered bidirectional relationships. The fix was to redesign the model, not to tune the database.

The general advice we give is to be much more conservative with bidirectional filtering in DirectQuery than in Import. If you can solve the problem with a different model design, do that. If you can solve it with a small calculated table that pre-aggregates the cross-filter, do that. Only reach for bidirectional cross-filtering when there is no cleaner alternative, and benchmark the performance before and after every time.

What we do on engagements

When we audit a client's Power BI model, the first thing we look for is bidirectional relationships. They are the most reliable indicator of either a model design that needed rethinking, or a security pattern that was solved at the wrong layer.

The questions we ask:

Why is this relationship bidirectional? If the answer is "I do not remember" or "because it was the only way to get the filter to work", we usually find a cleaner solution by reshaping the model.

Does this relationship have RLS on either side? If yes, is "Apply security filter in both directions" ticked? If not, we have probably found a security leak.

What happens to query performance if we switch it back to single direction? We test this in DAX Studio. Sometimes the answer is "nothing", which means the bidirectional flag was added defensively and is not needed. Sometimes the answer is "everything", which means the model genuinely depends on it.

Could a calculated table replace the bidirectional behaviour? Often yes, and the resulting model is faster and more maintainable.

Most of the time, on models that started life with someone learning Power BI on the job, we end up removing about half the bidirectional relationships and the model gets faster and more predictable. The remaining ones are kept deliberately, with comments in the documentation explaining why.

What I would tell you to do this week

If you are running a DirectQuery model in production, open it up and look at every bidirectional relationship. For each one, ask the four questions above. Be especially suspicious of bidirectional relationships in models with RLS - that is where the worst incidents happen.

If you are designing a new model and you are tempted to set everything to bidirectional because it makes filters "just work", resist. Build the model with default directions first. When you find a specific case where you need the filter to flow both ways, change just that relationship and document why. The future you who has to debug a wrong number at month end will thank present you.

If you want help thinking through model architecture or have a Power BI report that has started giving wrong numbers, our Microsoft Fabric specialists do model review engagements specifically for this. You can also have a look at our broader Power BI consulting services if you need ongoing support.

The Microsoft reference page for bidirectional cross-filtering on DirectQuery is short but worth bookmarking. There is also a longer whitepaper linked from it which goes into the engine behaviour in more detail. Both are worth a read before you make changes to a production model.