PowerBI DAX 用 SUBSTITUTEWITHINDEX 为表增加索引

这是一个非常重要的技巧，重要到：涉及到性能优化的重要技巧。

理论基础

我们是给业务人员用白话来讲解深刻的技术技巧的。作为业务，不必担心。

在很多场景下，我们需要某个表是按照某列预先排过序的，这有很多好处。

也就是说：预先排序，是一个在计算中用到的重要技巧。

请先直接记住吧。

在数据库的各种优化中，加索引是一种特别常见而立竿见影的优化方法。当然，在做某些事情时，也需要增加索引。

动态增加索引

业务人员会问：为什么要在 DAX 中增加索引呢？为什么不预先在 Power Query 中增加一个索引呢？

这样的问题表示：还没有入门 DAX。

入门 DAX 后，会知道：计算总是在用户选择后发生的。

也就是说，如果计算中涉及到增加索引，那么应该是在用户选择以后，那么这种在用户选择以后的计算，我们说它依赖于用户的选择，无法提前预知，所以称为：动态的。

因此，DAX 必须提供一个函数来为用户选择以后的表来添加索引。

理解 SUBSTITUTEWITHINDEX

该函数顾名思义，用索引替代之用，更好的来解释这个函数为：

SUBSTITUTE (X) WITHINDEX (Y)

也就是说，使用（Y）来替代（X）。

该函数并不直接解决任何业务问题，但却是很多问题处理的中间手段。

在实际中，与替换元素为索引相比，为元素增加索引，更加常见。这里统一说明。

也就是说，有两种场景定式，一定用该函数解决：

场景一：用数字索引替换某个已经存在的列

场景二：为一个表增加一个数字索引列

用法

SUBSTITUTEWITHINDEX (主表，新增的索引列名，参考表，参考表 [某列] , 排序，... )

解释：在主表中新增一列，增加的这列会替代主表与参考表的公有列，新增的列值将依据【参考表 [某列]】按【排序】方式来进行。该函数的名称和用法比较不易理解，需要参考示例再充分理解。

示例

对 SUBSTITUTEWITHINDEX 的使用场景分为两种情况：

• 【场景 1】将元素替代为序号，元素重复则序号重复，替换后，元素不保留。
• 【场景 2】为元素新增加序号，元素重复则序号重复，替换后，元素要保留。

场景 1 - 将元素替代为序号

直接复制如下内容，建立计算表。

T = 

// 目的：将明细表的某列替换为索引，若该列有重复项，重复项的索引号相同。

// 构建一个明细表，有重复项
VAR FactTable = SELECTCOLUMNS( { "D" , "A" , "C" , "B" , "B" , "A" , "D" , "D" } , "Item" , [Value] )

// 从明细表提出主表，元素唯一化
VAR MasterTable = DISTINCT( SELECTCOLUMNS( FactTable , "Item" , [Item] ) )

// 为明细表建立一个索引列替换在主表中出现的列，并按主表的列排序
RETURN SUBSTITUTEWITHINDEX( FactTable , "Index" , MasterTable , [Item] , ASC )
    

结果如下：

可见：原有的元素被替换为了序号。

场景 2 - 为元素新增序号

直接复制如下内容，建立计算表。

T = 

// 目的：为明细表按某列添加索引，若该列有重复项，重复项的索引号相同。

// 构建一个明细表，有重复项
VAR FactTable = SELECTCOLUMNS( { "D" , "A" , "C" , "B" , "B" , "A" , "D" , "D" } , "Item" , [Value] )

// 为明细表添加 索引元素，与 原元素 相同
VAR FactWithIndex = ADDCOLUMNS( FactTable , "Index" , [Item] )

// 从明细表提出主表，元素唯一化
VAR MasterTable = DISTINCT( SELECTCOLUMNS( FactTable , "Index" , [Item] ) )

// 为明细表建立一个索引列替换明细表中在主表出现的列，并按主表的列排序
RETURN SUBSTITUTEWITHINDEX( FactWithIndex , "Index" , MasterTable , [Index] , ASC )

效果如下：

可见：为原有的元素增加了一个索引列。

总结

SUBSTITUTEWITHINDEX 虽然用于添加了索引列，但其真正语义在于：替换，因此，精确地讲，不是增加索引列，而是创建索引列并替换原有匹配的列。

在目前看来，SUBSTITUTEWITHINDEX 似乎什么都没有做，但我们此前的一些重要算法和未来的重要算法都将基于这个函数来实现。

请业务人员记忆以下套路：

第一步：有一个明细表

第二步：从明细表抽出主表（参考表）

第三步：替换（SUBSTITUTE）明细表的列 X 为（WITH）索引号（INDEX）参照参考表进行

在实际操作中有两个定式：

一个是：替换为序号，不保留原来元素。

一个是：增加新序号，且保留原来元素。

这个函数有多强大，参考：【无敌】PowerBI 终极算法性能优化 最强版

如果你想变得更强大，参考：BI真经 - 让数据真正成为你的力量