DAX 第八篇:表连接

表连接是指两张表根据关联字段,组合成一个数据集。表连接不仅可以利用数据模型中已有的关系,而且可以利用DAX表达式基于表的任意列定义连接条件。因此,在DAX中,实现表与表之间的连接,有两种方式:

  • 第一种方式:利用数据模型中的现有关系,以便查询包含在不同表中的数据。
  • 第二种方式:编写 DAX 表达式创建连接,以生成与关系等效的效果。

一,利用数据模型中的关系(左外连接)

利用数据模型中的关系来连接表,是DAX中最常用的行为,关系隐式实现外连接(LEFT JOIN)。DAX数据模型中的关系分为三种:实线关系,虚线关系和虚拟关系。其中,实线关系是指已经创建的、处于激活状态的关系;虚线关系是指已经创建的、处于未激活状态的关系;虚拟关系是指未创建、只在DAX中临时创建的关系。 数据模型中的关系,要求一方提供唯一值列,因此,只能创建1:M 和 1:1类型的关系,当两个表之间存在多对多的关系时,可以在DAX中使用TREATAS函数创建临时的关系。

例如,FactSales和DimProduct、FactSales和DimDate之间存在多对一的关系,可以利用关系来建立左外连接(LEFT OUTER JOIN),注意,左外联接的左表(保留表)是FactSales,右表是DimDate和DimProduct,这意味着,如果FactSales表中存在的数据不包含在DimProduct或DimDate表中,那么这些数据行都被分到Color为空值(BLANK)或CalendarYear为空值的分组中。

SUMMARIZE(FactSales,DimProduct[Color],DimDate[CalendarYear],"SalesAmount",SUM(FactSales[SalesAmount]))

该DAX表达式等价于以下TSQL脚本,

select p.Color
    ,d.CalendarYear
    ,sum(f.SalesAmount) as SalesAmount
from FactSales f
left join DimProduct p
    on f.ProductKey=p.ProductKey
left join DimDate d
    on f.DateKey=d.DateKey
group by p.Color
    ,d.CalendarYear

二,利用DAX实现自然连接

自然连接不需要指定连接条件,两个表自动根据同名列进行匹配,前提是同名列的数据类型必须完全相同。在连接期间使用严格的比较语义,没有类型兼容和强制转换,例如,1不等于1.0。

NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)  
NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>) 

这两个函数不要求两个表之间存在关系,但是要求两个表之间存在同名列,并且同名列的数据类型必须完全相同。返回的数据集包含两个表中的同名列和其他列的数据,如果是自然内连接,那么结果集指包含匹配的数据;如果是自然左外连接,那么左表中的数据会全部保留,右表只返回匹配的数据,对于左表中没有匹配的数据,把相应的右表列设置为空值(BLANK)。

 三,创建虚拟连接

TREATAS函数用于建立虚拟关系,适合在表和表之间不存在关系时使用:

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )  

参数注释:

  • table_expression:返回表的表达式
  • column:基础表中的列,不能是表达式,column参数的数量必须和table_expression列数相同,并且顺序相同。

返回值:

返回一个表,包含column参数和table_expression参数中匹配的数据行。如果column参数中不包含table_expression中的值,那么忽略该值,也就是说,column参数和table_expression参数使用inner join,只有两方都存在值时,才会返回该值。

例如,表构造器{"Red", "Green", "Yellow"} 创建一个单列三行的表,在DimProduct[Color]列上设置了一个过滤器,过滤器的值只包含“Red”,“Green”和“Yellow”,如果DimProduct [Color]中不存在“Yellow”,则有效滤波器值将为“Red”和“Green”。

TREATAS({"Red", "Green", "Yellow"}, DimProduct[Color]) 

TREATAS的应用场景,TREATAS()函数用于在表格之间建立虚拟连接,当表之间出现多对多的关系时,此时无法使用关系来建立连接。

 例如,在模型中,表DimProduct1 和  DimProduct2之间没有关联,通过TREATAS()函数建立两者之间的连接:

CALCULATE(
SUM(Sales[Amount]), 
TREATAS(VALUES(DimProduct1[ProductCategory]), DimProduct2[ProductCategory])
)

四,激活已有的不活跃关系

在引用关系时,DAX可以在查询上下文中临时修改关系的设置,比如引用处于不活跃状态的关系,修改现有关系的交叉过滤的方向设置。重写的关系设置只在查询时有效,不会影响数据模型中的关系设置。

USERELATIONSHIP使用数据模型中已有的关系,用于激活候选关系,通过关系两端的字段来指定关系:

USERELATIONSHIP(<columnName1>,<columnName2>) 

在USERELATIONSHIP函数中,关系的状态是不重要的,但是关系必须事先创建于数据模型中,由于活跃的关系在DAX中是可以直接引用的,因此,该函数实际上用于引用处于不活跃(Inactive)状态的关系。

=CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date])) 

五,指定交叉过滤的方向

CROSSFILTER 使用数据模型中已有的关系,通过关系两端的字段来指定关系:

CROSSFILTER(<columnName1>, <columnName2>, <direction>) 

在CROSSFILTER 中,关系的交叉过滤(cross-filtering)的设置是不重要的,也就是说,不论关系在数据模型中设置为single 方向或both方向,都不会影响函数的使用,CROSSFILTER将覆盖任何现有的交叉过滤(cross-filtering)设置。

 

参考文档:

DAX 联接表系列(三)

TREATAS

USERELATIONSHIP

posted @ 2019-09-10 15:57 悦光阴 阅读(...) 评论(...) 编辑 收藏