关系是 DAX 非常重要的一个知识,有多种类型和丰富的扩展场景。在数据模型一文中,你对关系已经有了初步了解,在本文中我们会对关系做深入全面的阐述。
关系的基本配置
Power BI 中的关系有两个基本配置项,基数的类型和交叉筛选的方向。当你需要为两表建立关系时,必须为它们选择合适的选项。
基数的类型
多对一 (*: 1) : 最常见的、 默认的基数类型。在多对一关系中,位于多端的表的关系列可以有重复值,而位于一端的表的关系列则必须是不重复值。多端的表通常是事实表,一端的表称为查找表或维表。
一对一 (1:1) :两表的关系列都具有唯一值,一对一关系连接的表可以视为一个整体。
多对多关系:两表的关系列都含有重复值。在 Power BI 支持多对多关系之前,关系涉及的列至少有一个必须包含唯一值。 不过,在某些场景下可能没有列包含唯一值,比如分析订单和产品的时候,一个订单包含多个产品,一个产品也可以属于多笔订单,通常的解决方法是在模型中引入包含必要唯一值的附加表,也称做桥接表。现在借助多对多关系,你可以直接连接此类表,关于多对多关系稍后将专门发文介绍。
关系的方向
之前介绍过,每个关系都可以有一个或两个筛选方向。一般来说,筛选总是从关系的一端传递到多端。如果关系是双向的(有两个箭头),那么筛选也会从多端传递到一端。
一个例子可以帮助你更好地理解这种行为。如果你基于上图所示的数据模型创建一个透视表,将年份置于行标签,将 SalesAmount 和 Count of ProductName 置于值区域,你将看到下面的结果。
行标签使用的年份列来自日期表,日期表位于与销售表的关系的一端。所以当你把销售额的总和放在数据透视表中,引擎会根据年份筛选销售额。销售与产品表的关系是双向的;当你把产品名称的计数放在数据透视表中,你会得到每年卖出的产品数量。换句话说,对年份的筛选通过关系链传递到了产品表。
如果你在透视表的行上添加颜色标签并在值区域中添加 Count of FullDateLabel,那么结果就有点难以理解了,如图所示。
行标签的筛选条件来自产品表的颜色列,由于产品表位于和销售表关系的一端,因此会正确的筛选度量值 Sum of SalesAmount,Count of ProductNames 的计算也没有问题,因为它计算的值与行标签位于同一张表。
来自颜色列的筛选器没有传递到 Date 表的原因是 Date 表和 Sales 表之间的关系是单向的,从日期表指向销售表. 因此,即使销售表有活动的筛选条件正在生效,筛选也不能传递到日期表,因为关系的类型阻止了它。
如果将日期表和销售表的之间的关系变为双向筛选,结果会变成:
正如你所看到的,结果发生了变化,反映了至少有一种特定颜色的产品被出售的天数。
理解双向筛选
DAX 引擎通过注入筛选代码的方式使得关系在两个方向上的传递成为可能,如果你已经熟悉扩展表理论,双向筛选看起来像是扩展在两个方向上都生效了,在多数情况下,代码可以很好地工作。虽然双向筛选非常方便,但你需要谨慎使用这个功能,因为双向筛选有很明显的副作用。
双向筛选的副作用
解决方案
理解非活动关系
数据模型可能同时包含活动关系和非活动关系。非活动关系以虚线表示,称之为虚线关系可能更直观。非活动关系的存在也是为了规避关系歧义,因为当两个表之间有多个关系时,只允许一个活动关系存在。
销售表的每笔订单都存储了订单日期和交货日期。通常,你希望根据订单日期进行销售分析,但对于某些特定度量值,你希望考虑交付日期。在这种情况下,你可以在销售表和 Date 表之间创建两个关系:一个基于 OrderDateKey,另一个基于 DeliveryDateKey。一次只能激活其中一个,因为你通常根据订单日期分析销售情况,所以你保持与 OrderDateKey 的关系处于活动状态,而另一个则处于不活动状态。然后,你需要创建一个基于给定日期统计交付数据的度量值,以便与订单数据进行比较。这个新度量值(交付数量)应该使用非活动关系来计算销售额,同时断开与订单日期的关系。
要解决这个问题,可以借助 CALCULATE 和 USERELATIONSHIP,代码如下所示
[Delivered Amount] := CALCULATE ( [Sales Amount], USERELATIONSHIP ( Sales[DeliveryDateKey], Date[DateKey] ) )
DeliveryDateKey 和 DateKey 之间的关系将在[Delivered Amount]计算时被激活,同时与 OrderDateKey 的关系将被停用。在图 5-22 中,你可以看到一个数据透视表,它显示了基于 OrderDateKey 计算的销售额和基于 DeliveryDateKey 计算的销售额之间的不同。
激活非活动关系注意事项
前文我们提到过,当两个表之间有多个关系时,只允许存在一个活动(正在生效的)关系,这是一条通用规则,不仅适用于相邻的两个表,也适用于模型中所有可以通过关系传递的表。在复杂的模型中,由于表间关系较多,激活某些非活动关系时可能会违反此规则
遇到这种情况你可以采取同双向筛选相同的解决方法,可以借助 CALCULATE 和 USERELATIONSHIP 在度量值中激活非活动关系。
行上下文和关系
行上下文和关系的交互易于理解,因为没什么需要理解的:它们不会以任何方式相互作用,至少不会自动地相互作用。
试想一下,如果你要在销售表中创建一个计算列,统计存储于该事实表的单位价格和存储于产品表的产品标价之间的差额,你可以尝试使用以下公式:
Sales[UnitPriceVariance] = Sales[UnitPrice] - Product[UnitPrice]
该表达式使用来自两个不同表格的两列,并将在一个行上下文中计值,该上下文仅在销售表中迭代,因为计算列是在该表内定义的。产品表位于关系的“一”端(很明显,销售表位于“多”端),所以你可能希望能访问产品表相关行的标价。不幸的是这无法实现。销售表的行上下文不会传递到产品表,如果尝试创建计算列,该公式将返回一条错误消息。
如果你想从位于关系的多端表中访问关系一侧的列,正如本例中的情况,可使用 RELATED 函数。RELATED 接受列名作为参数,从当前行上下文开始通过沿着多对一方向中的现有关系来发现相应行并检索列值。
下面的写法可以纠正之前的公式:
Sales[UnitPriceVariance] = Sales[UnitPrice] - RELATED ( Product[UnitPrice] )
RELATED 在位于关系多端的表的行上下文上工作,如果关系一端的行上下文是活动的,那么这个函数就不适用了,因为按照关系返回的结果会包含多行。在这种情况下,需要使用 RELATED 的伴侣 RELATEDTABLE 函数,你可以在关系的一端使用 RELATEDTABLE,它返回与当前表相关的位于关系多端的表的所有行。例如,如果你想计算每个产品的销售记录数,你可以使用下面的公式,用作产品表的计算列:
Product[NumberOfSales] = COUNTROWS ( RELATEDTABLE ( Sales ) )
表达式计算与当前产品对应的销售表中的行数。你可以在下图中看到结果。
值得注意的是,RELATED 和 RELATEDTABLE 可以贯穿一条长长的关系链以实现其结果,它们并不限于单步传递。例如,你可以使用与相同的代码创建一个列,但这次是在产品类别表中:
'Product Category'[NumberOfSales] = COUNTROWS ( RELATEDTABLE ( Sales ) )
结果是每个产品类别的销售记录数,它通过关系链从产品类别到产品子类别,再到产品,最终到达销售表。
关于关系链的唯一限制是,所有关系都需要具有相同的类型(即一对多或多对一),并且它们都朝着相同的方向传递。如果有两个表之间通过一对多和多对一进行关联,中间有一个桥接表,那么 RELATED 和 RELATEDTABLE 都不能工作。一对一关系是一对多和多对一的集合体,因此可以在一对多关系链中使用一对一关系而不会造成关系中断。
让我们用一个例子来说明这个概念(模型关系图见下方「筛选上下文和关系」一节)。你可能认为客户表与产品表存在关系,因为客户表与销售表之间存在一对多关系,而销售表与产品表之间存在多对一关系。因此,一个关系链将这两个表连接起来。然而,这并不成立,因为两种关系的方向不同。
我们将此场景称为多对多关系。换句话说,一个客户与许多产品(TA 购买的产品)相关,而一个产品与许多客户(购买产品的人)相关。稍后你将了解如何使多对多关系起作用的细节;现在让我们专注于行上下文。如果你试图通过多对多关系应用 RELATEDTABLE,那么结果可能不是你期望的那样。例如,在产品表中考虑这样一个计算列,其公式如下:
Product[NumOfBuyingCustomers] = COUNTROWS ( RELATEDTABLE ( Customer ) )
你可能希望看到,对于每一行,购买该产品的客户数量。出乎意料的是,结果总是 18869,即数据库中的客户总数。
RELATEDTABLE 不能遵循关系链,因为它们的方向不同:一个是一对多关系,另一个是多对一关系。因此,来自产品的筛选条件无法到达客户。值得注意的是,如果你从相反的方向尝试这个公式,也就是说,对于每个客户计算其购买的产品的数量,结果将是正确的:每一行的不同的数字代表客户购买的产品的数量。
筛选上下文和关系
你已经了解到行上下文与关系之间并不存在相互作用,且如果想贯穿关系,有两个不同的函数可供使用,这取决于在访问目标表时你位于关系的哪一端。
筛选上下文以不同的方式运行:它们自动与关系交互,并且根据筛选关系的设置,产生不同的行为。通常的规则是,如果关系的筛选方向允许,则筛选上下文通过关系传递。
通过使用带有度量值的简单数据透视表,这种行为非常容易理解。在下图中,你可以看到一个基于目前使用的数据模型生成的透视表,其中定义了三个非常简单的度量值:
[NumOfSales] := COUNTROWS ( Sales ) [NumOfProducts] := COUNTROWS ( Product ) [NumOfCustomers] := COUNTROWS ( Customer )
筛选器位于产品颜色列,产品表与销售表是一对多关系,因此筛选上下文从产品表传递到销售表,这易于观察,因为 NumOfSales 度量值只计算具有特定颜色的产品的销售记录。NumOfProducts 显示了每种颜色的产品数量,每一行的不同值(颜色)就是你所期望的,因为筛选器和计算发生在同一张表。
另一方面,NumOfCustomers 在统计客户数量时总是显示相同的值,即客户总数。这是因为客户表和销售表之间的关系是从前者指向后者,如图所示:
筛选从产品表开始,沿着箭头的方向传递到销售表,但是当它试图向客户表传递的时候,被关系阻止了,箭头的方向不允许筛选继续传递,因为单向关系只允许筛选上下文沿着单向传递。
你可以认为关系上的箭头就像信号一样。如果启用了它们,那么信号灯为绿色,传递就会发生。如果没有启用箭头,则信号灯为红色,筛选无法传递。箭头总是从关系的一端指向多端,你也可以增加从多端指向一端的箭头(双向筛选),如果禁用双向筛选,那么筛选将不会从多端向一端传递。
如果你查看下图中的数据透视表,可以更好地理解这种行为。这次我们不使用产品颜色行,而是通过教育水平列(Customer Education)进行切片。
筛选从客户表开始,沿着箭头方向到达销售表。然后,它可以从销售表进一步传递到产品表,因为销售和产品之间的关系是双向的。
现在,我们向模型添加一个类似的度量值,它计算产品子类别的数量,例如下面这个:
NumOfSubcategories := COUNTROWS ( 'Product Subcategory' )
将其添加到报表中,你将看到,行标签没有筛选产品子类别的数量,如图所示。
这是因为产品表和产品子类别表之间的关系是单向的,也就是说筛选器只能在一个方向上传递。一旦你启用了从产品表指向子类别表的关系,筛选器将发生传递,如图所示。
正如行上下文那样,需要经过多少步才能到达一个表并不重要:只要有启用的关系链,关系就会自动传递。例如,如果在产品类别上放置一个筛选器,过滤条件将传递到产品子类别表,然后传递到产品表,最后传递到销售表。
我觉得这里“产品数量”可能会有歧义,应该改成“产品名称的数量”更准确。因为对初学者来说产品数量给人的感觉是在统计’sales’中的quantity,但实际上统计的是productname的数量。
高老师,您好。请教一个问题。我有一张’客户经理表‘,在‘地区’表的一端,而‘地区’表,在‘订单表’的一端。也就是说,筛选可以通过客户经理表传到地区表再传到订单,且是单向的。现在我的问题是,我写了一个度量值。销售:FILTER ALL ‘地区’整表 且 利润率大于0.12 = CALCULATE(
[销售:销售额 合计] ,
FILTER(ALL(‘地区’), [销售:利润率 合计] > 0.12 )
)
。这个时候,我用‘客户经理表’作为切片器,我发现客户经理的切片器不起作用。那是不是说明,ALL‘地区’表之后,客户-地区表之间的关系也失效了呢?这是为什么呢?我ALL的是‘地区’表,不是‘客户经理’表
高老师,请问Power BI ReportServer是什么啊?
高飞大大
圣经第三版的493页,倒数第三段的最后一句话是不是 这样理解
****原文****如下:
换句话说,这个数字是指某一年销售的所有产品的收入总和,非常不直观
—————————
***我之前理解为***
“某一年销售的”这个条件也针对了Receipts表,并筛选了Receipts表(然后就没然后了,我懵了)
*****但实际看下来,我是这样理解Rec Amount Longer Path的业务意义****
Rec Amount Longer Path,转换成手动筛选的步骤是
首先 ,我们聚焦到CY 2007的Rec Amount Longer Path这个值 :即155,636,856.07
1、筛选器先根据矩阵CY 2007,将筛选传递到SALES表,→筛选出SALES表所有[ORDER DATE]等于CY 2007的记录
2、此时假设 第一步筛选出的结果的产品列 为 {pro 01,pro 02,pro03},(但注意,这些产品可能在SALES和RECEIPTS表中的CY 2007,CY 2008,CY 2009都有销售)
3、将第二步的筛选器,传递到PRODUCT表,→product被筛选出的也是{pro 01,pro 02,pro03},此时日期筛选没有了
4、最后在RECEIPTS表上的筛选器是{pro 01,pro 02,pro03}→但这里并不筛选年份 CY 2007→导致 RECEIPTS所有符合{pro 01,pro 02,pro03}的记录都呈现出来,而不是仅仅呈现CY 2007的
5、这几个步骤也展示了,
为什么Rec Amount Longer Path的结果会比 Receipts AMT(正确的,走短路径)那个度量值结果更大 ,即:
CY 2007 Rec Amount Longer Path (155,636,856.07)
大于
CY 2007 Receipts AMT( 92,929,563.18),
→因为CY 2007 Rec Amount Longer Path的结果不仅仅包含了CY2007的结果,也包含了CY 2008 ,CY 2009的结果
“值得注意的是,如果你从相反的方向尝试这个公式,也就是说,对于每个客户计算其购买的产品的数量,结果将是正确的:每一行的不同的数字代表客户购买的产品的数量。”原文这句话不好理解。因为从客户到销售表是一对多关系,从销售表到产品是多对一关系,不是跟前面说的情况一样吗?怎么就可以统计出客户购买产品的数量了?
我用PBI DESKTOP来测试一下关系这个例子。但是好像年份没有起到筛选作用。所有行都是一样的数值。不知道为什么。
高飞老师你好,关于多对多的关系,就是比如A和C都是关系的多端,B是一端,A和C通过B建立了间接多对多的联系。
第一种一个写法calculatetable(A,calculatetable(B,C))就可以,当然这个很好理解,C表通过扩展表影响B表,B表通过一端-多端的关系影响A表。
但是我发现在使用calculatetable(A,C)这种情况的话,C是没办法直接影响A的,但是我的理解是C作为整张表作为筛选条件的时候,是要考虑扩展表理论的,所以C表要影响B表,从而影响A表。但是返回的结果完全没有影响,请问问题出在哪里?
老师你好,“值得注意的是,如果你从相反的方向尝试这个公式,也就是说,对于每个客户计算其购买的产品的数量,结果将是正确的:每一行的不同的数字代表客户购买的产品的数量。”这里真不理解,是否因为销售表和产品表之间开启了双向筛选?
高老师,这句话有点没懂“RELATED 在位于关系多端的表的行上下文上工作,如果关系一端的行上下文是活动的,那么这个函数就不适用了,因为按照关系返回的结果会包含多行。”这里说的“上下文是活动的”怎么理解?
看到这里以后,我准备放弃power BI了。有没有其他可以替代的软件推荐啊。
大数据分析的。