聲明：父子層級(jí)結(jié)構(gòu)難點(diǎn)不在于DAX函數(shù)，在于表格的數(shù)據(jù)清洗，洗不出來(lái)就很難做出來(lái)。99%的情況下是不需要使用度量值生成父子層級(jí)結(jié)構(gòu)的。直接在矩陣上用鼠標(biāo)拖拽就可以滿足大部分需求，只有精益求精以及特殊需求才會(huì)用到。

一、理解層級(jí)結(jié)構(gòu)

不同的表格會(huì)有不同的深度，從單位到家庭，都有它自己的層級(jí)，數(shù)量均不相同。例如：左圖有3個(gè)層級(jí)，右圖有2個(gè)層級(jí)。我們以左圖為例，做出如下表格：

// 爸爸和叔叔的上一級(jí)是爺爺，小明和小紅上一級(jí)是爸爸，叔叔還沒有孩子

二、制作層級(jí)序號(hào)

眾所周知，PowerBI中有DAX和M兩種語(yǔ)言，M函數(shù)負(fù)責(zé)數(shù)據(jù)清洗，DAX負(fù)責(zé)建模分析。而這件事情，就應(yīng)該在導(dǎo)入數(shù)據(jù)時(shí)先進(jìn)入PowerBI中的PowerQuery模塊進(jìn)行數(shù)據(jù)清洗。

視頻名稱是： 《孫興華 PowerQuery M函數(shù)》全107集

https://www.bilibili.com/video/BV1oa4y1j75e

現(xiàn)在我們使用模擬數(shù)據(jù)：為什么說(shuō)它是模擬數(shù)據(jù)，因?yàn)檎鎸?shí)數(shù)據(jù)肯定有大類編碼、中類編碼、商品編碼等。

我們想將模擬數(shù)據(jù)表標(biāo)注層級(jí)關(guān)系，如下圖所示：將原表變成目標(biāo)表

PowerQuery M函數(shù)? 代碼如下：方法不唯一

Ps: 這里只是舉例，不同的表格有不同的處理方法，而且方法有很多種。如果你沒有學(xué)過(guò)我講的PowerQuery課程，不易理解上面的代碼。建議系統(tǒng)學(xué)習(xí)。

三、PATH函數(shù)

2021年11月2日我在《Power BI DAX 論數(shù)據(jù)清洗的重要性》視頻中首次使用

https://www.bilibili.com/video/BV13R4y1E774?spm_id_from=333.999.0.0

語(yǔ)法：PATH ( <當(dāng)前行序號(hào)>, <父級(jí)序號(hào)> )? ? 作用：返回路徑

【新建列】路徑 = PATH('Sheet3'[序號(hào)],Sheet3[父級(jí)])

就個(gè)函數(shù)就好比，有一個(gè)文件：孫興華.txt? 我想知道文件的絕對(duì)路徑：

C:\免費(fèi)講課\喜歡nueco\酷愛dbykt\孫興華.txt

解讀:

第1層：C盤根目錄

第2層：文件夾：免費(fèi)講課

第3層：文件夾：喜歡nueco

第4層：文件夾：酷愛dbykt

第5層：文件：孫興華.txt

四、PATHITEM函數(shù)

語(yǔ)法：PATHITEM ( 路徑, 位置, [返回類型])? ? // 從左向右

語(yǔ)法：PATHITEMREVERSE?( 路徑, 位置, [返回類型])???? // 從右向左

例如:? PATHITEM ( "1|3|5",?3)? ?// 返回：5

例如:??PATHITEMREVERSE ( "1|3|5",?3)? ?//?返回：1

常用返回類型：

整數(shù) (Integer)、十進(jìn)制數(shù) (Float)、貨幣 (Currency)、日期 (DateTime)、布爾值 (TRUE/FALSE)、文本 (String)

【新建列】第1級(jí) = LOOKUPVALUE('Sheet3'[名稱],Sheet3[序號(hào)],PATHITEM('Sheet3'[路徑],1,INTEGER))?

【新建列】第2級(jí) = LOOKUPVALUE('Sheet3'[名稱],Sheet3[序號(hào)],PATHITEM('Sheet3'[路徑],2,INTEGER))?

【新建列】第3級(jí) = LOOKUPVALUE('Sheet3'[名稱],Sheet3[序號(hào)],PATHITEM('Sheet3'[路徑],3,INTEGER))?

//《火力全開》第2課 重點(diǎn)講了LOOKUPVALUE

// 語(yǔ)法：Lookupvalue(把哪張表的哪個(gè)列拿過(guò)來(lái)，找那張表上的誰(shuí)？，找自己表里的誰(shuí)？)

如果只有3列，你就按上面的方法做就好了，如果有100列，你要新建100列嗎？我建議，將數(shù)據(jù)復(fù)制到Excel中使用PowerQuery分成N列后再回到PowerBI中

注意：不能從PowerBI進(jìn)入PowerQuery因?yàn)槁窂竭@一列是你在DAX中新建的列，PowerBI中的PQ不識(shí)別。以下代碼可以滿足不同需求使用

Power Query M函數(shù)：不填充

PowerQuery M函數(shù) ：填充

友情提示：如果沒有看過(guò)我的PowerQuery 107集合集，很難看懂M函數(shù)的代碼

五、PATHLENGTH 函數(shù)

語(yǔ)法：PATHLENGTH ( 路徑)? ? ?返回：路徑有幾層

六、PATHCONTAINS ( 路徑, 查找項(xiàng)目?)

七、我們使用真實(shí)案例

【1】我們將商品表處理成目標(biāo)表（帶有本級(jí)序號(hào)和父級(jí)序號(hào)）

PowerQuery M函數(shù)代碼如下：

【2】處理銷售表，讓銷售表匹配商品表中的序號(hào)

實(shí)現(xiàn)商品表序號(hào)是主鍵，銷售表序號(hào)是外鍵

商品表與銷售表做合并查詢，實(shí)現(xiàn)下面的樣子：

【3】建立關(guān)系（一對(duì)多關(guān)系）

現(xiàn)在可以實(shí)現(xiàn)連線關(guān)系了

【4】對(duì)商品表新建列

函數(shù)我們?cè)谏厦娑冀榻B過(guò)了

【新建列】路徑 = PATH('商品表'[序號(hào)],'商品表'[父級(jí)序號(hào)])

【新建列】第1級(jí) = LOOKUPVALUE('商品表'[商品名稱],'商品表'[序號(hào)],PATHITEM('商品表'[路徑],1,INTEGER))

【新建列】第2級(jí) = LOOKUPVALUE('商品表'[商品名稱],'商品表'[序號(hào)],PATHITEM('商品表'[路徑],2,INTEGER))

【新建列】第3級(jí) = LOOKUPVALUE('商品表'[商品名稱],'商品表'[序號(hào)],PATHITEM('商品表'[路徑],3,INTEGER))

?友情提示：序號(hào)和父級(jí)序號(hào)數(shù)據(jù)類型必需是整數(shù)

如果你看著第2級(jí)和第3級(jí)為空不舒服，可以通過(guò)公式補(bǔ)全他。

用DAX函數(shù)處理方法，修改第2級(jí)和第3級(jí)新建列公式：

有兩個(gè)思路任選其一：

思路1：if(PATHITEM(路徑,2)=Blank(),第1級(jí),第2級(jí))

// 如果路徑第2層是空的，就返回第1級(jí)，否則返回第2級(jí)

思路2：if(PATHLENGTH(路徑)=1,第1級(jí),第2級(jí))

// 如果路徑的層級(jí)是1，就返回第1級(jí)，否則返回第2級(jí)

我們以學(xué)習(xí)為目的，將2個(gè)思路分別應(yīng)用于第2級(jí)和第3級(jí)，效果相同，你任選其一

【新建列】第2級(jí) = if(PATHITEM([路徑],2)=Blank(),'商品表'[第1級(jí)],LOOKUPVALUE('商品表'[商品名稱],'商品表'[序號(hào)],PATHITEM('商品表'[路徑],2,INTEGER)))

【新建列】第3級(jí) = if(PATHLENGTH('商品表'[路徑])<=2,'商品表'[第2級(jí)],LOOKUPVALUE('商品表'[商品名稱],'商品表'[序號(hào)],PATHITEM('商品表'[路徑],3,INTEGER)))

忠告：沒有作弊的公式，只能不停鍛煉的大腦，思考最重要。

【5】制作層級(jí)關(guān)系

【6】放到矩陣

向下鉆?。何覀?cè)凇陡鴮O興華學(xué)習(xí)數(shù)據(jù)分析第五季》圖表篇中講過(guò)

我們發(fā)現(xiàn)紅框內(nèi)的顯示是多余的，如果不是為了去掉它們，我們根本沒必要這么麻煩，搞什么層級(jí)結(jié)構(gòu)

【7】在商品表中新建列

【新建列】有幾層 = PATHLENGTH('商品表'[路徑])?

// 函數(shù)我們?cè)谏厦嬷v過(guò)了，返回路徑有幾層

【8】找規(guī)律

【度量值】

第1層是否被篩選 = ISFILTERED('商品表'[第1級(jí)])?

第2層是否被篩選 = ISFILTERED('商品表'[第2級(jí)])?

第3層是否被篩選 = ISFILTERED('商品表'[第3級(jí)])?

篩選深度 = [第1層是否被篩選]+[第2層是否被篩選]+[第3層是否被篩選]

// True是1，F(xiàn)alse是0，加在一起，看看篩選了幾個(gè)

層次深度 = max('商品表'[有幾層])

總銷量 = sum('銷售表'[銷量])

【9】解決問(wèn)題

我們找到上圖的兩個(gè)規(guī)律，問(wèn)題就解決了

【度量值】篩選等于最大層級(jí)返回空 = if([篩選深度]=[層次深度],BLANK(),[總銷量])

現(xiàn)在我們的目的是，返回普通黃瓜、秋黃瓜、國(guó)產(chǎn)土豆、荷蘭土豆對(duì)應(yīng)的值，而不返回子級(jí)蘋果、西瓜、香蕉對(duì)應(yīng)的值。

以下兩種新建列的方法，都可以使用，在商品表中新建列

// VAR 指定某列時(shí)，代表當(dāng)前行指定單元格的值，詳見《DAX神功》第1卷第14回

【度量值】第1種方法唯一值 = SELECTEDVALUE('商品表'[第一種方法])

// 《DAX神功》第2卷第19回 關(guān)于SELECTEDVALUE函數(shù)的原理

【度量值】滿足兩個(gè)條件 = if([篩選深度]=[層次深度] && [第1種方法唯一值],[總銷量])

// 這樣就返回我們需要的層級(jí)結(jié)果

我們將剛才用過(guò)的兩個(gè)公式寫成一個(gè)：

篩選等于最大層級(jí)返回空 = if([篩選深度]=[層次深度],BLANK(),[總銷量])

滿足兩個(gè)條件 = if([篩選深度]=[層次深度] && [第1種方法唯一值],[總銷量])

寫成

結(jié)果 = if([篩選深度]=[層次深度] && [第1種方法唯一值],[總銷量],if([篩選深度]=[層次深度],BLANK(),[總銷量]))

終于成功了！真累！

《孫興華講PowerBI火力全開》PowerBI必學(xué)課程

https://www.bilibili.com/video/BV1qa4y1H7wp

《DAX神功》文字版合集：

https://www.bilibili.com/read/readlist/rl442274

《DAX神功》視頻版合集：

https://www.bilibili.com/video/BV1YE411E7p3

PowerBI(DAX函數(shù))、PowerQuery(M函數(shù))、Python辦公自動(dòng)化、Python爬蟲、Python數(shù)據(jù)分析、ExcelVBA、WordVBA、AccessVBA、MySQL等等

https://www.bilibili.com/read/cv10222110?