Bidirectional cross-filtering for DirectQuery

Often report creators and data modelers face challenges determining how to apply filters to a report, when filtering tables to create the appropriate view of data. Given, the bidirectional cross-filtering, report creators and data modelers have better control to apply filters when working with related tables. Moreover, cross-filtering enables them to apply filters on both sides of a table relationship. Hence, you can apply the filters by propagating the filter context to a second related table on the other side of a table relationship.

How to enable bidirectional cross-filtering for DirectQuery?

We can enable cross-filtering in the Edit relationship dialog box. In general, for enabling cross-filtering for a relationship, you must configure –

Firstly, Set Cross filter direction to Both.

Then, select Apply security filter in both directions.

Source: Microsoft

Remember, when you are creating cross-filtering DAX formulas in Power BI Desktop, use UserPrincipalName. Since, this field is often the same as a user’s login, for instead [email protected], instead of UserName. Also, you may be required to create a related table to map UserName or EmployeeID to UserPrincipalName.

Scenario: To learn how date table works together with bidirectional cross-filtering. One of the basic suggestions is to always use a one-way filter for the date table and not use bidirectional relationships at all for date tables.

For more visit: Bidirectional cross-filtering for DirectQuery in Power BI Desktop