邏輯函式

Power BI 的 DAX 中級

Carl Rosseel

Curriculum Manager

邏輯函式總覽

邏輯函式會對運算式進行判斷，回傳該運算式中的值或集合的資訊。

最常用的邏輯函式有：

IF()
AND(), OR(), NOT()
SWITCH()

IF() 是最常用的邏輯函式之一

結構：

IF(<logical_test>, <value_if_true>, <value_if_false>)

範例：

Performance = IF([Total Sales] >= 50 000, "Target Reached", "Target Not Reached")

IF() 是最常用的邏輯函式之一

結構：

IF(<logical_test>, <value_if_true>[, <value_if_false>])

範例：

Performance = IF([Total Sales] >= 50 000, "Target Reached", "Target Not Reached")

Name	Total Sales
Jenny	48,431
Jane	76,528
Dwayne	24,167
Thomas	52,125

IF() 是最常用的邏輯函式之一

結構：

IF(<logical_test>, <value_if_true>[, <value_if_false>])

範例：

Performance = IF([Total_Sales] >= 50 000, "Target Reached", "Target Not Reached")

Name	Total Sales	Performance
Jenny	48,431	Target not Reached
Jane	76,528	Target Reached
Dwayne	24,167	Target Not Reached
Thomas	52,125	Target Reached

AND()、OR() 與 NOT() 運算子

三種運算子都會回傳 TRUE 或 FALSE。

AND(<logical1>,<logical2>)
- 若兩個條件皆為 TRUE，則回傳 TRUE
- 範例： AND(5 < 4, 5 < 6) = AND(FALSE, TRUE) = FALSE
OR(<logical1>,<logical2>)
- 只要任一條件為 TRUE，就回傳 TRUE
- 範例： OR(5 < 4, 5 < 6) = OR(FALSE, TRUE) = TRUE
NOT(<logical>)
- 將 TRUE 轉為 FALSE，反之亦然
- 範例： NOT(OR(5 < 4, 5 < 6)) = NOT(TRUE) = FALSE

AND()、OR() 與 NOT() 運算子

AND 可用 && 取代

AND(5 < 4, 5 < 6) = 5 < 4 && 5 < 6

OR 可用 || 取代

OR(5 < 4, 5 < 6) = 5 < 4 || 5 < 6

SWITCH() 的威力

將運算式與一串值比對，並回傳多個可能結果中的一個。

SWITCH(<expression>, <value>, <result>[, <value>, <result>] ... [, <else>])

常用來取代巢狀 IF() 函式

Performance = SWITCH(TRUE, 
[Total_Sales] < 25 000, "Poor",
[Total_Sales] < 50 000, "Below expectations",
[Total_Sales] < 75 000, "Above expectations",
"Exceptional")

SWITCH() 的威力

Performance = SWITCH(TRUE, 
[Total_Sales] < 25 000, "Poor",
[Total_Sales] < 50 000, "Below expectations",
[Total_Sales] < 75 000, "Above expectations",
 "Exceptional")

Name	Total Sales
Jenny	48,431
Jane	76,528
Dwayne	24,167
Thomas	52,125

SWITCH() 的威力

Performance = SWITCH(TRUE, 
[Total_Sales] < 25 000, "Poor",
[Total_Sales] < 50 000, "Below expectations",
[Total_Sales] < 75 000, "Above expectations",
 "Exceptional")

Name	Total Sales	Performance
Jenny	48,431	Below Expectations
Jane	76,528	Exceptional
Dwayne	24,167	Poor
Thomas	52,125	Above expectations

SWITCH() 的威力

DISCOUNT = SWITCH([Clothing Type], 
               "Shoes", 0.15,   
               "Pants", 0.20,
               "Belts", 0.30, 
               "T-shirt", 0.25)

Clothing Type
Shoes
Pants
Belt
T-shirt

SWITCH() 的威力

DISCOUNT = SWITCH([Clothing Type], 
               "Shoes", 0.15,   
               "Pants", 0.20,
               "Belts", 0.30, 
               "T-shirt", 0.25)

Clothing Type	Discount
Shoes	15%
Pants	20%
Belt	30%
T-shirt	25%

換個 SWITCH！

Power BI 的 DAX 中級