Edit

Share via

Enable bidirectional cross-filtering for DirectQuery in Power BI Desktop

  • Article

When filtering tables to create the appropriate view of data, report creators and data modelers face challenges determining how to apply filters to a report. Previously, the table's filter context was held on one side of the relationship, but not the other. This arrangement often required a complex Data Analysis Expressions (DAX) formula to get the wanted results.

With bidirectional cross-filtering, report creators and data modelers now have more control over how they can apply filters when working with related tables. Bidirectional cross-filtering enables them to apply filters on both sides of a table relationship. You can apply the filters by propagating the filter context to a second related table on the other side of a table relationship.

Enable bidirectional cross-filtering for DirectQuery

You can enable cross-filtering in the Edit relationship dialog box. To enable cross-filtering for a relationship, you must configure the following options:

  • Set Cross filter direction to Both.

  • Select Apply security filter in both directions.

    Screenshot of the Edit relationship dialog box with Cross filter direction set to both and the checkbox to Apply security filter in both directions selected.

Note

When creating cross filtering DAX formulas in Power BI Desktop, use UserPrincipalName. This field is often the same as a user's login, for example joe@contoso.com, instead of UserName. As such, you might need to create a related table that maps UserName or EmployeeID to UserPrincipalName.

For more information and for examples of how bidirectional cross-filtering works, see the Bidirectional cross-filtering for Power BI Desktop whitepaper.