Power BI: NATURALINNERJOIN的使用

查看官方的文档，NATURALINNERJOIN描述是这样的：

语法

NATURALINNERJOIN(<LeftTable>, <RightTable>)  

参数

术语	定义
LeftTable	用于定义联接左侧的表的表表达式。
RightTable	用于定义联接右侧的表的表表达式。

返回值

一个表，仅包含两个表的指定共有列都存在值的行。 返回的表将具有左侧表的公用列以及这两个表中的其他列。

备注

• 这些表在两个表的共有列（按名称）上联接。 如果两个表没有公共列名，则返回错误。

• 结果没有排序顺序保证。

• 两个表中要联接的列必须具有相同的数据类型。

• 只能基于同一源表中的列（具有相同的世系）进行联接。 例如，对于 Products[ProductID]、WebSales[ProductdID] 和 StoreSales[ProductdID]，WebSales 和 StoreSales 表与 Products 表之间存在基于 ProductID 列的多对一关系，因而 WebSales 和 StoreSales 表基于 [ProductID] 进行联接。

• 联接期间使用严格的比较语义。 类型强制转换不适用；例如 1 不等于 1.0。

• 在已计算的列或行级安全性 (RLS) 规则中使用时，不支持在 DirectQuery 模式下使用此函数。

总结了一下，NATURALINNERJOIN主要运用在两个场景：

1. 用于有关系的表

这个好理解，只需要将LeftTable、RightTable两个参数替换成已经建立关联关系的表名即可。

2. 用于无关系的表

借助DAX圣经里面的例子：

EVALUATE
VAR A =
    UNION (
        ROW ( "Name", "Audio", "Value", 1 ),
        ROW ( "Name", "Audio", "Value", 2 ),
        ROW ( "Name", "Computers", "Value", 3 ),
        ROW ( "Name", "Games", "Value", 4 ),
        ROW ( "Name", "Music", "Value", 5 )
    )
VAR B =
    UNION (
        ROW ( "Name", "Audio", "Ext", 6 ),
        ROW ( "Name", "Computers", "Ext", 7 ),
        ROW ( "Name", "Computers", "Ext", 8 ),
        ROW ( "Name", "Games", "Ext", 9 ),
        ROW ( "Name", "TV", "Ext", 10 )
    )
RETURN
    NATURALINNERJOIN ( A, B )

在DAX Studio执行后得到的结果为：

圣经中还提到：

用于无关系表的注意事项：

NATURALINNERJOIN 通常用于无关系的两个表，在这种情况下函数遵循以下规则：

• 结果不保证排序顺序。

• 两表用于连接的公共列必须使用相同的名称，且具有相同的数据类型。

• 连接使用的列必须具有相同的数据沿袭，或者都不具备数据沿袭。

• 连接使用严格的比较语义。不会强制转换类型，例如，1 不等于 1.0。

用于关系表的注意事项：

NATURALINNERJOIN 可以用于存在关系的两个表，在这种情况下，通过 Power BI Desktop 新建表和在 DAX Studio 中创建查询的行为有所不同：

• 在 Power BI Desktop 中使用此函数新建表，两表不能包含同名列，否则将报错。

• 在 DAX Studio 中创建的查询不受同名列的限制，结果可以区分来自不同表的相同列。

圣经源地址：https://www.powerbigeek.com/understanding-naturalinnerjoin/

遇到的问题

在实际运用中，两个无关系的表在使用NATURALINNERJOIN往往出现各种问题，以下是我的问题分析过程：

已知两个表：factCostControl，factCosBreakdown

它们都有字段CostKey，且都是字符型，并已经建立了1对多的关联关系。

在某一个计算中需要使用到这两个表的数据，如果只是简单的检索他们的交集，写法很简单：

EVALUATE
NATURALINNERJOIN(
    ALL('Breakdown'),
    ALL('Control')
)

或者：

EVALUATE
NATURALINNERJOIN(
    'Breakdown',
    'Control'
)

以上两个公式，在没有任何外部筛选器的情况下，它们得到的结果是相同的，会检索出两个表相互交叉的所有数据。

而由于这两个表本身都有几十个字段，当我在使用ALL函数时，这两个表的几十个字段都参与计算，结果就导致性能很差，于是改进了写法：

EVALUATE
NATURALINNERJOIN(
    ALL(
        'Breakdown'[Key],
        'Breakdown'[MainID],
        'Breakdown'[ReportM],
        'Breakdown'[Cost],
        'Breakdown'[AllID],
        'Breakdown'[AllTypeID]
    ),
    ALL(
        'Control'[Key],
        'Control'[IsPair],
        'Control'[No]
    )
)

上面的写法中，我只提取了需要用到的字段，并用ALL将它们包起来，ALL会去掉作用在这个表上的所有筛选器，按文章开头说法，这样写应该是没问题的，它们都有一个同名同数据类型的字段CostKey，但在DAX Studio运行时却报错：

未检测到公用联接列。联接函数“NATURALINNERJOIN”至少需要一个公用联接列。

于是想到，虽然factCosBreakdown和factCostControl已经建立了关联关系，但是这种写法可能导致它们的关系已经失效，它们现在就相当于两个无关联关系的表，于是尝试改进写法：

EVALUATE
NATURALINNERJOIN(
    SELECTCOLUMNS(
        ALL(
            'Breakdown'[Key],
            'Breakdown'[MainID],
            'Breakdown'[ReportM],
            'Breakdown'[Cost],
            'Breakdown'[AllID],
            'Breakdown'[AllTypeID]
        ),
        "CostKey",[Key],
        "MainID",[MainID],
        "ReportM",[ReportM],
        "Cost",[Cost],
        "AllID",[AllID],
        "AllTypeID",[AllTypeID]
    ),
    SELECTCOLUMNS(    
        ALL(
            'Control'[Key],
            'Control'[IsPair],
            'Control'[No]
        ),
        "Key",[Key],
        "IsPair",[IsPair],
        "No",[No]
    )
)

使用SELECTCOLUMNS函数将需要的字段都提取出来，还是报错：

检测到不兼容的联接列(''[CostKey])。'NATURALINNERJOIN' 不支持使用具有不同的数据类型或世系的联接。

这个提示很明白，说CostKey字段数据类型不一致，但是在Power BI Desktop中查看，CostKey确实都是文本类型，想不明白，直接给它们强制转换：

EVALUATE
NATURALINNERJOIN(
    SELECTCOLUMNS(
        ALL(
            'Breakdown'[Key],
            'Breakdown'[MainID],
            'Breakdown'[ReportM],
            'Breakdown'[Cost],
            'Breakdown'[AllID],
            'Breakdown'[AllTypeID]
        ),
        "Key",CONVERT([Key],STRING),
        "MainID",[MainID],
        "ReportM",[ReportM],
        "Cost",[Cost],
        "AllID",[AllID],
        "AllTypeID",[AllTypeID]
    ),
    SELECTCOLUMNS(    
        ALL(
            'Control'[Key],
            'Control'[IsPair],
            'Control'[No]
        ),
        "Key",CONVERT([Key],STRING),
        "IsPair",[IsPair],
        "No",[No]
    )
)

问题终于解决。

总结：

在两个无关系的表之间使用NATURALINNERJOIN时，可以通过强制转换它们的同名列数据类型以达到数据兼容的目的。