What Is Cross Filter Direction in Power BI?
Cross filter direction in Power BI controls which way filters travel along a relationship between two tables. Set to Single, filters flow from the ‘one’ side (dimension) to the ‘many’ side (fact) only. Set to Both, filters flow in both directions — useful in specific scenarios but carrying performance and ambiguity risks if overused.
Single vs. Both Cross Filter Direction
- Single (default and recommended): Filters propagate from dimension to fact only. When a user selects ‘Electronics’ in a Product slicer, the Sales table is filtered but the Product table is not filtered by Sales. Standard star schema behavior.
- Both (bi-directional): Filters flow in both directions. Selecting a value in the fact table can also filter the dimension. Useful for many-to-many relationships and scenarios where a slicer should show only relevant dimension values.
When to Use Bi-Directional Cross Filtering
- Many-to-many relationships: Direct many-to-many often requires Both for correct filter propagation.
- Showing only relevant dimension values: If a Product slicer should hide products with zero sales, bi-directional filtering achieves this.
- Multiple fact tables sharing a dimension: Selections in one fact table can filter the shared dimension and, through it, the other fact table.
Risks of Bi-Directional Filtering
- Performance: More filter paths to evaluate — can slow queries on large models.
- Ambiguity: Multiple bi-directional relationships can create circular filter paths.
- DAX alternative: CROSSFILTER() can temporarily change filter direction for a specific measure, avoiding permanent Both.
How to Set and Use Cross Filter Direction in Power BI
This tutorial shows how to configure cross filter direction, when to use Single vs. Both, and how to use CROSSFILTER() in DAX. About 4 steps.
Prerequisites
- Power BI Desktop with at least two related tables
- An existing relationship (e.g., Products to Sales)
Step-by-Step
Step 1 — Open relationship properties. In Model View, double-click the relationship line.
Step 2 — Check the current direction. Look at the Cross filter direction dropdown. Defaults to Single for one-to-many.
[Screenshot: Edit Relationship dialog with Cross filter direction dropdown highlighted]
Step 3 — Change to Both (if needed). Select Both and click OK. Test a slicer — it will now show only products with matching Sales rows.
Step 4 — Use CROSSFILTER() in DAX instead. Keep the relationship as Single and write: Products With Sales = CALCULATE(DISTINCTCOUNT(Products[ProductName]), CROSSFILTER(Sales[ProductID], Products[ProductID], Both))
