PowerBI之DAX神功：第1卷第21回 Calculate調(diào)節(jié)器USERELATIONSHIP函數(shù)
無忌哥哥永遠不可能與黃衫女在一起，他們之間不會建立關(guān)系
但是，無忌哥哥與芷若妹妹和敏敏之間有著說不清的關(guān)系。
不過，當無忌哥哥跟芷若曖昧時，敏敏肯定不在；反之，與敏敏曖昧時，芷若也不在。
實線是：現(xiàn)任關(guān)系
虛線是：備胎關(guān)系
所以，無忌哥哥被稱作渣男，他并沒有違法；如果兩條都是實線，那就違了49年以后的法了。
但是，無忌哥哥很聰明，他在虛虛實實中不停的變幻，這種事，就是放在今天，也只能封殺他，撤了他明教教主的職務(wù)，抓不了他。（前提他必須是知名人士，如果是江湖中人，也只能道德譴責他）
無忌哥哥是通過什么方法，將虛線關(guān)系啟用，同時實線關(guān)系自動變成虛線？
一、USERELATIONSHIP函數(shù)
張無忌是Calculate，USERELATIONSHIP函數(shù)是調(diào)節(jié)器，它不返回任何值，它的作用是開啟指定關(guān)系并停之原來的關(guān)系。
這個函數(shù)《孫興華講PowerBI火力全開》筆記28.04中詳細講解過，而且還舉了兩個案例，我們今天不是來炫案例的，這是原理課，我們只講原理：（案例請看《火力全開》）
需求1：我想使用一端表的日期篩選入職人數(shù)
入職人數(shù)1= count('多端表'[姓名])?
入職人數(shù)2= countrows('多端表')
入職人數(shù)3=count('多端表'[入職])
以上3個度量值都可以實現(xiàn)入職人數(shù)的篩選（功能相同）
需求2：我想使用一端表的日期篩選離職人數(shù)
這時，您需要先切換兩張表的連線關(guān)系
離職人數(shù)1= count('多端表'[姓名])?
離職人數(shù)2= countrows('多端表')
離職人數(shù)3=count('多端表'[入職])
以上3個度量值都可以實現(xiàn)相同功能，但是你在做匯報時，你不能在表上改關(guān)系玩呀？
這時，我們可以建立一條實線關(guān)系，再建立一條虛線關(guān)系：
寫兩個度量值：
入職人數(shù) = count('多端表'[姓名])
離職人數(shù) = CALCULATE([入職人數(shù)],USERELATIONSHIP('一端表'[日期],'多端表'[離職]))
新建一張表：方法源于《DAX神功》第1卷第3回
參數(shù)表 =?
VAR tb =?
? ? {
? ? ? ? ("入職"),
? ? ? ? ("離職")
? ? }
Return
? ? SelectColumns(tb , "項目" , [Value])
再寫一個度量值：
切換 =
switch(TRUE(),
SELECTEDVALUE('參數(shù)表'[項目])="入職",[入職人數(shù)],
SELECTEDVALUE('參數(shù)表'[項目])="離職",[離職人數(shù)]
)
的確很方便，但是你要注意一些問題：
【1】兩表之間可以有多條線，但只能有一條實線。
【2】你要保證兩表是一對多關(guān)系
【3】在使用USERELATIONSHIP函數(shù)前，必須保證虛線關(guān)系已經(jīng)建立。
我們假設(shè)你沒有建立虛線關(guān)系，就使用USERELATIONSHIP函數(shù)會出現(xiàn)下面的情況：
入職人數(shù) = count('多端表'[姓名])
離職人數(shù) = CALCULATE([入職人數(shù)],USERELATIONSHIP('一端表'[日期],'多端表'[離職]))
【4】USERELATIONSHIP函數(shù)里面兩個參數(shù)沒有順序，先寫誰都可以，總之一個是主鍵另一個是外鍵，下面兩個度量值都是正確的
離職人數(shù) = CALCULATE([入職人數(shù)],USERELATIONSHIP('一端表'[日期],'多端表'[離職]))
離職人數(shù) = CALCULATE([入職人數(shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期]))
【5】Calculate嵌套使用時，只有最內(nèi)層USERELATIONSHIP函數(shù)生效
度量值=
Calculate(
Calculate([總數(shù)量],USERELATIONSHIP(主鍵,外鍵)),
USERELATIONSHIP(主鍵,外鍵)
)
《DAX神功第1卷第18回》講到DAX所有函數(shù)都是從內(nèi)向外，USERELATIONSHIP函數(shù)在calculate中使用只生效第1次
我們舉個通俗的例子：
度量值=
Calculate(
Calculate([孩子],USERELATIONSHIP(張無忌,趙敏)),
USERELATIONSHIP(張無忌,周芷若)
)
張無忌和趙敏的孩子，永遠都是他們倆的，變不成張無忌和周芷若的孩子
我們應(yīng)用到自己的案例上來：
總?cè)藬?shù) = count('多端表'[姓名])
離職數(shù) =?
Calculate(
Calculate([總?cè)藬?shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期])),
USERELATIONSHIP('多端表'[入職],'一端表'[日期])
)
離職數(shù)2= Calculate([總?cè)藬?shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期])
你將度量值【離職數(shù)2】嵌套進來，也是一個道理，誰和誰的孩子，DNA是改不了的。
入職數(shù)2=Calculate([離職數(shù)2],USERELATIONSHIP('多端表'[入職],'一端表'[日期]))
入職數(shù)2仍然是離職數(shù)2
【6】有人問calculate中可以嵌套多少個USERELATIONSHIP函數(shù)？
只有最內(nèi)層的生效，外面的又不生效，你嵌套他做什么？
【7】USERELATIONSHIP函數(shù)不支持新建列？誰說的？
【度量值】總?cè)藬?shù)=count('多端表'[姓名])
在一端表上新建列沒有問題：
新建列 = Calculate([總?cè)藬?shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期]))
但是在多端表中新建列就不行了
新建列=Calculate([總?cè)藬?shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期]))
先寫成度量值再放到新建列上，也不行
度量值 = Calculate([總?cè)藬?shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期]))
我們耐心的從頭開始分析：
<1>? 關(guān)系 ：日期~入職
新建列建立在一端表中：一端表篩選多端表：
新建列建立在多端表中：
<2>? 關(guān)系 ：日期~離職
一端表：
多端表：
<3>? 兩條關(guān)系
這次我先看多端表上新建列：
多端表，啟用虛線關(guān)系，禁用實現(xiàn)關(guān)系
注意：Calculate是先建立關(guān)系，再應(yīng)用于篩選器。
因為Calculate是先執(zhí)行調(diào)節(jié)器USERELATIONSHIP函數(shù)，后執(zhí)行篩選器
原來實線關(guān)系是先存在的，現(xiàn)在你現(xiàn)改的關(guān)系，所以要先執(zhí)行調(diào)節(jié)器USERELATIONSHIP('多端表'[離職],'一端表'[日期])
再應(yīng)用到篩選器
你重新建立一對多關(guān)系后，一端表中的列就被Vlookup到多端表了。
雖然，在我的案例中，1端表只有日期列，但是你看不到的并不是不存在，你始終要記住
一對多關(guān)系，就是一端表中所有列Vlookup到多端表中了。
這個時候你就要指定可以篩選的列，禁用Vlookup過來的那些列
列 = Calculate([總?cè)藬?shù)],USERELATIONSHIP('多端表'[離職],'一端表'[日期]),ALLEXCEPT('多端表','多端表'[姓名],'多端表'[入職],'多端表'[離職]))??
?//只有指定的列可以篩選，其它列不能篩選
反之，我在一端表中新建列就沒這個問題，因為一端篩選多端，不會從多端獲取內(nèi)容
二、名詞名句解釋
《The Definitive Guide to DAX》中提到的 USERELATIONSHIP函數(shù)在激活關(guān)系時，關(guān)系的定義發(fā)生在引用表時，而不是發(fā)生在調(diào)用Related或其它關(guān)系函數(shù)時。
這句話翻譯成通俗的解釋：關(guān)系函數(shù)只能用在創(chuàng)建行上下文的函數(shù)中（例如迭代函數(shù)）
calculate和calculatetable 創(chuàng)建是篩選上下文，不能直接使用關(guān)系函數(shù)
三、篩選器與調(diào)節(jié)器的順序
度量值1 = CALCULATE([總分],'Sheet1'[班級]="一班",'Sheet1'[姓名]="二班")
等價
度量值1 = CALCULATE([總分],'Sheet1'[班級]="一班" && 'Sheet1'[姓名]="二班")
解釋： 這么寫是沒有結(jié)果的
正確的寫法：
度量值1 = CALCULATE([總分],'Sheet1'[班級] in {"一班","二班"})
這樣看不出順序來，我們換成or的關(guān)系就看出來了，篩選器是自左向右的先后順序
度量值2 = CALCULATE([總分],'Sheet1'[班級]="二班" || 'Sheet1'[班級]="一班")
度量值3 = CALCULATE([總分],'Sheet1'[班級]="三班" || 'Sheet1'[班級]="二班")
解釋：篩選器從左向右
例如度量值2，找到2班就不會再找1班了，返回2班的分數(shù)
例如度量值3，找不到3班，但是能找到2班的，返回2班的分數(shù)
但是不同列篩選時，這個順序?qū)Y(jié)果是無所謂的，但是仍然從左向右，例如：
度量值4 = CALCULATE([總分],'Sheet1'[班級]="一班" && 'Sheet1'[性別]="男")
《DAX》神功中我們講解了兩個調(diào)節(jié)器ALL和Keepfilters
ALL即可以是調(diào)節(jié)器，又可以是表函數(shù)
例如：all出現(xiàn)在例如迭代函數(shù)第一參數(shù)為表的時候，它是表函數(shù)
Filter(all(表[列]),篩選條件)
但是，放到calculate篩選器中時ALL系列函數(shù)的作用是Calculate調(diào)節(jié)器。
調(diào)節(jié)器優(yōu)先于篩選器
度量值5 = CALCULATE([總金額],ALL('多端表'[姓名]),VALUES('多端表'[姓名]))
度量值6 = CALCULATE([總金額],VALUES('多端表'[姓名]),ALL('多端表'[姓名]))
解釋：度量值5和度量值6，結(jié)果相同
步驟1：ALL調(diào)節(jié)器
步驟2：Values篩選器
《DAX神功第1卷第17回》Values做篩選器，它即是表函數(shù)又是篩選器，當它放到Calculate篩選器中，它就是篩選器
keepfilters也是調(diào)節(jié)器，可它不是Calculate的調(diào)節(jié)器，它是篩選參數(shù)調(diào)節(jié)器，它的作用是：恢復參數(shù)的篩選。
網(wǎng)友提問：關(guān)于度量值5計值順序是怎樣的？
度量值5 = calculate([銷售額], all(日期表), values(日期表[月份]), all(商品表), keepfilters(商品表[商品種類]="A"))?
順序，從左向右，先Calculate調(diào)節(jié)器，再篩選器：
第1步：all(日期表)
第2步：all(商品表)
第3步：values(日期表[月份])
第4步：keepfilters(商品表[商品種類]="A")? ? //讓不能實現(xiàn)篩選的篩選器恢復篩選功能
小常識：
Calculate調(diào)節(jié)器有哪些？
Userlationship、Crossfilter、ALL、Allselected、Allexcept、Allcrossfiltered、Allnoblankrow
