《孫興華講PowerBI火力全開版》
【全92集】PowerBI教程 DAX函式 視覺化圖表 Power BI教程 PowerPivot教程 Excel
很多人DAX公式錯誤都是:咎由自取!因為他已經習慣了Excel裡亂七八糟的寫法,接下來我們就挨個的講一講,如果你是從正規資料庫上獲取的資料,可能大多數問題你都遇不到。一、文字與數值根據《孫興華講PowerBI火力全開》筆記第1集 運算子
數值加數值 = [數值]+[數值] // 520 + 1314 = 1834
文字加數值 = [字元]+[數值] // "520" + 1314 = 1834
文字加文字 = [字元]+[字元] // "520" + "1314" = 1834
數值連線數值 = [數值] & [數值] // 520 & 1314 = "5201314"
文字連線數值 = [字元] & [數值] // "520" & 1314 = "5201314"
文字連線文字 = [字元] & [字元] // "520" & "1314" = "5201314"
你會發現規律:
使用“+”時,他會將字元自動轉化成數值進行計算
使用“&”時,他會將數值自動轉化成文字進行計算
你真的認為它永遠都能轉換嗎?例如:"1"不等於1,"520"大於1314
因為:"1"=49 而 :1 = 1
因為:"520"=53 而:1314=1314
因為ASCII碼?不!你這是,Excel邏輯,在PowerBI的DAX中,文字與數值就無法進行比較運算。DAX會報錯,會提示你:
DAX比較操作不支援將型別為Text的值和型別為Integer的值進行比較,請考慮使用Value或Format函式轉換其中一個值。
可以使用《孫興華講PowerBI火力全開》筆記第17課轉換函式 VALUE【將文字轉數值】
Value("520")
所以建議大家無論何時何地,都不要讓DAX自己轉換資料型別,你要檢查每個欄位的資料型別是否正確,如果你的表匯入時,這一列都是數值,那PowerBI會自動識別為數值,就是因為大多數人我行我素(如圖1-4-1)。造成後期麻煩不斷。
規避方法:
誰在這樣輸入資料,我們就讓他來分析:)
圖1-4-1
剛才我們遇到的“520”或者“1”都是文字型的數字,但是遇到(圖1-4-1)這種情況你怎麼辦呢?這就引出了我們第二個概念:
二、如何正確的規避錯誤
如圖1-4-2,因為銷售數量是文字型,單價是數值型,我們直接【新建列】計算,會報錯!
圖1-4-2
《孫興華講PowerBI火力全開》筆記第12課.IFERROR 遇到錯誤時使用指定數值替換
如圖1-4-3,我們可以使用IFERROR函式判斷髮生錯誤時,返回空。這也是推薦的方法!
圖1-4-3
圖1-4-4,也可以使用同樣的函式,將錯誤返回成指定數值。
圖1-4-4
圖1-4-5,但是,不能將錯誤轉化成文字。
圖1-4-5
為什麼不能將錯誤寫成中文或英文?
因為銷售金額這一列在計算過程中,資料型別已經變成了數值
而當初匯入Excel檔案時,銷售數量這列是文字型,你看都靠左,而單價這列是數值型都靠右
因為銷售數量那一列有一個孫興華,所以就自動使用了文字型(即可以存數值又可以存文字)
如果你想強行轉換結果的型別,也沒有問題,但是這屬於胡鬧,用辦公軟體的人有兩個最嚴重的胡鬧
1. 非打印表格使用合併單元格或多表頭,胡鬧指數100%
2。 資料型別混亂,同一欄位各種型別胡寫,胡鬧指數100%三、為何推薦將錯誤、空值、缺失值轉化為BLANK()
在DAX中,通常缺失值,空值都使用BLANK()處理
科普:
缺失值包含空值(null)、0、False等
null值是空值,是缺失值的一種
你心裡應該非常清楚,如果(圖1-4-5)的結果對錯誤的返回值寫了中文或英文,再次計算時仍然要規避下一個錯誤,那為何你還要這樣做呢?
強行寫也不是不可以,但是無意義,還添亂:如圖1-4-6
《孫興華講PowerBI火力全開》筆記第17課.轉換函式 CONVERT函式【轉換為指定資料型別】
銷售金額 = IFERROR(CONVERT([銷售數量]*[單價],STRING),"這是錯的")
圖1-4-6
現在如你所願”銷售金額“這一列變成了文字型
你現在寫一個度量值,並將它放到矩陣中,會有什麼結果?如圖1-4-7
總金額 = sum(Sheet1[銷售金額])
圖1-4-7
原理:IFERROR之所以遇到錯誤時使用指定數值替換,因為你在計算,計算結果本身就是數值,所以必須用數值,這是資料型別的統一性。
你說了一個謊就要去用另一個謊去圓
IFERROR(表示式,BLANK()或數值)
有些人會抬扛:如果我的表示式是&連線兩個列,報錯以後,是不是可以返回文字?可以!但是,如果你原始的兩個列本身不是錯誤,你怎麼可能連接出錯誤?
那麼問題來了,為何推薦將錯誤轉化為BLANK()?答:為了不再出錯!
圖1-4-8
銷售金額 = IFERROR(CONVERT([銷售數量]*[單價],STRING),"錯了")
銷售金額2 = IFERROR([銷售數量]*[單價],BLANK())
銷售金額*10 = [銷售金額]*10
銷售金額2*10 = [銷售金額2]*10
我們再進行一個測試:
圖1-4-9
銷售數量2 = if([銷售數量]="孫興華",BLANK(),[銷售數量])
空字串 = [銷售數量2]=""
銷售金額 = IFERROR([銷售數量]*[單價],BLANK())
0 = [銷售金額]=0
如果這一列,是文字型別,那麼,BLANK()就是空字串
如果這一列,是數值型別,那麼,BLANK()就是0
這個時候,數學邏輯好的人,就會舉一反三,那麼日期/時間型呢?因為他們本身也是數字型別的演化,所以同理數字。
但是要注意:並不是任何型別與BLANK()進行四則運算結果都是BLA
NK()
例如:
1+BLANK()=BLANK()
BLANK()+BLANK()=BLANK()
這隻限定於加減乘除
PS: 水軍經常會拿常識說我講的內容不適合小白,例如他聽不懂"唯一值"是什麼?如果哪位水軍聽不懂四則運算是什麼,只能證明你的小學畢業證是假的。
另外需要注意的是:日期型別的BLANK()+1 不等於BLANK()
修證:在影片上我講這裡時忽略了日期型別,大家要特別注意
但是:連線和比較運算就不一樣了,你需要舉一反三:
"孫興華" & BLANK() = "孫興華" //因為這個時候, BLANK()代表空字串
520 & BLANK() = "520" //因為&的結果都是字串,所以BLANK()代表空字串
"孫興華" > BLANK() //返回: True 因為這個時候, BLANK()代表空字串
"" = BLANK() //返回: True 因為這個時候, BLANK()代表空字串
1 > BLANK() = True //返回: True 因為這個時候, BLANK()代表0
0= BLANK() //返回: True 因為這個時候, BLANK()代表0
如果是邏輯判斷呢?廢話連篇!True和False是0和1
那你說: True && BLANK() 是什麼? // 1 and 0 那不還是0嗎?
我小時候打撲克牌,有一種玩法,就是誰拿到大王小王(等同於BLANK()),可以用它們代替任何牌,可能地區不同我們的玩法不同,我們「塞伯坦星球」就是這麼玩的。
例如,我手上有 3、4、6、7、9、10、J、Q、K、A
很明顯,我若將手上順子的9、10、J、Q、K、A出掉,手上的3、4、6、7就變成了單牌。
如果大小王在手,我可以這樣出
3、4、大王、6、7、小王、9、10、J、Q、K、A
//大王代替5,小王代替8
或
3、4、小王、6、7、大王、9、10、J、Q、K、A
//小王代替5,大王代替8
四、IF與IFERROR的區別
大家都知道,分母為0時會報錯,除非你用安全除法
=IFERROR(5/0,BLANK())
等同於
= If(ISERROR(5/0),BLANK(),5/0)
《孫興華講PowerBI火力全開》筆記第12課 if條件判斷
《孫興華講PowerBI火力全開》筆記第22課 資訊函式中的ISERROR
=ISERROR(5/0) //返回:True ,功能判斷5/0是不是一個錯誤
很明顯,我們使用IFERROR更簡單,但是現在的情況是:
如果5/0是個錯誤,我們就返回BLANK(),否則返回5/0的結果
現在我們換一道題,如果 '表'[列名] 是BLANK(),就怎麼樣,否則怎麼樣?
我們有IFERROR,但是沒見過IFBLANK,然而我們有ISBLANK
《孫興華講PowerBI火力全開》筆記第22課 資訊函式中 還有很多判斷函式可供使用
圖1-4-10
你知道"銷售金額3"為什麼報錯嗎?
因為:[銷售數量2]*[單價] 返回數值型,而“哈哈它是空的”是個文字型
五、拋開速度,IFERROR與ISERROR最大的弊端
圖1-4-11
商1 =iferror([分子]/[分母],Blank())
商2=if(iserror([分子]/[分母]),Blank(),[分子]/[分母])
商3=if([分母]0,[分子]/[分母],Blank())
商4=if([分母]0,[分子]/[分母],ERROR("分母不能為0"))
商1和商2需要先判斷[分子]/[分母]是不是一個錯誤,理論上比商3慢,是因為多了一步判斷。
但是,實際你不易感覺出來,就好比有些奇怪的人們對比照片和影片清晰度一樣。
例如照片:它們將照片放大放大再放大,對比銳度,焦外,二線性。
例如影片:他們將畫面定住,然後趴在電視機前,看看演員的毛孔。
另一個問題才是最致命的,官方語言說:截獲內層級別計算時發生的錯誤
圖1-4-12
商1 = CALCULATE(sumx('Sheet2',iferror([分子]/[分母],Blank())))
商2 = iferror(sumx('Sheet2',[分子]/[分母]),Blank())
IFERROR與ISERROR同理,官方語言也是很好理解,可即便是萌新小白
你也應該清楚,他的運算規則,是從內到外,你搞清楚運算規則,原理不攻而破。
六、ERROR函式測試未透過
首先宣告,我並不是要黑誰和批判誰,這只是我在使用中發現的問題,和大家分享一下,大家可以自行測試,有問題可以提,互通有無。
圖1-4-13 是某位DAX原理講師,在知乎上引用《The Definitive Guide to DAX》原著中的內容:
圖1-4-13
第1個 = IFERROR (
SQRT ( Test[Temperature] ),
)
這個度量值肯定是沒問題的,但是第2個度量值就有問題了:
PowerBI 2021年6月版測試不透過
第2個 = IF (
Test[Temperature] >= 0,
SQRT ( Test[Temperature] ),
ERROR ( "The temperature cannot be a negative number. Calculation aborted." )
)
圖1-4-14
處理方法:
錯誤提示 = IFERROR(CONVERT(SQRT(Test[Temperature]),STRING),"The temperature cannot be a negative number. Calculation aborted.")
七、PowerBI中的DAX使用變數時不受版本影響
Marco Russo和Alberto Ferrari 兩位義大利專家寫的《The Definitive Guide to DAX》
曾指出,使用變數時,會根據產品DAX版本不同,返回不同的結果
親測:2021年6月版的PowerBI不受影響
但是在PowerPivot中,可能就真的不一樣了。PowerPivot內建在Excel中,Excel2016、2019、2021、365版本眾多,365訂閱尚且更新,其它版本…
2021年6月版的PowerBI中:以下三種寫法,均正常返回結果
測試1=iferror(5/0,520)
// 先定義變數,輸出時再做判斷
測試2 =
var
abc= 5/0
return
iferror(abc,520)
//先判斷,再輸出
測試3 =
iferror(
var abc=5/0
return
abc,
520
)
八、防抬扛:分母為零時計算報錯可用安全除法
《孫興華講PowerBI火力全開》筆記第13課 安全除法
語法:DIVIDE(分子,分母,[替換結果])
替換結果可以省略不寫,省略時返回為空。
除法 = [分子]/[分母]