Power BI之DAX神功：第3卷第6回 笛卡兒積CROSSJOIN、GENERATE 和 GENERATEALL
《火力全開》中我只講了一個笛卡爾積CROSSJOIN，因為他能解決99%的笛卡兒積問題。
一、GENERATE函數兩張表的笛卡兒積
《DAX神功》第2卷第9回 我們做過這樣一個案例，計算每個人的銷售排名
【度量值】總銷售 = sum(?'表2'[銷售])
【度量值】排名 = RANKX ( GENERATE ( ALL ( '表1'[名稱] ), ALL ( '表2'[編碼2] ) ), [總銷售])
二、笛卡兒積并不是非用不可
我們也可以通過下面公式實現(xiàn)排名：
度量值 =?RANKX(calculatetable(SUMMARIZE('表2','表2'[編碼2],'表1'[名稱]),all('表1'[名稱])),[總銷售])
原理：
【1】SUMMARIZE('表2','表2'[編碼2],'表1'[名稱]) 取表2[編碼2]與表1[名稱]列去重后的臨時表
詳見《DAX神功》第1卷第15回
【2】名次不是通過行標題篩選出來的，而是V過來的
詳見《DAX神功》第2卷第5回至第9回? 我們證明了名次并不篩選出來的是V過來的
我的行標題上放表1[名稱]，所以我必須要取消(刪除)這個列的篩選功能
我要使用all('表1'[名稱])取消（刪除）指定列的篩選功能，就必須使用引擎，數值的引擎是Calculate，表的引擎是CalculateTable
CalculateTable(臨時表,all('表1'[名稱]))? ?// 返回的這張表與臨時表長像一樣，只是對表1名稱列刪除了篩選功能的表。
【3】最后使用RankX函數
RankX(CalculateTable(臨時表,all('表1'[名稱])),[總銷售])
PS:我這里只用了all('表1'[名稱])一個調節(jié)器，你可以再加上all('表2'[編碼2])，根據你自己的需求而定?？傊痪湓?，RankX函數它行標題上的列不能有篩選功能。這些原理在《DAX神功》第2卷第5回至第9回證明過。
這樣做的弊端就是速度問題，SUMMARIZE生成臨時表，CalculateTable篩選臨時表，現(xiàn)在是教學案例只有幾行，如果有幾十萬行速度還是有區(qū)別的。這時建議使用笛卡兒積，一次完成
三、GENERATE與CROSSJOIN函數的區(qū)別
新建表1 = GENERATE ('表1',RELATEDTABLE ('表2'))
新建表2 = GENERATE ('表1','表2')
新建表3 = CROSSJOIN ('表1','表2')
新建表1 =?GENERATE?('表1',RELATEDTABLE?('表2'))
新建表2 =?GENERATE?('表1','表2')
新建表3 =?CROSSJOIN?('表1','表2')
我們發(fā)現(xiàn)新建表2和新建表3結果是一樣的，CROSSJOIN支持多張表笛卡兒積，GENERATE支持兩張表笛卡兒積。
但是，我們觀察新建表1，結果與新建表2和新建表3不同，其實當你使用CalculateTable時結果同新建表1是一樣的
新建表4 = GENERATE ('表1',CALCULATETABLE( ('表2')))
Calculatetable和Calculate一樣都是將行上下文轉換成篩選上下文
RELATEDTABLE作用：一端找多端。一端和多端什么關系？多端可以被篩選，相當于將行上下文轉換成篩選上下文。
新建表1或新建表4生成方式不只這一種：我們換一種思路
我們發(fā)現(xiàn)，新建表2中，編碼1=編碼2? 就是新建表1或新建表4的樣子
所以我們也可以寫成：
新建表5 = filter(GENERATE ('表1','表2'),'表1'[編碼1]='表2'[編碼2])
// 同理我們使用CROSSJOIN函數結果相同
四、將笛卡兒積應用到數據沿襲優(yōu)勢明顯
// 《DAX神功》第2卷第21回 數據沿襲我們講過這個公式的原理，但是表不只兩張又怎么辦？
我們可以使用笛卡兒積代替Selectcolumns，使用CROSSJOIN的優(yōu)勢：可以一次將篩選器應用到多張表
// 至于CROSSJOIN中使用ALL、VALUES、DISTINCT的區(qū)別在這里的新建表中是看不到的。每個函數的區(qū)別我們在前面的課程中都有詳細介紹。例如你配合Calculate使用時，all和其它兩個函數就有區(qū)別了，VALUES和DISTINCT區(qū)別：是否滿足實時參照完整性返回空行的問題。
五、顯示空行的GENERATEALL函數
表2中日期不連續(xù)：沒有2021/1/3和2021/1/4
新建日期表：
【新建表】根據表2在表1中拿到名稱 =?SUMMARIZE?(RELATEDTABLE?('表2'),'表1'[名稱])
【新建表】不重復日期 = VALUES('日期表'[Date])
【新建表】generate = generate(VALUES('日期表'[Date]),SUMMARIZE (RELATEDTABLE ('表2'),'表1'[名稱]))
// 如果表2沒有相對應的日期，笛卡兒積之后會被忽略該日期。（隱藏空行）
// 不能直接用表1中的名稱列，因為表1中沒有日期
【新建表】generateall = generateall(VALUES('日期表'[Date]),SUMMARIZE (RELATEDTABLE ('表2'),'表1'[名稱]))
// generateall與generate相反，它顯示空行
孫興華講PowerBI火力全開》PowerBI必學課程
https://www.bilibili.com/video/BV1qa4y1H7wp
《DAX神功》文字版合集：
https://www.bilibili.com/read/readlist/rl442274
《DAX神功》視頻版合集：
https://www.bilibili.com/video/BV1YE411E7p3
PowerBI(DAX函數)、PowerQuery(M函數)、Python辦公自動化、Python爬蟲、Python數據分析、ExcelVBA、WordVBA、AccessVBA、MySQL等等
https://www.bilibili.com/read/cv10222110
