DAX:跟关系相关的函数
在表格数据模型中,用户可以创建关系,并可以沿着关系的方向自动进行交叉过滤。但是在计算列中,必须通过RELATED 和 RELATEDTABLE函数来检索相关联的表。当使用CALCULATE函数时,可以直接使用现有的活跃关系来进行过滤,并不需要显示调用RELATED 和 RELATEDTABLE函数来利用关系。
一,RELATED函数和RELATEDTABLE 函数
RELATED 和 RELATEDTABLE函数作用于行上下文中,常用于计算列和迭代函数中。在行上下文中,通过RELATED 和 RELATEDTABLE函数获取相关联表中得数据。
从关系的角度,这两个函数的区别是:
- 在一对多的关系中,RELATED 用于从“多”端访问“一”端,在这种情况下,相关表中至多有一行。如果数据行不存在,那么RELATED 函数返回BLANK。
- 如果希望从关系的“一”端访问到“多”端,就需要使用RELATEDTABLE 函数,在这种情况下,相关表中可能有多行,RELATEDTABLE 返回一个表,表中包含所有与当前行相关联的其他表的所有行。
另外一个区别是返回值类型的不同,RELATED返回的是标量值,而RELATEDTABLE 返回的是表。
RELATED 和 RELATEDTABLE函数可以贯穿一条长长的关系链,跟关系的长度(或步数)无关。对关系的唯一限制是:所有关系都需要具有相同的类型(即一对多或对一),并且关系都朝着相同的方向。
1,RELATED函数
函数RELATED通过关系的多端,从其他表中返回一个或0个值,该值跟当前数据行有关系,该函数不受任何过滤器的影响:
RELATED(<column>)
使用函数RELATED的前提是:当前表和关系表之间存在关系,关系有多少步不重要的,只要可以过滤到。
当RELATED执行查找(lookup)时,它会检查所有值,而忽略所有的过滤器。
RELATED函数需要行上下文(row context),因此,RELATED函数只能用于计算列表达式中。
RELATED函数需要当前表和关联表之间存在关系,在函数中指定包含你需要数据的列,该函数遵循现有的多对一关系,以从相关表的指定列中获取一个值,也就是说,RELATED函数从多方关联到1方的一个值,参数column是1方表中的列。当RELATED函数执行查找时,它会检查指定表中的所有值,而不管可能已应用的任何过滤器。
注意:RELATED函数需要行上下文,因此,只能用于计算列表达式中(其当前行上下文是明确的) 和表扫描函数中。表扫描函数(如SUMX)获取当前行的值,然后扫描关联表以查找指定列的关联值。
例如,从下图中可以看到,FactSales和DimProduct之间存在 “*对1” 的关系,并且FactSales是多方,DimProduct是1方。可以在RELATED()函数中指定DimProduct表中的列,以从DimProduct表获得跟FactSales中某一行相关联的值。
在FactSales表中创建计算列,获取跟当前行相关的产品颜色的值:
Related_color = RELATED( DimProduct[Color])
举个例子,Sales 和 Product 之间有多对一的关系,Product 和 Product Category之间有多对一的关系,那么可以在Sales表中创建一个计算列,根据 Product Category来调整值。
Sales[AdjustedCost] = IF (
RELATED( 'Product Category'[Category] = "Cell Phone")
, Sales[UnitCost] * 0.95,
, Sales[UnitCost]
)
2,RELATEDTABLE 函数
RELATEDTABLE 函数通过关系的一端,从其他表中返回由一行或多行构成的表,表中包含所有与当前行相关联的其他表的所有行。
RELATEDTABLE(<tableName>)
RELATEDTABLE 函数收到过滤上下文的影响,在当前的过滤上下文中,返回相关联的数据行构成的表,RELATEDTABLE 函数相当于没有过滤条件的CALCULATETABLE函数:
CALCULATETABLE(<expression>)
RELATEDTABLE 函数不仅可以用于计算列中,还可以用于迭代函数中。
二,USERELATIONSHIP 函数
由于PowerBI的数据模型的限制,虽然两个表之间可以创建多个关系,但是只能有一个活跃的关系,其他关系是非活跃的。在一个确定的时间点,任何两个表之间只能激活一个关系,因此,对于两个表之间的关系,USERELATIONSHIP在激活一个关系的同时,会禁用其他关系。
在使用Calculate函数设置筛选上下文时,可以使用USERELATIONSHIP函数,在Calculate函数的上下文中启用非活跃的关系。
USERELATIONSHIP(<columnName1>,<columnName2>)
USERELATIONSHIP函数只能启用已有的关系,通过两个表之间的字段来确定关系。USERELATIONSHIP函数不返回任何值,只是启用关系,举个例子:
= CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))
三,CROSSFILTER 函数
对于已经存在的关系,通过CROSSFILTER函数指定交叉过滤(cross-filter)的方向,
CROSSFILTER(<columnName1>, <columnName2>, <direction>)
参数direction 有4个可用值:
- None :禁用关系
- Both :设置交叉过滤的方向为双向
- OneWay_LeftFiltersRight:设置交叉过滤的方向为单向,<columnName1>过滤<columnName2>,不能用于1对1的关系中和多对多的关系中,
- OneWay_RightFiltersLeft:设置交叉过滤的方向为单向,<columnName2>过滤<columnName1>,不能用于1对1的关系中和多对多的关系中,
CROSSFILTER 函数的注意事项:
- CROSSFILTER函数只能用于数据模型中已经存在的关系。
- 在Calculate等函数的上下文,CROSSFILTER函数会覆盖在数据模型中设置的关系的cross-filter的方向。
- 如果CALCULATE 嵌套,并且多个 CALCULATE 表达式包含 CROSSFILTER 函数,那么最内层的CROSSFILTER 是在发生冲突或歧义时优先级最高。
参考文档: