Cross Filter Direction in Power BI: What It Is and How It Works

In Power BI, cross filter direction controls how filters flow between related tables in a data model. It can be set to Single (one-way filtering) or Both (two-way filtering), affecting how visuals and calculations respond to data changes across tables.

How It Works

Imagine you have two tables: Sales and Products. Cross filter direction decides if filtering one table affects the other. With Single direction, filtering the Products table filters Sales, but not the other way around. It's like a one-way street where filters travel in one direction only.

With Both direction, filters flow both ways. Filtering Sales can also filter Products. This is like a two-way street where filters can travel back and forth, making your reports more interactive but sometimes more complex.

This setting helps Power BI know how to apply filters when you create visuals or write formulas, ensuring your data behaves as expected.

Example

This example shows how to set cross filter direction in Power BI using DAX and the data model interface.

DAX

/* In Power BI Desktop, you set cross filter direction in the Model view:
1. Go to Model view.
2. Click the relationship line between two tables.
3. In the properties pane, find 'Cross filter direction'.
4. Choose 'Single' or 'Both'.

-- Example DAX measure to count filtered products --
FilteredProductCount = CALCULATE(COUNTROWS(Products))

-- This measure respects the cross filter direction set in the model --

Output

If cross filter direction is Single (Products to Sales), filtering Sales does NOT affect Products, so FilteredProductCount shows all products.
If cross filter direction is Both, filtering Sales filters Products too, so FilteredProductCount changes accordingly.

When to Use

Use Single cross filter direction when your data flows naturally in one direction, like from Products to Sales. This keeps your model simple and efficient.

Use Both when you want filters to work both ways, such as when you want to filter products based on sales data or vice versa. This is useful in complex reports where interactivity between tables is needed.

Be careful with Both because it can create circular filter paths and slow down your report if overused.

Key Points

• Single direction means filters flow one way only.
• Both direction allows filters to flow both ways between tables.
• Cross filter direction affects how visuals and calculations respond to filters.
• Use Both carefully to avoid performance issues.
• Set cross filter direction in the Model view in Power BI Desktop.

Key Takeaways

Cross filter direction controls how filters flow between related tables in Power BI.

Single direction filters one way; Both direction filters both ways.

Setting it correctly ensures your reports filter data as expected.

Use Both direction only when necessary to avoid complexity and slowdowns.

You set cross filter direction in the Model view by editing relationships.