函数周期表丨筛选丨表丨SUBSTITUTEWITHINDEX

SUBSTITUTEWITHINDEX函数

SUBSTITUTEWITHINDEX函数是一个非常高阶的函数。

隶属“筛选”类函数，属于“表函数”，高阶使用方法相对而言烧脑一些。

用途：

1、用来提取维度，添加索引；

2、某些特定的问题添加数字索引可以转化为数学计算问题的方式。

出处注明：

注意： 用途2，是@大海老师提出来的概念，并不是白茶想到的，这里白茶只是阐述一下高阶版用途。 致谢： 感谢@焦棚子、@大海、@嘿、@冬、@刘钰，五位大佬的提点； 感谢@M与DAX的恩怨纠葛群里的各位大佬，给你们添麻烦了。 以上排名不分先后，还有很多未提及的大佬，仅此感念各位大佬百忙之中抽出时间引导白茶，万分感谢

高级用途传送门丨@焦棚子老师的具体案例：

https://jiaopengzi.com/392.html

注明：此链接来源于@焦棚子老师的网站。

语法

DAX=
SUBSTITUTEWITHINDEX(<主表>, <索引列名>, <参照表>, [<参照表[列]>, [<排序>][, <参照表[列]>, [<排序>]]…])

参数

主表：通常代指事实表，也可以指被添加索引的表。

索引列名：为即将添加的索引列命名。

参照表：与主表有关系的参考表，通常指的是维度表。

参照表[列]：与主表所共有的列，通常是模型关系中的一端。

排序：调整索引列的排序方式。DESC/ASC。

返回结果

返回一张表，其中共有列会被替换成索引列。

例子

模拟数据：

这是白茶随机模拟的数据。

例子1：

原本，添加维度的方法是利用VALUES、DISTINCT或者ALL函数。

维度 =
DISTINCT ( '销售明细'[商品名称] )

这样的话做出来的维度没有索引，也就是并没有一个让中文字段排序的列，这样其实很麻烦的。倒是可以通过添加计算列，利用EARLIER函数处理，但是略显繁琐了。

现在假定事实表没有相关索引的列，但是我们做出来的维度要索引，该怎么进行呢？

例子1 =
VAR BA =
    SELECTCOLUMNS ( DISTINCT ( '销售明细'[商品名称] ), "商品", [商品名称], "品名", [商品名称] )
VAR INDEX =
    SELECTCOLUMNS ( DISTINCT ( BA ), "商品", [商品] )
RETURN
    SUBSTITUTEWITHINDEX ( BA, "索引", INDEX, [商品], ASC )

结果如下：

解释一下含义： 在BA虚拟表中，利用DISTINCT函数，提取事实表中的[商品名称]，去掉重复项目； SELECTCOLUMNS函数将[商品名称]这一列复制成两份，分别进行命名。 INDEX虚拟表中，提取BA表中的一列，去掉重复项，作为维度参考。 最后，利用SUBSTITUTEWITHINDEX函数，替换掉了两个虚拟表中的共有列[商品]，输出结果为[品名列]和[索引列]。

这样的话我们就绕过PQ，不需要去PQ编辑界面添加索引，仅在DAX中完成了维度提取，添加索引的操作。

例子2：

将我们刚才生成的维度表与事实表建立联系。

例子2 =
SUBSTITUTEWITHINDEX ( '销售明细', "索引", '例子1', [品名], DESC )

可以将事实表中的数据，按照维度表中的顺序添加索引列。

例子3：

将我们之前生成的维度表与例子1建立联系。

例子3 =
SUBSTITUTEWITHINDEX ( '例子1', "索引", '维度', [商品名称], DESC )

输出结果是将主表[维度表]中的[商品名称]替换掉，换成[例子1]中商品的对应顺序的数字，这种用法，是原生的基础用法，也是一开始白茶疑惑的地方。

因为按照咱中国式习惯，我添加了索引，那么原本的主键应该是保留的，但是函数本身并没有保留的操作，白茶一度以为这个函数很鸡肋。

后来冬哥说了，可以多复制几列主键，原来限制这个函数的，是白茶的思维，局限了。

例子4：

关于例子4，其实没有实例，这一部分内容是白茶对于大海老师的那句话进行的思考。

通过例子2中的代码，加上大海老师的提点，白茶隐约有点明白了，可能有些时候，我们无法直接对共有维度进行计算，但是有一些需求又恰好需要这方面，这时候问题来了，我想计算维度，但是文字是无法计算的，怎么办？这个时候SUBSTITUTEWITHINDEX的作用就得到了完美的体现。

为维度，添加一个索引，我们无法对文字进行聚合运算，但是可以对数字进行计算！这样的话可以将问题转化为数学的逻辑问题，很多问题也就迎刃而解了。

以后遇到合适的例子，白茶可能会对SUBSTITUTEWITHINDEX函数进行补充，本期就到这里了，再次感谢各位大佬对白茶的提点，谢谢。

小伙伴们❤GET了么？

白茶会不定期的分享一些函数卡片

（文件在知识星球[PowerBI丨需求圈]）

这里是白茶，一个PowerBI的初学者。