關于 “按列排序” 兩個列的“坑”問題

Power零售數(shù)據(jù)BI 整理

2018.5.17

? ? ? 依據(jù)SQLBI最新的一篇文章，我找翻譯軟件翻譯了一下仇轻，稍做修改难述，能大致看懂就行较木。然后侵状，再作了點進一步的解釋。并附帶了解RANKS函數(shù)在此問題下的舉例正罢。
? ? ? 感謝官方文獻阵漏。 以下是原文的簡體版（可能與原文有出于）：

? ? ? 標題：當“按列排序”列處于活動狀態(tài)時，在Power BI中需要刪除該列上存在的篩選器。

? ? ? ? Power BI用戶在編寫DAX代碼時履怯，一個經(jīng)常遇到的問題是：當某個被指定為’按列排序”后的列處于活動狀態(tài)時回还，刪除該列的過濾器（比如使用ALL函數(shù)）可能會有意想不到的結(jié)果。 起先叹洲，在一篇與RANKX有關的文章中談到了這個話題柠硕，但在那里，該問題并沒有做過多介紹疹味。所以仅叫，我認為最好是有一個專門的博客文章來說明它。事情是這樣的：假如有一下這樣的公式：

% ofYearWrong =DIVIDE(
[Sales Amount],
CALCULATE([Sales[Amount],
ALL('Date'[Month Name])))

? ? ? ? ? 如圖糙捺，這就產(chǎn)生了對下面透視表中年度百分比的錯誤計算:

? ? ? 原因是：[Month Name]列的屬性是按[Month]“按序排列”后的列 ——也就是透視表的行值。這樣做笙隙，避免四月作為一年中的第一個月（如果以原數(shù)據(jù)的默認方式洪灯，將是按字母排列，四月應該在最前面）竟痰。
? ? ? 如果使用Excel作為輸出签钩，這不是問題。但在Power BI中坏快，必須修改[Month Name]列上過濾器的篩選條件铅檩，即在ALL函數(shù)中應同時包括[Month ]列。

這是正確的公式：

% of Year Correct = DIVIDE(
[Sales Amount],
CALCULATE( [Sales Amount],
ALL('Date'[Month Name],'Date'[Month])))

? ? ? ? 原文中使用了? SUMMARIZECOLUMNS（）來驗證莽鸿，大致的意思是說：在透視表里將[Month ]列放置在行時昧旨，相當于使用SUMMARIZECOLUMNS（）在該列上做分組行（處理成唯一值的行，然后將當前行排序后計算）處理祥得，即相當于作了排序動作兔沃。但是，由于時期表里存在[Month]列级及，該列是[Month Name]列指定為按序依據(jù)的原列表乒疏。即[Month Name]列在數(shù)據(jù)模型里存在指定的排序列。如下圖：

? ? ? 這時候饮焦，無論以哪種排序方式處理該列后怕吴，即相當于SUMMARIZECOLUMNS（）作用在兩個列上，需要同時使用ALL（）來刪除這兩個列表上的篩選县踢。即修改為原來的第二個公式：

% of Year Correct = DIVIDE (
[Sales Amount],
CALCULATE ([SalesAmount],
ALL ( 'Date'[MonthName], 'Date'[Month] ) ))

? ? ? ? 請注意：在Excel中不具有此問題, 因為它依賴于 MDX, 并不更改查詢的語義, 并自動將自然順序應用于該字段列转绷。不需要關注這一點。

? ? 其實殿雪，只需要記住一句話暇咆，也就是原文里的結(jié)論：
? ? 經(jīng)驗法則是：當你需要從某個“按列排序”列中移除篩選器時，它的列屬性可能被引用到另一個列，應該在ALL語句中同時包含該列——否則爸业，ALL函數(shù)在計算中可能不會達到預期的效果其骄。

? ? ? 其實問題已經(jīng)結(jié)束，但原因呢扯旷？好像并沒有講清拯爽。也就是，我們再加了個問題：

為什么需要這樣處理呢钧忽？

? ? ? 文章中提到RANKS示例毯炮，我們找到這篇文章，然后也放在這里耸黑，作統(tǒng)一的一次性說明：并將它處理成一個普遍的問題桃煎？ 試試也是可以的。

一大刊、我們做第一次为迈、第一步的了解：

? ? ? 模擬一個數(shù)據(jù)模型表，包含三列缺菌，分別是：Values葫辐、Name、State伴郁，其中Name列為唯一值列表耿战。

? ? ? 我們使用[ Sum Values]度量來對[Name]列執(zhí)行排序：

結(jié)果顯示：

? ? ? ? 這里創(chuàng)建的是一個度量值, 而不是使用計算列（有利于本例說明）。因為希望根據(jù)當前篩選器顯示前三名 (例如,焊傅，可以篩選狀態(tài)列實現(xiàn))剂陡，利用 "Power BI" 中的表和行列隱藏在所有度量值顯示中返回空白的行的事實記錄,，使用如下度量值獲得所需的結(jié)果:

? ? [Top 3 Cases] := IF(? [Rank Name Simple]<= 3,? [SumValue]）

獲得結(jié)果:

? ? ? 但是, 注意到透視表中的度量值并不是按希望的大小自然順序排列的租冠。本例中, 因為只有三列鹏倘，比較簡單，將適當?shù)淖匀豁樞蛑苯討糜趫蟊碇械?列了顽爹，但在更復雜的情況下,纤泵，要求可能會有所不同,，自然順序需要由表中的另一列來定義它镜粤。
? ? ? ? 在此示例中, 我們希望通過使用同一表里的"Value" 列以升序方式對 Name 列進行排序捏题。因此, 我們將 "按列排序" 應用于 " Name " 列。方法見前面的圖示肉渴。? 這時公荧，結(jié)果顯示了所有的Name, 而不僅僅是前3個：

? ? 為了更好觀察這種行為的原因, 我們也同時顯示Rank Name Simple度量：

? ? ? ? 這說明，"按列排序" 設置似乎會破壞 RANKX 的行為（行的行為）同规。這似乎是一個 bug循狰，事實并非如此窟社。Rank Name Simple度量值由兩個列所迭代：列表計算的Name 和Value列，這兩列分別在所創(chuàng)建的行篩選中計算绪钥。無論應用于表的列是哪一個灿里，都將發(fā)生這種情況。當然程腹，我們在度量計算之前匣吊，在報告中使用的是其中的[Name ]列。

? ? ? 因此寸潦， 當在報表中包含“按列排序”中的其中一列時色鸳，則 "PowerBI" 也會在同一DAX 查詢中包含相應的另一個排序列。對于任何聚合來說见转，這通常不是問題命雀，因為這兩個列具有相同的粒度級別（月級別）。
? ? ? ? 但是池户，當在報表中包含的列 (示例中的Name列) 上定義了一個迭代器（行篩選）時, 以及在這樣的迭代器 (比如RANKX 函數(shù))發(fā)生的當前篩選轉(zhuǎn)換 (度量[Sum Value])行中附加有排序列 (Value) 的存在時咏雌，計算度量值的當前篩選將生成一個列表篩選，其中包含對由 RANKX 迭代的所有行的相同篩選器校焦。
? ? ? 在將 RANKX 應用于其中一個列時，可以通過將 "排序" 列包括在內(nèi)來避免此問題统倒。例如寨典，可以編寫:

[Rank Name] := IF(HASONEVALUE(Test[Name] ),
? ? ? ? ? ? ? ? ? ? ? ? RANKX(ALL( Test[Name], Test[Value] ), [Sum Value]))

? ? ? 通過這種方式，確保 RANKX 中的[Sum Value]度量生成的篩選轉(zhuǎn)換房匆，覆蓋由Power BI查詢中生成的Name和Value列的列表篩選耸成。 同時刪除所有不必要的度量，這將正確地顯示出前三名：

? ? ? ? 這里浴鸿，唯一的問題是井氢，只有Total總計并不是針對可見行求和 (結(jié)果應該是240)，這是因為最初的[Top 3 Cases]度量不檢查單個[Name]列的選擇岳链。如果需要刪除這樣的總計, 可使用以下度量值 (如果是使用較大數(shù)據(jù)花竞，受性能影響，建議最好去掉可見行的總計)掸哑。

[Top 3Cases]:=? ? IF(HASONEVALUE(Test[Name] ),? ? IF(? [Rank Name Simple]<=3,[SumValue]? ))

? ? ? 如果使用 BI, 請記住, 對數(shù)據(jù)模型應用 "按列排序" 條件可能會破壞現(xiàn)有的度量值约急。應該知道，在物理表上的迭代器不應受此問題的影響苗分，但表的粒度可能不是需要的正確計算厌蔽，而且從性能角度來看，迭代表時的列表轉(zhuǎn)換可能更昂貴摔癣。
? ? ? ? 在使用迭代器在一個或多個列上生成 DAX 度量值之前奴饮，我們可以考慮在數(shù)據(jù)模型中設置“按列排序”纬向，這是最佳做法。應用 "按列排序" 設置時戴卜，應驗證該列是否在某些迭代器中被使用逾条，以及這是否會影響該度量值的計算。

? ? ? 說真的叉瘩，我每次看這些篩選轉(zhuǎn)換膳帕、更改之類的文章，總有一種似懂非懂的感覺薇缅，一種言猶未盡的感覺……危彩。那么，讓我們試著繼續(xù)泳桦。

? ? ? 二汤徽、我們做第二次、進一步的了解：

? ? ? 1灸撰、其實這個案例比較特別谒府。也算是列表關系里的一個“坑”。
? ? ? 我們知道浮毯，“按列排序”只可能發(fā)生在同一個模型表里完疫，即使該表中唯一值最大的列表有時不止一個，但作為活動物理關系列的只有一個债蓝。上例中因為只使用了簡單的三個列壳鹤，如果是復雜點的更多列表的數(shù)據(jù)模型，比如可能真正的關系列表是在公式中并沒有出現(xiàn)的[Date]列饰迹。因此芳誓，我們說：在這個表里，其他列都是通過[Date]連接為數(shù)據(jù)模型列表（組成本身所在的這個表或與其他表的列表連接成擴展表）啊鸭。?
? ? ? ? 按理說锹淌，[Month Name] 與[Month ]都不是該表的關系列表，只能被動的被關系列表分別引用赠制。而且關系不能同時作用于這兩個列上赂摆。它們之間也應該互不影響才對。問題的關鍵只可能出現(xiàn)在“按列排序”上憎妙。
? ? ? 第一個公式中库正，我們通過ALL( )去掉了[Month Name]列表上所有關系（因為要將它放置在透視表的行里，并希望不受該列上所有其他篩選器影響厘唾，而計算當前月的度量）褥符，按照正常思路，這應該不會計算錯誤抚垃，但結(jié)果出于我們的意料喷楣。

? ? ? 2趟大、這時候我們通常的想法都是檢查公式有沒有錯誤。問題來了铣焊，天空飄下四個字：如何檢查逊朽？
? ? ? ? 其實，通過前面官方的介紹曲伊，“按列排序”的原理應該是列表結(jié)構的一部分：即在計算中成為了關系列表叽讳。我們馬上想到：能夠轉(zhuǎn)換列表結(jié)構供內(nèi)部引擎計算的只有CALCULATE函數(shù)（以及CALCULATEDTABIE函數(shù)）。
? ? ? ? 前面我們討論過：任何一個CALCULATE ( ) 其實都是引擎執(zhí)行下的一個附加（虛擬）列表模型坟募，是提供給內(nèi)部引擎計算的列表結(jié)構岛蚤。CALCULATE ( ) 的結(jié)果錯了，首先要檢查的就是這個計算需要的列表結(jié)構Ｐ概础涤妒！它包括列表關系以及列表屬性層次結(jié)構等。由于列表關系本身就是一種數(shù)據(jù)結(jié)構赚哗，這里我們檢查的就是該列表關系她紫。
? ? ? 因此，換句話說： 任何DAX計算錯誤屿储，首先檢查的總是列表關系贿讹！(很多人總是從檢查自已的公式條件入手，而忽說了關系的檢查)够掠。

? ? ? 而且围详，應該記住：有時候祖屏，我們是需要某個列表關系來計算，反過來买羞，有時候恰恰是不需要某個列表關系來計算（例如ALL系列：去掉列表關系袁勺，透視表里希望不顯示當前沒有度量計算的行等）。
? ? ? 本例中畜普，我們使用ALL([Month Name] )去掉了該列表上所有關系期丰，但是，該關系結(jié)構表里還有一個具備相同唯一值（基數(shù)）的另一個列[Month]（而且同粒度）吃挑，因為具有相同基數(shù)钝荡，引擎內(nèi)部通過關系列表找到這兩列中的任何一列來構建計算列表都是一樣的結(jié)果。也就是說舶衬，ALL([Month Name])之后埠通，關系列表（比如[Date]列）通過關系傳遞，會找到[Month]列來構建相同的關系列表來參與計算逛犹，效果是一樣的端辱！
? ? ? ? 這時候梁剔，你可以理解為：第一個公式里，通過ALL([Month Name])顯式處理了該列表的關系后舞蔽，還存在一個由[Month]列接管的荣病、能產(chǎn)生同樣效果的隱式關系。所以必須要一同去掉（有可能還有其他這樣的列）渗柿。
? ? ? 當然个盆，所有這一切都發(fā)生在當前的計算中，可理解它為一種隱式虛擬關系朵栖，“按列排序”的兩個列并不存在實際意義上的物理關系颊亮，所以，在物理表里的計算并不會受影響混槐。
? ? ? 理解了以上這些编兄，如是就有了前面的第二個正確的公式：

% of Year Correct =DIVIDE (
[Sales Amount],
CALCULATE ([SalesAmount],
ALL ( 'Date'[MonthName], 'Date'[Month] ) ))

? 3、結(jié)論

? ? （1）當計算中使用了“按列排序”的兩個列中任何一個列時声登，可能會出現(xiàn)隱式的虛擬關系狠鸳；?

? ? ? （2）再次重復之前說過的一句話：很多時候，我們可以以某種方式或某個自己能把握的概念來達到理解DAX的目的（官方也推出過很多通俗易懂的概念）悯嗓，但DAX最終的內(nèi)部核心卻一直是一個專業(yè)的領域件舵。一句話，你可以用這樣或那樣的方式方法去理解它脯厨，但它內(nèi)部不一定是這樣铅祸！
? ? ? ? 正因為如此，以上觀念除了官方的合武，我的那部分內(nèi)容僅代表本人的理解临梗。由此造成的后果，本人概不負責稼跳。

End