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 )