PowerBI DAX 用 SUBSTITUTEWITHINDEX 為表增加索引
這是一個非常重要的技巧，重要到：涉及到性能優(yōu)化的重要技巧。
理論基礎(chǔ)
我們是給業(yè)務(wù)人員用白話來講解深刻的技術(shù)技巧的。作為業(yè)務(wù)，不必?fù)?dān)心。
在很多場景下，我們需要某個表是按照某列預(yù)先排過序的，這有很多好處。
也就是說：預(yù)先排序，是一個在計算中用到的重要技巧。
請先直接記住吧。
在數(shù)據(jù)庫的各種優(yōu)化中，加索引是一種特別常見而立竿見影的優(yōu)化方法。當(dāng)然，在做某些事情時，也需要增加索引。
動態(tài)增加索引
業(yè)務(wù)人員會問：為什么要在 DAX 中增加索引呢？為什么不預(yù)先在 Power Query 中增加一個索引呢？
這樣的問題表示：還沒有入門 DAX。
入門 DAX 后，會知道：計算總是在用戶選擇后發(fā)生的。
也就是說，如果計算中涉及到增加索引，那么應(yīng)該是在用戶選擇以后，那么這種在用戶選擇以后的計算，我們說它依賴于用戶的選擇，無法提前預(yù)知，所以稱為：動態(tài)的。
因此，DAX 必須提供一個函數(shù)來為用戶選擇以后的表來添加索引。
理解 SUBSTITUTEWITHINDEX
該函數(shù)顧名思義，用索引替代之用，更好的來解釋這個函數(shù)為：
SUBSTITUTE (X) WITHINDEX (Y)
也就是說，使用（Y）來替代（X）。
該函數(shù)并不直接解決任何業(yè)務(wù)問題，但卻是很多問題處理的中間手段。
在實際中，與替換元素為索引相比，為元素增加索引，更加常見。這里統(tǒng)一說明。
也就是說，有兩種場景定式，一定用該函數(shù)解決：
場景一：用數(shù)字索引替換某個已經(jīng)存在的列
場景二：為一個表增加一個數(shù)字索引列
用法
SUBSTITUTEWITHINDEX (主表，新增的索引列名，參考表，參考表 [某列] , 排序，... )
解釋：在主表中新增一列，增加的這列會替代主表與參考表的公有列，新增的列值將依據(jù)【參考表 [某列]】按【排序】方式來進(jìn)行。該函數(shù)的名稱和用法比較不易理解，需要參考示例再充分理解。
示例
對 SUBSTITUTEWITHINDEX 的使用場景分為兩種情況：
【場景 1】將元素替代為序號，元素重復(fù)則序號重復(fù)，替換后，元素不保留。
【場景 2】為元素新增加序號，元素重復(fù)則序號重復(fù)，替換后，元素要保留。
場景 1 - 將元素替代為序號
直接復(fù)制如下內(nèi)容，建立計算表。
T =
// 目的：將明細(xì)表的某列替換為索引，若該列有重復(fù)項，重復(fù)項的索引號相同。
// 構(gòu)建一個明細(xì)表，有重復(fù)項
VAR FactTable = SELECTCOLUMNS( { "D" , "A" , "C" , "B" , "B" , "A" , "D" , "D" } , "Item" , [Value] )
// 從明細(xì)表提出主表，元素唯一化
VAR MasterTable = DISTINCT( SELECTCOLUMNS( FactTable , "Item" , [Item] ) )
// 為明細(xì)表建立一個索引列替換在主表中出現(xiàn)的列，并按主表的列排序
RETURN SUBSTITUTEWITHINDEX( FactTable , "Index" , MasterTable , [Item] , ASC )
結(jié)果如下：
可見：原有的元素被替換為了序號。
場景 2 - 為元素新增序號
直接復(fù)制如下內(nèi)容，建立計算表。
T =
// 目的：為明細(xì)表按某列添加索引，若該列有重復(fù)項，重復(fù)項的索引號相同。
// 構(gòu)建一個明細(xì)表，有重復(fù)項
VAR FactTable = SELECTCOLUMNS( { "D" , "A" , "C" , "B" , "B" , "A" , "D" , "D" } , "Item" , [Value] )
// 為明細(xì)表添加 索引元素，與 原元素 相同
VAR FactWithIndex = ADDCOLUMNS( FactTable , "Index" , [Item] )
// 從明細(xì)表提出主表，元素唯一化
VAR MasterTable = DISTINCT( SELECTCOLUMNS( FactTable , "Index" , [Item] ) )
// 為明細(xì)表建立一個索引列替換明細(xì)表中在主表出現(xiàn)的列，并按主表的列排序
RETURN SUBSTITUTEWITHINDEX( FactWithIndex , "Index" , MasterTable , [Index] , ASC )效果如下：
可見：為原有的元素增加了一個索引列。
總結(jié)
SUBSTITUTEWITHINDEX 雖然用于添加了索引列，但其真正語義在于：替換，因此，精確地講，不是增加索引列，而是創(chuàng)建索引列并替換原有匹配的列。
在目前看來，SUBSTITUTEWITHINDEX 似乎什么都沒有做，但我們此前的一些重要算法和未來的重要算法都將基于這個函數(shù)來實現(xiàn)。
請業(yè)務(wù)人員記憶以下套路：
第一步：有一個明細(xì)表
第二步：從明細(xì)表抽出主表（參考表）
第三步：替換（SUBSTITUTE）明細(xì)表的列 X 為（WITH）索引號（INDEX）參照參考表進(jìn)行
在實際操作中有兩個定式：
一個是：替換為序號，不保留原來元素。
一個是：增加新序號，且保留原來元素。
讓數(shù)據(jù)真正成為你的力量
Create value through simple and easy with fun by PowerBI
Excel BI | DAX Pro | DAX 權(quán)威指南 | 線下VIP學(xué)習(xí)
掃碼與PBI精英一起學(xué)習(xí)，驗證碼：data2021
PowerBI MVP 帶你正確而高效地學(xué)習(xí) PowerBI
點(diǎn)擊“閱讀原文”，即刻開始
↙