Skip to main content

How to Make a Relationship Filter in Both Directions in Power BI

· 6 min read
Jagdish Kumawat
Founder @ Dewiride

Filters in Power BI normally flow one way along a relationship: from the table with one row per value to the table with many. Sometimes they need to flow back, most often for row-level security, when the rule sits on the "many" table. Here's how to make a relationship filter in both directions, security filters included.

The example model, Expenses, has three tables (How to Create a Relationship Between Two Tables in Power BI):

  • Expense Categories: one row per category, in Title.
  • Expenses: the claims, related to Expense Categories by Category.
  • Category managers: one row per category and manager, with ManagerEmail, related to Expense Categories by Title.

A row-level security role keeps only the rows of Category managers whose ManagerEmail is the signed-in user (How to Add Row-Level Security Roles in Power BI in the Browser). For that to reach the expenses, the filter has to go from Category managers up to Expense Categories, against the relationship's direction, then down to Expenses.

Microsoft's row-level security article explains why a second setting is needed: By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional. Security filters cross a relationship backwards only when Apply security filter in both directions is ticked.

Step 1: Edit the Relationship​

Open the model, select Manage relationships on the Home tab, tick the relationship, here Category managers (Title) to Expense Categories (Title), and select Edit:

The Manage relationships dialog with Category managers (Title) to Expense Categories (Title) ticked and the Edit button outlined in red, and a second relationship, Expenses (Category) to Expense Categories (Title)

Step 2: Set Both and the Security Filter​

In Edit relationship:

  1. Cross-filter direction: change Single to Both.
  2. Tick Apply security filter in both directions, which becomes available once Both is chosen.

The Edit relationship dialog from Category managers, Title, to Expense Categories, Title, with Cardinality Many to one (*:1), Cross-filter direction Both, Make this relationship active ticked and Apply security filter in both directions ticked

Select Save.

Step 3: Check the Arrows​

In Manage relationships, the edited relationship's symbol now has arrows both ways, while Expenses (Category) keeps its single arrow:

The Manage relationships list with Category managers (Title) to Expense Categories (Title) now showing a double arrow, and Expenses (Category) to Expense Categories (Title) a single arrow, both Active

The model view shows the same double arrow on the line:

The model view with Expense Categories and Category managers joined by a line marked 1 and *, with arrows pointing both ways

Step 4: Test It​

Test the role as yourself (How to Test Row-Level Security in Power BI). In the example I manage only the Office category, and the report showed one expense, 35.00, with only Office in the category slicer:

The Test as role view of the Expenses Report, Now viewing as Category managers, with Total amount 35.00, Expense count 1, People 1, Signed in as admin@example.com, and a Category slicer listing only Office

The filter went from Category managers to Expense Categories, across the edited relationship, and on to Expenses.

Common Mistakes​

MistakeWhat happensDo this instead
Both without the security checkboxReport filters cross, but the security rule doesn'tTick Apply security filter in both directions
Both on every relationshipFilters can take unexpected pathsOnly where a filter must flow back
Expecting the checkbox on a one-to-one relationshipIt isn't offeredMake the relationship many to one (Fixed: Apply Security Filter in Both Directions Is Missing in Power BI)

Troubleshooting​

SymptomLikely causeFix
The role still shows every expenseThe security filter doesn't cross the relationshipTick the security checkbox
The checkbox is greyCross-filter direction is SingleChoose Both
The checkbox isn't there at allThe relationship is one to oneSet Many to one

FAQ​

Isn't Both enough on its own?​

Not for security. The article quoted above says security filters are single-directional by default, even on a bi-directional relationship.

Are there limits?​

The same article says that if a table takes part in several bi-directional relationships, you can select the option for only one of them.

Should I use Both for ordinary report filtering?​

Microsoft's bi-directional relationship guidance recommends using it only where it's needed.

Conclusion​

Edit the relationship, set Cross-filter direction to Both, and tick Apply security filter in both directions. The example's Category managers rule then reached Expenses: a manager of Office saw only Office's expense.

Additional Resources​