活动与非活动关系指南Active vs inactive relationship guidance

本文面向使用 Power BI Desktop 的数据建模者。This article targets you as a data modeler working with Power BI Desktop. 它指导你何时创建活动或非活动模型关系。It provides you with guidance on when to create active or inactive model relationships. 默认情况下,活动关系将筛选器传播到其他表。By default, active relationships propagate filters to other tables. 然而,非活动关系只有在 DAX 表达式激活(使用)此关系时,才传播筛选器。Inactive relationship, however, only propagate filters when a DAX expression activates (uses) the relationship.

备注

本文不涵盖对模型关系的介绍。An introduction to model relationships is not covered in this article. 如果你对模型关系、其属性或配置方法并非完全熟悉,建议先阅读 Power BI Desktop 中的模型关系一文。If you're not completely familiar with relationships, their properties or how to configure them, we recommend that you first read the Model relationships in Power BI Desktop article.

此外,还应了解星型架构设计,这一点也很重要。It's also important that you have an understanding of star schema design. 有关详细信息,请参阅了解星型架构和 Power BI 的重要性For more information, see Understand star schema and the importance for Power BI.

活动关系Active relationships

通常,建议尽可能定义活动关系。Generally, we recommend defining active relationships whenever possible. 此类关系扩大了报表作者和使用问答的用户使用模型的范围和可能性。They widen the scope and potential of how your model can be used by report authors, and users working with Q&A.

例如,假设有一个导入模型,旨在分析航空公司航班准点率 (OTP)。Consider an example of an Import model designed to analyze airline flight on-time performance (OTP). 此模型包含 Flight 表,这是一个事实类型表,每个航班存储一行。The model has a Flight table, which is a fact-type table storing one row per flight. 每行记录航班日期、航班号、出发和到达机场以及任何延误时间(以分钟为单位)。Each row records the flight date, flight number, departure and arrival airports, and any delay time (in minutes). 还有一个 Airport 表,这是维度类型表,每个机场存储一行。There's also an Airport table, which is a dimension-type table storing one row per airport. 每行记录机场代码、机场名称和国家/地区。Each row describes the airport code, airport name, and the country.

下面展示了这两个表的部分模型图。Here's a partial model diagram of the two tables.

显示包含下面两个表的模型的示意图:Flight 和 Airport 表。

Flight 表与 Airport 表之间有两种模型关系。There are two model relationships between the Flight and Airport tables. 在 Flight 表中,DepartureAirport 和 ArrivalAirport 列与 Airport 表的 Airport 列关联。In the Flight table, the DepartureAirport and ArrivalAirport columns relate to the Airport column of the Airport table. 在星型架构设计中,Airport 表被描述为角色扮演维度In star schema design, the Airport table is described as a role-playing dimension. 在此模型中,两个角色分别为“出发机场”和“到达机场”。In this model, the two roles are departure airport and arrival airport.

虽然这种设计对于关系星型架构设计很有效,但是对于 Power BI 模型却不行。While this design works well for relational star schema designs, it doesn't for Power BI models. 这是因为模型关系是筛选器传播路径,而这些路径必须是确定的。It's because model relationships are paths for filter propagation, and these paths must be deterministic. 因此,一个模型不能在两个表之间有多个活动关系。For this reason, a model cannot have multiple active relationships between two tables. 所以,正如此示例中所述,一种关系是活动的,而另一种关系则是非活动的(由虚线表示)。Therefore—as described in this example—one relationship is active while the other is inactive (represented by the dashed line). 具体而言,与 ArrivalAirport 列之间的关系是活动的。Specifically, it's the relationship to the ArrivalAirport column that's active. 也就是说,应用于 Airport 表的筛选器会自动传播到 Flight 表的 ArrivalAirport 列。This means filters applied to the Airport table automatically propagate to the ArrivalAirport column of the Flight table.

此模型设计对数据的报表生成方式施加了很大限制。This model design imposes severe limitations on how the data can be reported. 具体而言,无法通过筛选 Airport 表来自动隔离出发机场的航班详细信息。Specifically, it's not possible to filter the Airport table to automatically isolate flight details for a departure airport. 由于报表要求涉及同时按出发机场和到达机场进行筛选(或分组),因此需要两个活动关系。As reporting requirements involve filtering (or grouping) by departure and arrival airports at the same time, two active relationships are needed. 将此要求转换为 Power BI 模型设计意味着,模型必须包含两个机场表。Translating this requirement into a Power BI model design means the model must have two airport tables.

下面展示了改进后的模型设计。Here's the improved model design.

显示包含下面四个表的模型的示意图:Date、Flight、Departure Airport 和 Arrival Airport。

现在,此模型包含两个机场表:Departure Airport 和 Arrival Airport。The model now has two airport tables: Departure Airport and Arrival Airport. 这两个机场表与 Flight 表之间的模型关系是活动的。The model relationships between these tables and the Flight table are active. 另请注意,Departure Airport 和 Arrival Airport 表中的列名分别以 Departure 和 Arrival 这两个词为前缀。Notice also that the column names in the Departure Airport and Arrival Airport tables are prefixed with the word Departure or Arrival.

改进后的模型设计支持生成以下报表设计。The improved model design supports producing the following report design.

显示包含两个切片器和一个表视觉对象的报表页的示意图。

报表页按“墨尔本”作为出发机场进行筛选,表视觉对象按到达机场进行分组。The report page filters by Melbourne as the departure airport, and the table visual groups by arrival airports.

备注

对于导入模型,附加表不仅增大了模型大小,还延长了刷新时间。For Import models, the additional table has resulted in an increased model size, and longer refresh times. 因此,它与导入建模的数据缩减方法一文中所述的建议相矛盾。As such, it contradicts the recommendations described in the Data reduction techniques for Import modeling article. 不过,在此示例中,只包含活动关系的要求比这些建议更重要。However, in the example, the requirement to have only active relationships overrides these recommendations.

此外,相对于事实类型表的行计数,维度类型表的行计数通常较少。Further, it's common that dimension-type tables contain low row counts relative to fact-type table row counts. 因此,模型大小增加和刷新时间延长都不太可能会太多。So, the increased model size and refresh times aren't likely to be excessively large.

重构方法Refactoring methodology

下面介绍了一种方法,可将模型从一个角色扮演维度类型表重构为,采用每个角色一个表的设计。Here's a methodology to refactor a model from a single role-playing dimension-type table, to a design with one table per role.

  1. 删除所有非活动关系。Remove any inactive relationships.

  2. 不妨重命名角色扮演维度类型表,以更好地描述它的角色。Consider renaming the role-playing dimension-type table to better describe its role. 在此示例中,Airport 表与 Flight 表的 ArrivalAirport 列关联,因此将它重命名为 Arrival Airport。In the example, the Airport table is related to the ArrivalAirport column of the Flight table, so it's renamed as Arrival Airport.

  3. 创建角色扮演表的副本,同时为它命名可反映其角色的名称。Create a copy of the role-playing table, providing it with a name that reflects its role. 如果是导入表,建议定义计算表。If it's an Import table, we recommend defining a calculated table. 如果是 DirectQuery 表,可以复制 Power Query 查询。If it's a DirectQuery table, you can duplicate the Power Query query.

    在此示例中,Departure Airport 表是使用以下计算表定义进行创建。In the example, the Departure Airport table was created by using the following calculated table definition.

    Departure Airport = 'Arrival Airport'
    
  4. 创建活动关系来关联新表。Create an active relationship to relate the new table.

  5. 不妨重命名表中的列,以准确反映它们的角色。Consider renaming the columns in the tables so they accurately reflect their role. 在此示例中,所有列都以 Departure 或 Arrival 一词为前缀。In the example, all columns are prefixed with the word Departure or Arrival. 默认情况下,这些名称可确保报表视觉对象具有自描述的明确标签。These names ensure report visuals, by default, will have self-describing and non-ambiguous labels. 同时还能改进问答体验,便于用户轻松编写问题。It also improves the Q&A experience, allowing users to easily write their questions.

  6. 不妨向角色扮演表添加说明。Consider adding descriptions to role-playing tables. (在“字段”窗格中,当报表作者将光标悬停在表上方时,工具提示中会显示说明。)通过这种方式,可以向报表作者传达其他任何筛选器传播详细信息。(In the Fields pane, a description appears in a tooltip when a report author hovers their cursor over the table.) This way, you can communicate any additional filter propagation details to your report authors.

非活动关系Inactive relationships

在特定情况下,非活动关系可以满足特殊报表需求。In specific circumstances, inactive relationships can address special reporting needs.

现在来看看不同的模型和报表要求:Let's now consider different model and reporting requirements:

  • 销售模型包含 Sales 表,表中有两个日期列:OrderDate 和 ShipDateA sales model contains a Sales table that has two date columns: OrderDate and ShipDate
  • Sales 表中的每行都记录一个订单Each row in the Sales table records a single order
  • 日期筛选器几乎总是应用于 OrderDate 列,其中始终存储有效日期Date filters are almost always applied to the OrderDate column, which always stores a valid date
  • 只有一个度量值要求将日期筛选器传播到 ShipDate 列,其中可能包含BLANK(即在订单发货前)Only one measure requires date filter propagation to the ShipDate column, which can contain BLANKs (until the order is shipped)
  • 不要求同时按订购日期和发货日期进行筛选(或分组)There's no requirement to simultaneously filter (or group by) order and ship date periods

下面展示了这两个表的部分模型图。Here's a partial model diagram of the two tables.

显示包含下面两个表的模型的示意图:Sales 和 Date。

Sales 表与 Date 表之间有两种模型关系。There are two model relationships between the Sales and Date tables. Sales 表中的 OrderDate 和 ShipDate 列与 Date 表的 Date 列关联。In the Sales table, the OrderDate and ShipDate columns relate to the Date column of the Date table. 在此模型中,Date 表的两个角色分别为“订购日期”和“发货日期”。In this model, the two roles for the Date table are order date and ship date. 与 OrderDate 列之间的关系是活动的。It's the relationship to the OrderDate column that's active.

除了一个之外,其余所有五个度量值都必须按 OrderDate 列进行筛选。All of the six measures—except one—must filter by the OrderDate column. 这个例外的度量值就是“已发货订单数”,它必须按 ShipDate 列进行筛选。The Orders Shipped measure, however, must filter by the ShipDate column.

下面展示了“订单数”度量值定义。Here's the Orders measure definition. 它只计算筛选器上下文中的 Sales 表行数。It simply counts the rows of the Sales table within the filter context. 所有应用于 Date 表的筛选器都会传播到 OrderDate 列。Any filters applied to the Date table will propagate to the OrderDate column.

Orders = COUNTROWS(Sales)

下面展示了“已发货订单数”度量值定义。Here's the Orders Shipped measure definition. 它使用 USERELATIONSHIP DAX 函数,仅在表达式求值期间才激活特定关系的筛选器传播。It uses the USERELATIONSHIP DAX function, which activates filter propagation for a specific relationship only during the evaluation of the expression. 在此示例中,使用的是与 ShipDate 列之间的关系。In this example, the relationship to the ShipDate column is used.

Orders Shipped =
CALCULATE(
    COUNTROWS(Sales)
    ,USERELATIONSHIP('Date'[Date], Sales[ShipDate])
)

此模型设计支持生成以下报表设计。This model design supports producing the following report design.

显示包含两一个切片器和一个表视觉对象的报表页的示意图。

报表页按 2019 年第 4 季度进行筛选。The report page filters by quarter 2019 Q4. 表视觉对象按月份进行分组,并显示各种销售统计信息。The table visual groups by month and displays various sales statistics. “订单数”和“已发货订单数”度量值的结果不同。The Orders and Orders Shipped measures produce different results. 它们都使用相同的摘要逻辑(统计 Sales 表中的行数),但使用的 Date 表筛选器传播却不同。They each use the same summarization logic (count rows of the Sales table), but different Date table filter propagation.

请注意,“季度”切片器包含 BLANK 项。Notice that the quarter slicer includes a BLANK item. 此切片器项作为表扩大的结果显示。This slicer item appears as a result of table expansion. 虽然 Sales 表的每行都有订购日期,但某些行的发货日期显示为 BLANK(即这些订单尚未发货)。While each Sales table row has an order date, some rows have a BLANK ship date—these orders are yet to be shipped. 表扩大也考虑非活动关系,因此显示 BLANK 可能是因为关系的“多”端包含 BLANK,也可能是因为出现数据完整性问题。Table expansion considers inactive relationships too, and so BLANKs can appear due to BLANKs on the many-side of the relationship, or due to data integrity issues.

建议Recommendations

总之,建议尽可能定义活动关系。In summary, we recommend defining active relationships whenever possible. 此类关系扩大了报表作者和使用问答的用户使用模型的范围和可能性。They widen the scope and potential of how your model can be used by report authors, and users working with Q&A. 也就是说,应在模型中复制角色扮演维度类型表。It means that role-playing dimension-type tables should be duplicated in your model.

不过,在特定情况下,可以为角色扮演维度类型表定义一个或多个非活动关系。In specific circumstances, however, you can define one or more inactive relationships for a role-playing dimension-type table. 在以下情况下,可以考虑这种设计:You can consider this design when:

  • 不要求报表视觉对象同时按不同角色进行筛选There's no requirement for report visuals to simultaneously filter by different roles
  • 使用 USERELATIONSHIP DAX 函数激活特定关系,以进行相关模型计算You use the USERELATIONSHIP DAX function to activate a specific relationship for relevant model calculations

后续步骤Next steps

有关本文的详细信息,请参阅以下资源:For more information related to this article, check out the following resources: