Cross filter direction is the relationship setting that decides which way a filter travels between two related tables in a Power BI model. Single sends filters from the "one" side (a dimension such as Product) to the "many" side (a fact table such as Sales). Both also sends them back from Sales to Product. Use Single by default, and use Both only for a specific need such as a bridge table, because Both can create ambiguous paths and slower queries.

Types of cross filter direction in Power BI

Which options you get depends on the relationship's cardinality:

When a relationship has a "one" side, filters always flow from that side. The direction setting only decides whether they also flow back.

Single direction cross filtering

With Single, a filter on the dimension reaches the fact table, but not the other way. Select "Bikes" in a Product slicer and Sales shows only bike sales. Select a sales region and the Product slicer still lists every product, including ones never sold there.

This is the default for one-to-many relationships, and it fits a star schema:

• Filter paths are predictable.

• There is only one way for a filter to reach any table.

• Queries are cheaper than with Both.

Bidirectional cross filtering (Both)

With Both, filters also travel from the "many" side back to the "one" side. The same region selection now also narrows the Product slicer to products sold in that region.

Legitimate uses:

• A bridge table for a many-to-many design, such as customers who belong to several account groups.

• A dimension that should show only values with matching facts, in a small model where the cost is acceptable.

• Row-level security that must flow from a security table through a bridge.

Benefits and challenges of bidirectional filtering

Benefits of bidirectional cross filtering

• Lets a bridge table pass filters between two dimensions in a many-to-many design.

• Slicers show only values that have data behind them, without extra DAX.

• Lets security filters reach tables they otherwise could not, when combined with the security option below.

Challenges and risks

• Ambiguous paths. With Both on several relationships, there can be two routes from one table to another. Power BI then either refuses the change or picks a route by its own priority rules, which may not be the one you meant.

• Slower queries. Microsoft states that bidirectional relationships can hurt performance, because each filter has to be applied in more places.

• Surprising results. A slicer on a fact-side value quietly filters dimensions, which changes totals in visuals that have nothing to do with that slicer.

• Harder debugging. When a number looks wrong, you have to trace filters in two directions through every relationship.

Turning on Both to make a visual "work" usually means the model is missing a table or a measure.

Best practices and use cases

Best practices

• Default to Single.

• Build the model as a star schema, so every dimension relates directly to the fact tables.

• Use Both only for a named reason, such as a bridge table, and document that reason with the model.

• Where only one measure needs a reverse filter, use CROSSFILTER inside that measure instead of changing the relationship.

• After any change, check slicers and totals on every page for filters you did not expect.

Common use cases

In a star schema, Single covers almost everything. Filters flow from Date, Product and Customer into Sales.

Both is justified in three places. The first is a many-to-many design through a bridge table, where the relationship between the bridge and one dimension must be Both for the filter to reach the fact table. The second is row-level security that has to pass through such a bridge. The third is a small model where you want every slicer to show only values with data, and you have checked the cost.

If you only need fact-to-dimension filtering inside one calculation, CROSSFILTER in DAX does it without changing the model.

How to set or change cross filter direction

Steps to change cross filter direction

1. In Power BI Desktop, open Model view.

2. Double-click the line between two tables, or select Manage relationships on the ribbon and choose the relationship.

3. Under Cross filter direction, choose Single or Both.

4. If you chose Both and use row-level security, decide whether to check Apply security filter in both directions.

5. Select Save or OK.

Visual indicators

• A single arrowhead on the relationship line shows the direction filters flow.

• A double arrowhead shows a bidirectional relationship.

• A dashed line is an inactive relationship, which only a measure using USERELATIONSHIP can activate.

Changing direction inside one measure

CROSSFILTER changes a relationship's direction for one calculation only. It takes the two related columns and a direction: ONEWAY, BOTH or NONE.

This measure counts customers who bought something in the current filter context, such as a selected product category:

Customers Who Bought =
CALCULATE (
    DISTINCTCOUNT ( Customer[CustomerKey] ),
    CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH )
)

Without CROSSFILTER, a Product filter never reaches the Customer table, so the measure would count every customer. Often there is a simpler way. Counting the key on the fact table gives the same number with no direction change:

Customers Who Bought =
DISTINCTCOUNT ( Sales[CustomerKey] )

Use CROSSFILTER when you need other columns from the dimension, not just the key.

Practical applications and examples

Take a model with a Sales fact table and three dimensions: Product, Customer and Date. All three relationships are one-to-many and Single.

• A Product Category slicer filters Sales, and every sales measure responds.

• The same slicer does not filter the Customer table. A Customer slicer on the page still lists every customer.

• A measure that needs "customers who bought this category" uses CROSSFILTER or counts Sales[CustomerKey], as shown above.

Now set the Sales-to-Customer relationship to Both. The Customer slicer now shrinks to customers who bought the selected category. That may be what the page needs. But the Product slicer now also filters Customer, and Customer filters Sales, and if Date is also set to Both, there are two routes between some tables. That is where ambiguity starts. Keeping the relationships Single and handling the special case in one measure avoids it.

Relationship properties and cardinality

Cross filter direction is one of several relationship properties. The others are:

• Cardinality, which decides which direction options exist (see the table above).

• Active or inactive, since only one active path can exist between two tables.

• Assume referential integrity, which only exists for DirectQuery relationships.

Relationships on columns with different data types, or on datetime columns with a time part, may not match the way you expect, whatever the direction. For more on cardinality and how it affects performance, see Power BI relationships and cardinality.

Cross filter direction is a model setting. It is different from the report-level filters and slicers covered in our guide to Power BI filters. For security filters, see Power BI row-level security.

FAQs

How does relationship cardinality affect cross filter direction in Power BI?

Cardinality decides which directions are available. One-to-many relationships can be Single or Both. One-to-one relationships are always Both. Many-to-many relationships can filter in either single direction or Both. Many-to-many relationships are also "limited" relationships, which Power BI evaluates at query time, so they are slower than one-to-many.

Can incorrect data types impact cross filter direction behavior?

Yes. A relationship only filters when values match. If one column is text and the other is a number, or one is a date and the other a datetime with a time part, values may not match. Filters then do not reach the rows you expect, whichever direction is set. Set both columns to the same type in Power Query.

What role does referential integrity play in cross filter direction?

None directly. Assume referential integrity is a separate property that only exists for relationships between DirectQuery tables from the same source. When it is on, Power BI sends an INNER JOIN instead of an OUTER JOIN to the source, which is usually faster. If some fact rows have no matching dimension row, those rows disappear from results, so only turn it on when the data is clean.

Sources

• Model relationships in Power BI Desktop - Microsoft Learn

• Bi-directional relationship guidance - Microsoft Learn

• CROSSFILTER function - Microsoft Learn