Skip to content

Latest commit

 

History

History
32 lines (24 loc) · 2.41 KB

desktop-bidirectional-filtering.md

File metadata and controls

32 lines (24 loc) · 2.41 KB
Raw
title description author ms.author ms.reviewer ms.custom ms.service ms.subservice ms.topic ms.date LocalizationGroup
Bidirectional cross-filtering in Power BI Desktop
Learn how to enable bidirectional cross-filtering for your data by using DirectQuery in Power BI Desktop.
davidiseminger
davidi
powerbi
pbi-transform-model
conceptual
02/08/2023
Connect to data

Enable bidirectional cross-filtering for DirectQuery in Power BI Desktop

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.

    :::image type="content" source="media/desktop-bidirectional-filtering/bidirectional-filtering_2.png" alt-text="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.