在 Power BI Desktop 中建立和管理關聯性Create and manage relationships in Power BI Desktop

當您匯入多個資料表時,您可能想要使用所有資料表中的資料來進行一些分析。When you import multiple tables, chances are you’re going to do some analysis using data from all those tables. 這些資料表之間必須有關聯性,才能正確地計算結果,並在報表中顯示正確資訊。Relationships between those tables are necessary in order to accurately calculate results and display the correct information in your reports. Power BI Desktop 可讓您輕鬆地建立這些關聯性。Power BI Desktop makes creating those relationships easy. 事實上,在大多數情況下,您不必執行任何動作,[自動偵測] 功能會為您完成這項作業。In-fact, in most cases you won’t have to do anything, the Autodetect feature can do it for you. 不過,在某些情況下,您可能必須自行建立關聯性,或可能需要對關聯性進行一些變更。However, in some cases you might have to create relationships yourself, or you might need to make some changes to a relationship. 不論是哪種情況,請務必了解 Power BI Desktop 中的關聯性,以及如何建立和編輯關聯性。Either way, it’s important to understand relationships in Power BI Desktop and how to create and edit them.

在載入期間自動偵測Autodetect during load

如果您同時查詢兩個或多個資料表,Power BI Desktop 會在載入資料時,嘗試為您尋找並建立關聯性。If you query two or more tables at the same time, when the data is loaded, Power BI Desktop will attempt to find and create relationships for you. 系統會自動設定基數、交叉篩選方向和作用中屬性。Cardinality, Cross filter direction, and Active properties are automatically set. Power BI Desktop 會查看您要查詢之資料表中的資料行名稱,以判斷是否有任何可能的關聯性。Power BI Desktop looks at column names in the tables you are querying to determine if there are any potential relationships. 如果有,則會自動建立這些關聯性。If there are, those relationships are created automatically. 如果 Power BI Desktop 不太確定是否有相符項目,則不會自動建立關聯性。If Power BI Desktop cannot determine with a high-level of confidence there is a match, it will not automatically create the relationship. 您仍然可以使用 [管理關聯性] 對話方塊來建立或編輯關聯性。You can still use the Manage Relationships dialog to create or edit relationships.

使用自動偵測建立關聯性Create a relationship by using Autodetect

常用 索引標籤上,按一下 管理關聯性>自動偵測On the Home tab, click Manage Relationships > AutoDetect.

以手動方式建立關聯性Create a relationship manually

  1. 常用 索引標籤上,按一下 管理關聯性> 新增On the Home tab, click Manage Relationships > New.
  2. 在 [建立關聯性] 對話方塊中,選取第一個資料表下拉式清單中的資料表,然後選取您要用於關聯性的資料行。In the Create Relationship dialog, in the first table drop-down list, select a table, and then select the column you want to use in the relationship.
  3. 在第二個資料表下拉式清單中,選取您要用於關聯性的其他資料表,然後選取您要使用的其他資料行,再按一下 [確定] 。In the to second table drop-down list, select the other table you want in the relationship, then select the other column you want to use, and then click OK.

根據預設,Power BI Desktop 會自動為您的新關聯性設定基數 (方向)、交叉篩選方向和作用中屬性;不過,您可以視需要在 [進階選項] 中變更這些項目。By default, Power BI Desktop will automatically configure the Cardinality (direction), Cross filter direction, and Active properties for your new relationship; however, you can change these if necessary in Advanced options. 如需詳細資訊,請參閱本文稍後的<了解進階選項>一節。To learn more, see the Understanding advanced options section later in this article.

編輯關聯性Edit a relationship

  1. 在 [常用] 索引標籤上,按一下 [管理關聯性] 。On the Home tab, click Manage Relationships.
  2. 在 [管理關聯性] 對話方塊中,選取關聯性,然後按一下 [編輯] 。In the Manage Relationships dialog, select the relationship, then click Edit.

設定進階選項Configure advanced options

當您建立或編輯關聯性時,您可以設定進階選項。When you create or edit a relationship, you can configure advanced options. 預設會根據最佳猜測,自動設定進階選項。By default, advanced options are automatically configured based on a best guess. 根據資料行中的資料,每個關聯性可能會有不同的選項。This can be different for each relationship based on the data in the columns.

基數Cardinality

多對一 (*:1)- 這是最常見的預設類型。Many to One (*:1) - This is the most common, default type. 這表示一個資料表中的資料行可以有特定值的多個執行個體,而其他相關資料表 (通常稱為查閱資料表) 只能有特定值的一個執行個體。This means the column in one table can have more than one instance of a value, and the other related table, often know as the Lookup table, has only one instance of a value.

一對一 (1:1) - 這表示一個資料表中的資料行只能有特定值的一個執行個體,而其他相關資料表只能有特定值的一個執行個體。One to One (1:1) - This means the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.

如需何時變更基數的詳細資訊,請參閱本文稍後的<了解進階選項>一節。See the Understanding advanced options section later in this article for more details about when to change cardinality.

交叉篩選方向Cross filter direction

雙向 - 這是最常見的預設方向。Both - This is the most common, default direction. 這表示用於篩選時,兩個資料表會視為一個資料表。This means for filtering purposes, both tables are treated as if they're a single table. 這適用於周圍有一些查閱資料表的單一資料表。This works well with a single table that has a number of lookup tables that surround it. 例如,以下是具有部門之查閱資料表的銷售實際值資料表。An example is a Sales actuals table with a lookup table for department. 這通常稱為星型結構描述組態 (一個具有多個查閱資料表的中央資料表)。不過,如果您有兩個或多個資料表也有查閱資料表 (並共用一些資料表),則不會想要使用 [雙向] 設定。This is often called a Star schema configuration (a central table with several Lookup tables.) However, if you have two or more tables that also have lookup tables (with some in common) then you wouldn't want to use the Both setting. 延續上一個範例,在這種情況下,您也會有一個預算銷售資料表,來記錄每個部門的目標預算。To continue the previous example, in this case, you also have a budget sales table that records target budget for each department. 而部門資料表會同時連接到銷售與預算資料表。And, the department table is connected to both the sales and the budget table. 請避免針對這種組態使用 [雙向] 設定。Avoid the Both setting for this kind of configuration.

單向 - 這表示已連接的資料表中的篩選選項會套用至彙總值的所在資料表。Single - This means that filtering choices in connected tables work on the table where values are being aggregated. 如果您匯入 Excel 2013 或更早版本的 Power Pivot 資料模型,所有關聯性都會是單向。If you import a Power Pivot in Excel 2013 or earlier data model, all relationships will have a single direction.

如需何時變更交叉篩選方向的詳細資訊,請參閱本文稍後的<了解進階選項>一節。See the Understanding advanced options section later in this article for more details about when to change cross filter direction.

將這個關聯性設為作用中Make this relationship active

核取時,表示這個關聯性會做為作用中的預設關聯性。When checked, this means the relationship serves as the active, default relationship. 如果兩個資料表之間有多個關聯性,作用中的關聯性可讓 Power BI Desktop 自動建立包含兩個資料表的視覺效果。In cases where there are more than one relationship between two tables, the active relationship provides a way for Power BI Desktop to automatically create visualizations that include both tables.

如需何時將特定關聯性設為作用中的詳細資訊,請參閱本文稍後的<了解進階選項>一節。See the Understanding advanced options section later in this article for more details about when to make a particular relationship active.

了解關聯性Understanding relationships

當您透過關聯性將兩個資料表連接在一起之後,您可以使用這兩個資料表中的資料,就像是單一資料表一樣;您不需要擔心關聯性細節,也不需要先將這些資料表簡維成單一資料表,再進行匯入。Once you have connected two tables together with a relationship, you can work with the data in both tables as if they were a single table, freeing you from having to worry about relationship details, or flattening those tables into a single table before importing them. 在許多情況下,Power BI Desktop 可自動為您建立關聯性,因此可能甚至不需要您自行建立這些關聯性。In many situations, Power BI Desktop can automatically create relationships for you, so creating those relationships yourself might not even be needed. 不過,如果 Power BI Desktop 不太確定兩個資料表之間是否應該有關聯性,則不會自動建立關聯性。However, if Power BI Desktop can’t determine with a high-degree of certainty that a relationship between two tables should exist, it will not automatically create the relationship . 在該情況下,您必須建立關聯性。In that case, you will need to create the relationship.

以下將進行一些教學課程,讓您更了解關聯性在 Power BI Desktop 中的運作方式。Let’s do a little tutorial, to better show you how relationships work in Power BI Desktop.

提示

您可以自行完成這個課程。You can complete this lesson yourself. 將下面的 [專案時數] 資料表複製到 Excel 工作表,選取所有資料格,然後按一下 插入>資料表Copy the ProjectHours table below into an Excel worksheet, select all of the cells, click INSERT > Table. 在 [建立資料表] 對話方塊中,直接按一下 [確定] 。In the Create Table dialog, just click OK. 然後在 [資料表名稱] 中輸入「專案時數」 。Then in Table Name, type ProjectHours. 對 [公司專案] 資料表執行相同的動作。Do the same for the CompanyProject table. 您可以接著使用 Power BI Desktop 中的 [取得資料] 匯入資料。You can then import the data by using Get Data in Power BI Desktop. 選取您的活頁簿和資料表做為資料來源。Select your workbook and tables as a data source.

第一個資料表 [專案時數] 是工作票證記錄,記錄特定人員在特定專案上工作的時數。This first table, ProjectHours, is a record of work tickets that record the number of hours a person has worked on a particular project.

專案時數ProjectHours

票證Ticket 提交者SubmittedBy 時數Hours 專案Project 提交日期DateSubmit
10011001 Brewer, AlanBrewer, Alan 2222 藍色Blue 1/1/20131/1/2013
10021002 Brewer, AlanBrewer, Alan 2626 紅色Red 2/1/20132/1/2013
10031003 Ito, ShuIto, Shu 3434 黃色Yellow 12/4/201212/4/2012
10041004 Brewer, AlanBrewer, Alan 1313 橙色Orange 1/2/20121/2/2012
10051005 Bowen, EliBowen, Eli 2929 紫色Purple 10/1/201310/1/2013
10061006 Bento, NunoBento, Nuno 3535 綠色Green 2/1/20132/1/2013
10071007 Hamilton, DavidHamilton, David 1010 黃色Yellow 10/1/201310/1/2013
10081008 Han, MuHan, Mu 2828 橙色Orange 1/2/20121/2/2012
10091009 Ito, ShuIto, Shu 2222 紫色Purple 2/1/20132/1/2013
10101010 Bowen, EliBowen, Eli 2828 綠色Green 10/1/201310/1/2013
10111011 Bowen, EliBowen, Eli 99 藍色Blue 10/15/201310/15/2013

第二個資料表 [公司專案] 是指派優先順序 A、 B 或 C 的專案清單。This second table, CompanyProject, is a list of projects with an assigned priority, A, B, or C.

公司專案CompanyProject

專案名稱ProjName 優先順序Priority
藍色Blue AA
紅色Red BB
綠色Green CC
黃色Yellow CC
紫色Purple BB
橙色Orange CC

請注意,每個資料表都有一個專案資料行。Notice that each table has a project column. 每個專案的命名方式稍有不同,但值看起來像是相同的專案。Each are named slightly different, but the values look like they’re the same. 這很重要,稍後我們將回頭說明。That’s important, and we’ll get back to it in a little bit.

將兩個資料表匯入模型之後,讓我們來建立報表。Now that we have our two tables imported into a model, let’s create a report. 我們想要取得的第一個項目是依專案優先順序提交的時數,因此我們從 [欄位] 選取 [優先順序] 和 [時數] 。The first thing we want to get is the number of hours submitted by project priority, so we select Priority and Hours from Fields.

如果看一下報表畫布中的資料表,您會看到每個專案的時數為 256.00 ,這也是總計。If we look at our table in the Report canvas, you’ll see the number of hours is 256.00 for each project, and it’s also the total. 顯然不正確。Clearly this isn’t correct. 為什麼?Why? 這是因為如果兩個資料表之間沒有關聯性,則無法計算其中一個資料表中的值 ([專案] 資料表中的 [時數]),以另一個資料表中的值 ([公司專案] 資料表中的 [優先順序]) 交叉篩選後的總和。It’s because we can’t calculate a sum total of values from one table (Hours in the Project table), sliced by values in another table (Priority in the CompanyProject table) without a relationship between these two tables.

接下來,讓我們來建立這兩個資料表之間的關聯性。So, let’s create a relationship between these two tables.

還記得我們在這兩個資料表中看到具有專案名稱且其值類似的資料行嗎?Remember those columns we saw in both tables with a project name, but with values that look alike? 我們將使用這兩個資料行來建立資料表之間的關聯性。We’re going to use these two columns to create a relationship between our tables.

為什麼選擇這兩個資料行?Why these columns? 如果看一下 [專案時數] 資料表中的 [專案] 資料行,我們會看到 [藍色]、[紅色]、[黃色]、[橙色] 等值。Well, if we look at the Project column in the ProjectHours table, we see values like Blue, Red, Yellow, Orange, and so on. 事實上,我們看到多個資料列具有相同值。In fact, we see several rows that have the same value. 我們實際上會對專案套用許多色彩值。In-effect, we have many color values for Project.

如果看一下 [公司專案] 資料表中的 [專案名稱] 資料行,我們只會看到專案的唯一色彩值。If we look at the ProjName column in the CompanyProject table, we see there’s only one of each of the color values for project. 這個資料表中的每個色彩值都是唯一的,這很重要,因為如此便可建立兩個資料表之間的關聯性。Each color value in this table is unique, and that’s important, because we can create a relationship between these two tables. 在這種情況下,這是多對一關聯性。In this case, a many-to-one relationship. 在多對一關聯性中,其中一個資料表中的至少一個資料行必須包含唯一值。In a many-to-one relationship, at least one column in one of the tables must contain unique values. 某些關聯性有一些進階選項,稍後我們將進行討論,但現在讓我們來建立兩個資料表的 [專案] 資料行之間的關聯性。There are some advanced options for some relationships, and we’ll look at those later, but for now, let’s create a relationship between the Project columns in each of our two tables.

建立新的關聯性To create the new relationship

  1. 按一下 [管理關聯性] 。Click Manage Relationships.
  2. 在 [管理關聯性] 中,按一下 [新增] 。In Manage Relationships, click New. 這會開啟 [建立關聯性] 對話方塊,我們可以從中選取資料表、資料行,以及關聯性所需的任何進階設定。This opens the Create Relationship dialog, where we can select the tables, columns, and any advanced settings we want for our relationship.
  3. 在第一個資料表中,選取 [專案時數] ,然後選取 [專案] 資料行。In the first table, select ProjectHours, then select the Project column. 這是關聯性中代表「多」的一側。This is the many side of our relationship.
  4. 在第二個資料表中,選取 [公司專案] ,然後選取 [專案名稱] 資料行。In the second table, select CompanyProject, then select the ProjName column. 這是關聯性中代表「一」的一側。This is the one side of our relationship.
  5. 在 [建立關聯性] 對話方塊和 [管理關聯性] 對話方塊中,直接按一下 [確定] 。Go ahead and click OK in both the Create Relationship dialog and the Manage Relationships dialog.

實際上,這種建立關聯性的做法比較困難。In the interest of full disclosure, you really just created this relationship the hard way. 您可以直接按一下 [管理關聯性] 對話方塊中的 [自動偵測] 按鈕。You could've just clicked on the Autodetect button in the Manage Relationships dialog. 事實上,如果兩個資料行有相同的名稱,在載入資料時,[自動偵測] 就已經為您完成這項作業。In-fact, Autodetect would have already done it for you when you loaded the data if both columns had the same name. 但這樣做哪裡還有挑戰?But, what’s the challenge in that?

現在,讓我們再看一下報表畫布中的資料表。Now, let’s look at the table in our Report canvas again.

現在看起來比較好,不是嗎?Now that looks a whole lot better, doesn’t it?

當我們依優先順序加總時數時,Power BI Desktop 會尋找 [公司專案] 查閱資料表中唯一色彩值的每個執行個體,然後在 [公司專案] 資料表中尋找這些值的每個執行個體,並計算每個唯一值的總和。When we sum up hours by Priority, Power BI Desktop will look for every instance of the unique color values in the CompanyProject lookup table, and then look for every instance of each of those values in the CompanyProject table, and calculate a sum total for each unique value.

這很簡單,事實上透過 [自動偵測],您可能甚至不需要執行這麼多動作。That was pretty easy, in-fact, with Autodetect, you might not even have to do this much.

了解進階選項Understanding advanced options

建立關聯性時,不論是透過 [自動偵測] 或以手動方式建立,Power BI Desktop 都會根據您資料表中的資料來自動設定進階選項。When a relationship is created, either with Autodetect or one you create manually, Power BI Desktop will automatically configure advanced options based on the data in your tables. 您可以展開 [建立/編輯關聯性] 對話方塊中的 [進階選項],以設定進階關聯性屬性。You can configure advanced relationship properties by expanding Advanced options in the Create/Edit relationship dialog.

如前所述,這些通常會自動設定,不勞您費心;不過在幾種情況下,您可能會想要自行設定進階選項。As we said, these are usually set automatically and you won’t need to mess with them; however, there are several situations where you might want to configure advanced options yourself.

資料的未來更新需要不同的基數Future updates to the data require a different cardinality

一般而言,Power BI Desktop 可自動為關聯性決定最佳基數。Normally, Power BI Desktop can automatically determine the best cardinality for the relationship. 如果您知道資料在未來會變更,因此需要覆寫自動設定,則可以在 [基數] 控制項中加以選取。If you do need to override the automatic setting, because you know the data will change in the future, you can select it in the Cardinality control. 以下是需要選取不同基數的範例。Let’s look at an example where we need to select a different cardinality.

下面的 [公司專案優先順序] 資料表列出所有公司專案及其優先順序。The CompanyProjectPriority table below is a list of all company projects and their priority. [專案預算] 資料表顯示已核准預算的一組專案。The ProjectBudget table is the set of projects for which budget has been approved.

專案預算ProjectBudget

核准的專案Approved Projects BudgetAllocationBudgetAllocation AllocationDateAllocationDate
藍色Blue 40,00040,000 12/1/201212/1/2012
紅色Red 100,000100,000 12/1/201212/1/2012
綠色Green 50,00050,000 12/1/201212/1/2012

公司專案優先順序CompanyProjectPriority

專案Project 優先順序Priority
藍色Blue AA
紅色Red BB
綠色Green CC
黃色Yellow CC
紫色Purple BB
橙色Orange CC

如果在 [公司專案優先順序] 資料表中的 [專案] 資料行與 [專案預算] 資料表中的 [已核准的專案] 資料行之間,建立如下所示的關聯性:If we create a relationship between the Project column in the CompanyProjectPriority table and ApprovedProjects column in the ProjectBudget table, like this:

[基數] 會自動設定為 [一對一 (1:1)],而交叉篩選會設定為 [雙向] (如圖所示)。Cardinality is automatically set to One-to-One (1:1), and cross filtering to be Both (as shown). 這是因為對於 Power BI Desktop 而言,這兩個資料表的最佳組合實際上會如下所示:This is because to Power BI Desktop, the best combination of the two tables really looks like this:

專案Project 優先順序Priority BudgetAllocationBudgetAllocation AllocationDateAllocationDate
藍色Blue AA 40,00040,000 12/1/201212/1/2012
紅色Red BB 100,000100,000 12/1/201212/1/2012
綠色Green CC 50,00050,000 12/1/201212/1/2012
黃色Yellow CC

紫色Purple BB

橙色Orange CC

由於合併資料表的 [專案] 資料行沒有重複的值,因此兩個資料表之間有一對一關聯性。There is a one-to-one relationship between our two tables because there are no repeating values in the combined table’s Project column. 因為每個值只會出現一次,所以 [專案] 資料行是唯一的;因此,您可以直接合併兩個資料表中的資料列,而不會有任何重複項目。The Project column is unique, because each value occurs only once, so, the rows from the two tables can be combined directly without any duplication.

但假設您知道資料將會在下次重新整理時變更。But, let’s say you know the data will change the next time you refresh it. 重新整理後的 [專案預算] 資料表版本現在會有額外的資料列 [藍色] 和 [紅色]:A refreshed version of the ProjectBudget table now has additional rows for Blue and Red:

專案預算ProjectBudget

核准的專案Approved Projects BudgetAllocationBudgetAllocation AllocationDateAllocationDate
藍色Blue 40,00040,000 12/1/201212/1/2012
紅色Red 100,000100,000 12/1/201212/1/2012
綠色Green 50,00050,000 12/1/201212/1/2012
藍色Blue 80,00080,000 6/1/20136/1/2013
紅色Red 90,00090,000 6/1/20136/1/2013

這表示這兩個資料表的最佳組合現在實際上會如下所示:This means the best combination of the two tables now really looks like this:

專案Project 優先順序Priority BudgetAllocationBudgetAllocation AllocationDateAllocationDate
藍色Blue AA 40,00040,000 12/1/201212/1/2012
紅色Red BB 100,000100,000 12/1/201212/1/2012
綠色Green CC 50,00050,000 12/1/201212/1/2012
黃色Yellow CC

紫色Purple BB

橙色Orange CC

藍色Blue AA 8000080000 6/1/20136/1/2013
紅色Red BB 9000090000 6/1/20136/1/2013

在這個新的合併資料表中,[專案] 資料行有重複的值。In this new combined table, the Project column has repeating values. 重新整理資料表之後,兩個原始資料表將不會有一對一關聯性。The two original tables won’t have a one-to-one relationship once the table is refreshed. 在這種情況下,由於我們知道這些未來更新會導致 [專案] 資料行有重複項目,因此我們想要將 [基數] 設定為 [多對一 (*:1)],其中 [專案預算] 具有多個,而 [公司專案] 則有一個。In this case, because we know those future updates will cause the Project column to have duplicates, we want to set the Cardinality to be Many-to-One (*:1), with the Many on the ProjectBudget side and the One on the CompanyProject side.

調整一組複雜之資料表關聯性的交叉篩選方向Adjusting cross filter direction for a complex set of tables of relationships

大多數關聯性的交叉篩選方向會設定為 [雙向]。For most relationships, the cross filter direction is set to ‘Both’. 不過,在某些較罕見的情況下,您可能需要將此設定為非預設值,例如從舊版 Power Pivot 匯入模型時,每個關聯性會設定為單向。There are, however, some more uncommon circumstances where you might need to set this different from the default, like if you’re importing a model from an older version of Power Pivot, where every relationship is set to a single direction.

[雙向] 設定可讓 Power BI Desktop 將已連接的資料表整個視為單一資料表。The Both setting enables Power BI Desktop to treat all aspects of connected tables as if they are a single table. 不過,在某些情況下,Power BI Desktop 無法將關聯性的交叉篩選方向設定為 [雙向],而且也會保留一組明確的預設值以供報告使用。There are some situations, however, where Power BI Desktop cannot set a relationship’s cross filter direction to ‘Both’ and also keep an unambiguous set of defaults available for reporting purposes. 如果關聯性的交叉篩選方向未設定為 [雙向],通常是因為這會造成模稜兩可的情況。If a relationship cross filter direction isn't set to Both, then it’s usually because it would create ambiguity. 如果預設交叉篩選設定不適合您,請嘗試設定為特定資料表或 [雙向]。If the default cross filter setting isn’t working for you, try setting it to a particular table or Both.

單向交叉篩選適用於許多情況。Single direction cross filtering works for many situations. 事實上,如果您已從 Excel 2013 或更早版本的 Power Pivot 匯入模型,所有關聯性都會設定為單向。In fact, if you’ve imported a model from Power Pivot in Excel 2013 or earlier, all of the relationships will be set to single direction. 單向表示已連接的資料表中的篩選選項會套用至執行彙總工作的所在資料表。Single direction means that filtering choices in connected tables work on the table where aggregation work is happening. 有時候,了解交叉篩選可能有點困難,因此以下舉例說明。Sometimes, understanding cross filtering can be a little difficult, so let’s look at an example.

透過單向交叉篩選,如果您建立彙總專案時數的報表,則可以選擇依 [公司專案] 中的 [優先順序] 或 [公司員工] 中的 [城市] 來進行彙總 (或篩選)。With single direction cross filtering, if you create a report that summarizes the project hours and then you can choose to summarize (or filter) by CompanyProject, Priority or CompanyEmployee, City. 不過,如果您想要計算每個專案的員工數目 (較不常見的問題),這並不適用。If however, you want to count the number of employee per projects (a less common question), it won’t work. 您會得到一個其值完全相同的資料行。You’ll get a column of values that are all the same. 在下列範例中,兩個關聯性的交叉篩選方向設定為單向,也就是朝向 [專案時數] 資料表:In the example below, both relationships cross filtering direction is set to a single direction – towards the ProjectHours table:

篩選規格會從 [公司專案] 流向 [公司員工] (如下圖所示),但不會向上流至 [公司員工]。Filter specification will flow from CompanyProject to CompanyEmployee (as shown in the image below) but, it won’t flow up to CompanyEmployee. 不過,如果您將交叉篩選方向設定為 [雙向],則可運作。However, if you set the cross filtering direction to Both it will work. [雙向] 設定可讓篩選規格向上流至 [員工]。The Both setting allows the filter specification to flow up to Employee.

當交叉篩選方向設定為 [雙向] 時,報表現在看起來正確無誤:With the cross filtering direction set to Both, our report now appears correct:

雙向交叉篩選適用於類似上述模式的資料表關聯性模式。Cross filtering both directions works well for a pattern of table relationships that look like the pattern above. 這最常稱為星型結構描述,如下所示:This is most commonly called a star schema, like this:

交叉篩選方向不適用於資料庫中經常出現的較泛型模式,如下圖所示:Cross filtering direction does not work well with a more general pattern often found in databases, like in this diagram:

如果您有類似如下具有迴圈的資料表模式,則交叉篩選可能會建立一組模稜兩可的關聯性。If you have a table pattern like this, with loops, then cross filtering can create an ambiguous set of relationships. 例如,如果您加總資料表 X 中的欄位,然後選擇依資料表 Y 中的欄位進行篩選,則不確定篩選會經由頂端資料表,還是底部資料表。For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table. 這種模式的一個常見範例是資料表 X 為具有實際值資料的銷售資料表,而資料表 Y 包含預算資料。A common example for this kind of pattern is TableX to be a Sales table with actuals data and for TableY to be budget data. 而中間的資料表則是這兩個資料表所使用的查閱資料表,例如 [部門] 或 [地區]。Then, the tables in the middle are lookup tables that both tables use, such as Division or Region.

就像是作用中/非作用中的關聯性,如果會在報表中造成模稜兩可的情況,則 Power BI Desktop 不允許將關聯性設定為 [雙向]。Just like with active/inactive relationships, Power BI Desktop won’t allow a relationship to be set as Both if it will create ambiguity in reports. 您有幾種不同的方法可解決這個問題,以下是最常見的兩個方法:There are several different ways you can deal with this, here are the two most common:

  • 刪除關聯性或將關聯性標示為非作用中,可減輕模稜兩可的情況。Delete or mark relationships as inactive to reduce ambiguity. 然後您可以將關聯性的交叉篩選設定為 [雙向]。Then you might be able to set a relationship cross filtering as Both.
  • 將資料表帶入兩次 (第二次使用不同的名稱) 以刪除迴圈。Bring in a table twice (with a different name the second time) to eliminate loops. 這會使關聯性的模式類似星型結構描述。This makes the pattern of relationships like a star schema. 在星型結構描述中,所有關聯性都可以設定為 [雙向]。With a star schema all of the relationships can be set to Both.

作用中的關聯性不正確Wrong active relationship

當 Power BI Desktop 自動建立關聯性時,有時會遇到兩個資料表之間有多個關聯性的情況。When Power BI Desktop automatically creates relationships, it sometimes encounters more than one relationship between two tables. 當發生這種情況時,只有其中一個關聯性會設為作用中。When this happens only one of the relationships is set to be active. 作用中的關聯性會做為預設關聯性,以便您從兩個不同的資料表選擇欄位時,Power BI Desktop 可為您自動建立視覺效果。The active relationship serves as the default relationship so that when you choose fields from two different tables, Power BI Desktop can automatically create a visualization for you. 不過,在某些情況下,自動選取的關聯性可能不正確。However, in some cases the automatically selected relationship can be wrong. 您可以使用 [管理關聯性] 對話方塊,將關聯性設定為作用中或非作用中,也可以在 [編輯關聯性] 對話方塊中,設定作用中的關聯性。You can use the Manage Relationships dialog to set a relationship as active or inactive, or you can set the active relationship in the Edit relationship dialog.

為了確保有預設關聯性,Power BI Desktop 在指定時間,僅允許兩個資料表之間有一個作用中的關聯性。To ensure there’s a default relationship, Power BI Desktop only allows a single active relationship between two tables at a given time. 因此,您必須先將目前的關聯性設定為非作用中,再設定作用中的關聯性。So, you must first set the current relationship as inactive and then set the relationship you want to be active.

以下舉例說明。Let’s look at an example. 第一個資料表是 [專案票證],下一個資料表是 [員工角色]。This first table is ProjectTickets, and the next table is EmployeeRole.

專案票證ProjectTickets

票證Ticket 開啟者OpenedBy 提交者SubmittedBy 時數Hours 專案Project 提交日期DateSubmit
10011001 Perham, TomPerham, Tom Brewer, AlanBrewer, Alan 2222 藍色Blue 1/1/20131/1/2013
10021002 Roman, DanielRoman, Daniel Brewer, AlanBrewer, Alan 2626 紅色Red 2/1/20132/1/2013
10031003 Roth, DanielRoth, Daniel Ito, ShuIto, Shu 3434 黃色Yellow 12/4/201212/4/2012
10041004 Perham, TomPerham, Tom Brewer, AlanBrewer, Alan 1313 橙色Orange 1/2/20121/2/2012
10051005 Roman, DanielRoman, Daniel Bowen, EliBowen, Eli 2929 紫色Purple 10/1/201310/1/2013
10061006 Roth, DanielRoth, Daniel Bento, NunoBento, Nuno 3535 綠色Green 2/1/20132/1/2013
10071007 Roth, DanielRoth, Daniel Hamilton, DavidHamilton, David 1010 黃色Yellow 10/1/201310/1/2013
10081008 Perham, TomPerham, Tom Han, MuHan, Mu 2828 橙色Orange 1/2/20121/2/2012
10091009 Roman, DanielRoman, Daniel Ito, ShuIto, Shu 2222 紫色Purple 2/1/20132/1/2013
10101010 Roth, DanielRoth, Daniel Bowen, EliBowen, Eli 2828 綠色Green 10/1/201310/1/2013
10111011 Perham, TomPerham, Tom Bowen, EliBowen, Eli 99 藍色Blue 10/15/201310/15/2013

員工角色EmployeeRole

員工Employee 角色Role
Bento, NunoBento, Nuno 專案經理Project Manager
Bowen, EliBowen, Eli 專案負責人Project Lead
Brewer, AlanBrewer, Alan 專案經理Project Manager
Hamilton, DavidHamilton, David 專案負責人Project Lead
Han, MuHan, Mu 專案負責人Project Lead
Ito, ShuIto, Shu 專案負責人Project Lead
Perham, TomPerham, Tom 專案發起人Project Sponsor
Roman, DanielRoman, Daniel 專案發起人Project Sponsor
Roth, DanielRoth, Daniel 專案發起人Project Sponsor

此處實際上有兩個關聯性。There are actually two relationships here. 其中一個關聯性介於 [專案票證] 資料表的 [提交者] 與 [員工角色] 資料表的 [員工] 之間,另一個關聯性介於 [專案票證] 資料表的 [開啟者] 與 [員工角色] 資料表的 [員工] 之間。One is between SubmittedBy in the ProjectTickets table and Employee in the EmployeeRole table, and the other is between OpenedBy in the ProjectTickets table and Employee in the EmployeeRole table.

如果我們將這兩個關聯性加入模型 (先加入 [開啟者]),則 [管理關聯性] 對話方塊會顯示 [開啟者] 為作用中:If we add both relationships to the model (OpenedBy first), then the Manage Relationships dialog will show that OpenedBy is active:

現在,如果我們在報表畫布的資料表視覺效果中,建立使用 [員工角色] 中之 [角色] 和 [員工] 欄位及 [專案票證] 中之 [時數] 欄位的報表,我們只會看到專案發起人,因為他們是開啟專案票證的唯一人員。Now, if we create a report that uses Role and Employee fields from EmployeeRole, and the Hours field from ProjectTickets in a table visualization in the Report canvas, we’ll see only project sponsors because they’re the only ones that opened a project ticket.

我們可以變更作用中的關聯性,改為取得 [提交者] 而不是 [開啟者]。We can change the active relationship and get SubmittedBy instead of OpenedBy. 在 [管理關聯性] 中,我們取消核取 [專案票證 (開啟者)] 與 [員工角色 (員工)] 的關聯性,然後核取 [專案票證 (提交者)] 與 [員工角色 (員工)] 的關聯性。In Manage Relationships, we uncheck the ProjectTickets(OpenedBy) to EmployeeRole(Employee) relationship, and then we check the Project Tickets(SubmittedBy) to EmployeeRole(Employee) relationship.

在關聯性檢視中查看所有關聯性See all of your relationships in Relationship View

您的模型有時會有多個資料表,而且資料表之間的關聯性很複雜。Sometimes your model has multiple tables and complex relationships between them. Power BI Desktop 中的 [關聯性檢視] 以簡單易懂且可自訂的圖表,來顯示您模型中的所有關聯性、其方向和基數。Relationship View in Power BI Desktop shows all of the relationships in your model, their direction, and cardinality in an easy to understand and customizable diagram. 如需詳細資訊,請參閱 Power BI Desktop 中的關聯性檢視.To learn more, see Relationship View in Power BI Desktop.