PowerBI:NATURALINNERJOIN的使用

查看官方的文档&＃xff0c;NATURALINNERJOIN描述是这样的&＃xff1a;

语法

NATURALINNERJOIN(, )

参数

返回值

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

备注

• 这些表在两个表的共有列&＃xff08;按名称&＃xff09;上联接。 如果两个表没有公共列名&＃xff0c;则返回错误。

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

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

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

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

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

总结了一下&＃xff0c;NATURALINNERJOIN主要运用在两个场景&＃xff1a;

1. 用于有关系的表

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

2. 用于无关系的表

借助DAX圣经里面的例子&＃xff1a;

EVALUATE
VAR A &＃61;
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 &＃61;
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执行后得到的结果为&＃xff1a;

圣经中还提到&＃xff1a;

用于无关系表的注意事项&＃xff1a;

NATURALINNERJOIN 通常用于无关系的两个表&＃xff0c;在这种情况下函数遵循以下规则&＃xff1a;

• 结果不保证排序顺序。

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

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

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

用于关系表的注意事项&＃xff1a;

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

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

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

圣经源地址&＃xff1a;https://www.powerbigeek.com/understanding-naturalinnerjoin/

遇到的问题

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

已知两个表&＃xff1a;factCostControl&＃xff0c;factCosBreakdown

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

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

EVALUATE
NATURALINNERJOIN(
    ALL(&＃39;factCosBreakdown&＃39;),
ALL(&＃39;factCostControl&＃39;)
)


或者&＃xff1a;

EVALUATE
NATURALINNERJOIN(
&＃39;factCosBreakdown&＃39;,
&＃39;factCostControl&＃39;
)

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

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

EVALUATE
NATURALINNERJOIN(
ALL(
&＃39;factCosBreakdown&＃39;[CostKey],
&＃39;factCosBreakdown&＃39;[MainProjectID],
&＃39;factCosBreakdown&＃39;[ReportMonth],
&＃39;factCosBreakdown&＃39;[CostType],
&＃39;factCosBreakdown&＃39;[AllocationProjectID],
&＃39;factCosBreakdown&＃39;[AllocationTypeID]
),
ALL(
&＃39;factCostControl&＃39;[CostKey],
&＃39;factCostControl&＃39;[IsPair],
&＃39;factCostControl&＃39;[ReportControlCentreNo]
)
)

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

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

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

EVALUATE
NATURALINNERJOIN(
SELECTCOLUMNS(
ALL(
&＃39;factCosBreakdown&＃39;[CostKey],
&＃39;factCosBreakdown&＃39;[MainProjectID],
&＃39;factCosBreakdown&＃39;[ReportMonth],
&＃39;factCosBreakdown&＃39;[CostType],
&＃39;factCosBreakdown&＃39;[AllocationProjectID],
&＃39;factCosBreakdown&＃39;[AllocationTypeID]
),
"CostKey",[CostKey],
"MainProjectID",[MainProjectID],
"ReportMonth",[ReportMonth],
"CostType",&＃39;factCosBreakdown&＃39;[CostType],
"AllocationProjectID",[AllocationProjectID],
"AllocationTypeID",[AllocationTypeID]
),
SELECTCOLUMNS(
ALL(
&＃39;factCostControl&＃39;[CostKey],
&＃39;factCostControl&＃39;[IsPair],
&＃39;factCostControl&＃39;[ReportControlCentreNo]
),
"CostKey",[CostKey],
"IsPair",[IsPair],
"ReportControlCentreNo",[ReportControlCentreNo]
)
)

使用SELECTCOLUMNS函数将需要的字段都提取出来&＃xff0c;还是报错&＃xff1a;

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

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

EVALUATE
NATURALINNERJOIN(
SELECTCOLUMNS(
ALL(
&＃39;factCosBreakdown&＃39;[CostKey],
&＃39;factCosBreakdown&＃39;[MainProjectID],
&＃39;factCosBreakdown&＃39;[ReportMonth],
&＃39;factCosBreakdown&＃39;[CostType],
&＃39;factCosBreakdown&＃39;[AllocationProjectID],
&＃39;factCosBreakdown&＃39;[AllocationTypeID]
),
"CostKey",CONVERT([CostKey],STRING),
"MainProjectID",[MainProjectID],
"ReportMonth",[ReportMonth],
"CostType",&＃39;factCosBreakdown&＃39;[CostType],
"AllocationProjectID",[AllocationProjectID],
"AllocationTypeID",[AllocationTypeID]
),
SELECTCOLUMNS(
ALL(
&＃39;factCostControl&＃39;[CostKey],
&＃39;factCostControl&＃39;[IsPair],
&＃39;factCostControl&＃39;[ReportControlCentreNo]
),
"CostKey",CONVERT([CostKey],STRING),
"IsPair",[IsPair],
"ReportControlCentreNo",[ReportControlCentreNo]
)
)

问题终于解决。

总结&＃xff1a;

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