在 Power BI Desktop 中使用 DirectQuery 雙向交叉篩選Bidirectional cross-filtering using DirectQuery in Power BI Desktop

在篩選資料表以建立適當的資料檢視時,報表建立者 (和製造資料模型者) 在決定如何將篩選套用至報表上也面臨挑戰。資料表的篩選內容為關聯性的其中一端所持有,而其他相關聯的項目則不具有其內容,通常需要複雜的 DAX 公式才能取得所需的結果。When filtering tables to create the appropriate view of data, report creators (and data modelers) face challenges when determining how filtering is applied to a report; the filter context of a table was held on one side of the relationship, but not the other, often requiring complex DAX formulas to get the desired results.

有了雙向交叉篩選,報表建立者 (和製造資料模型者) 現在就可在使用相關資料表時,對篩選的套用方式具備更充分的掌控,讓這些篩選得以套用至資料表關聯性的 兩端With bidirectional cross-filtering, report creators (and data modelers) now have more control over how filters are applied when working with related tables, enabling those filters to be applied on both sides of a table relationship. 這可以藉由將篩選內容傳播至資料表關聯性另一端的第二個關聯資料表來完成。This is accomplished by having the filter context propagated to a second related table on the other side of a table relationship.

有份詳細的技術白皮書可供使用,其解釋了 Power BI Desktop 中的雙向交叉篩選 (該技術白皮書另涵蓋 SQL Server Analysis Services 2016,此兩者都有相同的行為)。A detailed whitepaper is available that explains bidirectional cross-filtering in Power BI Desktop (the whitepaper also covers SQL Server Analysis Services 2016, both have the same behavior).

啟用 DirectQuery 的雙向交叉篩選Enabling bidirectional cross-filtering for DirectQuery

若要啟用交叉篩選,在關聯性的 [編輯關聯性] 對話方塊中,必須選取下列項目︰To enable cross-filtering, in the Edit Relationship dialog for a relationship, the following must be selected:

  • [交互篩選方向] 必須設為 [兩者]The Cross filter direction must be set to Both
  • [雙向套用安全性篩選] 也必須選取The Apply security filter in both directions must also be selected

注意

在建立 Power BI Desktop 中的交叉篩選 DAX 公式時,使用 UserPrincipalName (這通常和使用者登入名稱相同,例如 joe@contoso.com) 而不是 UserName。When creating cross filtering DAX formulas in Power BI Desktop, use UserPrincipalName (which is often the same as a user's login, such as joe@contoso.com) instead of UserName. 因此,您可能需要建立將 UserName (或例如 EmployeeID) 對應至 UserPrincipleName 的相關資料表。As such, you may need to create a related table that maps UserName (or EmployeeID, for example) to UserPrincipleName.

如需詳細資訊,以及雙向交叉篩選如何運作的範例,請參閱本文稍早所提到的技術白皮書For more information, and for examples of how bidirectional cross-filtering works, check out the whitepaper mentioned earlier in this article.