Power BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX

ในบทความนี้ผมจะมาพูดถึงฟังก์ชัน CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX ที่ถูกออกแบบมาให้ทำหน้าที่ “เปลี่ยน Filter Context” โดยเฉพาะ

CALCULATE เป็นฟังก์ชันที่หน้าตาดูเรียบง่าย เหมือนไม่มีอะไร แต่ทรงพลังมากๆ ผมว่ามันเทียบได้กับตัวการ์ตูนเอกในเรื่อง One Punch Man ที่ชื่อไซตามะเลยครับ หน้าตาดูไม่เก่งอะไรเลย แต่ต่อยทีเดียวศัตรูตายหมด ดังนั้นอย่าดูถูก CALCULATE เด็ดขาด!!

ผมกล้าพูดได้เลยว่าใครที่ใช้ Power BI แต่ไม่รู้จัก CALCULATE ก็เหมือนกับยังใช้ Power BI ไม่เป็นอ่ะครับ ดังนั้นมันสำคัญจริงๆ

บอกเลยว่าตั้งแต่บทนี้ไปเนื้อหาจะเริ่มลึกซึ้งมากขึ้นๆ ดังนั้นความรู้เดิมควรจะแน่นก่อน และควรมีสมาธิในการอ่านดีๆ นะครับ อ่านผ่านๆ ไม่คิดตาม ไม่เข้าใจแน่นอน

ไฟล์ประกอบ

ใช้ไฟล์เดิมจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้

เอาล่ะเราไปเริ่มกันเลย!

Recap เล็กน้อย

ความหมายของการเปลี่ยน Filter Context ก็จะคล้ายๆ กับในบทความตอนก่อนหน้านี้ที่ ผมได้พยายามเขียนสูตร ALL เพื่อปลด Filter Context เดิมออกแล้ว Filter สินค้าที่เป็น Class ระดับ Economy เข้าไปอีกที ด้วยสูตรว่า

FilterEconomyRevenue =
SUMX (
    FILTER ( ALL ( fSales ), RELATED ( dProduct[ClassName] ) = "Economy" ),
    fSales[SalesQuantity] * RELATED ( dProduct[UnitPrice] )
)

ถ้าหากเราใช้ CALCULATE ทำ เราสามารถเขียนสูตรสั้นๆ เหลือแค่นี้

CalculateEconomyRevenue = 
CALCULATE([TotalRevenue],dProduct[ClassName]="Economy")

จะเห็นว่า CALCULATE นั้นสามารถใช้สูตรที่ทั้งสั้นกว่า และดูน่าจะอ่านเข้าใจง่ายกว่าสูตรยาวๆ นั้นด้วย ดังนั้นเรามาดูกันดีกว่าว่า CALCULATE มีวิธีการใช้งานยังไงกันแน่

ทำความเข้าใจ CALCULATE

CALCULATE ( <Expression> , [<Filter1>] , [<Filter2>] , ... )

CALCULATE นั้นประกอบไปด้วย 2 ส่วนหลักๆ ก็คือ

• Expression หรือก็คือวิธีการคำนวณ เราจะใส่สูตรคำนวณเอาเองหรือจะอ้างอิง Measure ที่มีอยู่แล้วก็ได้
• Filter คือ Filter Context ใหม่ที่อยากได้ สามารถใส่ได้หลายตัวโดยคั่นด้วย comma ซึ่งจะเป็นเงื่อนไขแบบ AND
  • สามารถใส่เป็นเงื่อนไขเปรียบเทียบคล้ายๆ กับเงื่อนไขในฟังก์ชัน FILTER ได้เลย
    • เช่น Field เทียบกับค่าอะไรบางอย่าง
    • ซึ่งความหมายคือจะมีการปลด Filter เดิมของ Field นั้นออกด้วย ALL แล้วค่อยใส่ Filter ใหม่ตามที่เราระบุ
  • ความพิเศษคือสามารถอ้างอิง Field จากตารางไหนก็ได้ โดยไม่ต้องใช้ RELATED มาช่วยเลย

หมายเหตุ : CALCULATE จะประเมิน Filter ก่อน Expression เสมอ

ฟังดูแล้วอาจจะยังงงๆ งั้นมาดูคำอธิบายของตัวอย่างข้างบนกัน

CalculateEconomyRevenue = CALCULATE([TotalRevenue],dProduct[ClassName]="Economy")

• Expression เขียนว่า [TotalRevenue] นั่นคือให้ใช้สูตรวิธีคำนวณของ Measure ที่ชื่อว่า [TotalRevenue] มาคำนวณ
• Filter เขียนว่า dProduct[ClassName]=”Economy” ความหมายคือ ให้สั่งปลด Filter ของ Field dProduct[ClassName] ออกก่อนด้วย ALL จากนั้นค่อย Filter dProduct[ClassName] ให้เป็น Economy อีกที

ก่อนจะมีการใช้ CALCULATE

Total Revenue จะถูก Filter ตามแต่ละ ClassName อย่างที่ผมเคยอธิบายไปในบทแรกๆ ว่าแต่ละจุดของตาราง Pivot นั้นมี Filter Context ที่ไม่เหมือนกันเลย

หลังจากใช้ CALCULATE

มันปลด Filter ของ ClassName ออกหมด แล้ว Filter เข้าไปใหม่ด้วย ClassName ว่าต้องเท่ากับ Economy ทุกตัวเลยได้ค่าเดียวกันหมดเลยดังรูป

สรุปสั้นๆ นะ

“CALCULATE จะเปลี่ยน Filter ตามเงื่อนไขที่ระบุ แล้วค่อยคำนวณตามวิธีที่เราระบุ“

โดยที่ถ้าใสเงื่อนไขแบบปกติมาตรฐาน เช่น Field เทียบกับค่าอะไรบางอย่าง มันก็จะปลด Filter เดิมของ Field นั้นๆ ออกไป แล้วค่อยใส่ Filter ใหม่ตามที่เราระบุในส่วน Filter ของ CALCULATE

จากคำอธิบายข้างบน จริงๆ แล้วสูตร CALCULATE ข้างบน มันคือการเขียนโดยย่อของการเขียนแบบนี้เลยครับ เทียบเท่ากันทุกประการ!!

CalculateEconomyRevenueFull =
CALCULATE (
    [TotalRevenue],
    FILTER ( ALL ( dProduct[ClassName] ), dProduct[ClassName] = "Economy" )
)

ดังนั้นแปลว่า ถ้าผมใส่ Filter ตัวอื่นที่ไม่เคยถูก Filter มาก่อน เช่น StoreType ด้วยสูตรนี้ว่าต้องการ StoreType แบบ Online เท่านั้น

CalculateOnlineRevenue = 
CALCULATE([TotalRevenue],dStores[StoreType]="online")

การทำงานก็ยังคงเป็น Concept เดิม คือ ปลด Filter ด้วย ALL แล้ว Filter เข้าไปด้วยตัวใหม่ที่ระบุ

ผลที่ได้ก็จะเป็นการ Filter เพิ่มจากตัว [TotalRevenue] ปกติ เนื่องจากปลด Filter dStores[StoreType] ก็ไม่ได้มีผลอะไร (เพราะไม่ได้มี Filter ไว้อยู่แล้ว) จากนั้นค่อย Filter dStores[StoreType] ให้เป็น Online

ทั้งนี้มันต่างจากตอนแรกที่เราสั่ง dProduct[ClassName] = “Economy” ในแง่ที่ว่าอันนั้นการปลด Filter ออกจะเห็นผลที่ชัดเจนเลย เพราะถ้าไม่ปลดออก กรณี Class Name ใน Visual ไม่ใช่ Economy จะต้องได้ค่า Blank เนื่องจาก ไม่มีทางที่จะเป็น Class Name 2 อย่างได้พร้อมกันอยู่แล้ว

CALCULATE หลายเงื่อนไข

เงื่อนไขแบบ AND

ถ้าเราใส่ Filter สองเงื่อนไขคั่นด้วย comma มันจะเป็นเงื่อนไขแบบ AND เลย จะเป็นยังไงมาดูกันครับ

CalculateOnlineEconomyRevenue =
CALCULATE (
    [TotalRevenue],
    dProduct[ClassName] = "Economy",
    dStores[StoreType] = "online"
)

ผลที่ได้จะเป็นการปลด Filter ClassName และ StoreType ออกทั้งหมด และสั่งให้ Filter ClassName เป็น Economy และ StoreType เป็น online นั่นเอง

และถ้าจะเป็นเงื่อนไขแบบ OR ล่ะ?

OR Field เดียวกัน ไม่มีปัญหา

ถ้าเป็นเงื่อนไขแบบ OR แบบอ้างอิง Field เดียวกันเรายังพอใช้เครื่องหมาย || หรือพวก IN มาช่วยได้ดังนี้

CalculateEconomyRegular =
CALCULATE (
    [TotalRevenue],
    dProduct[ClassName] = "Economy"
        || dProduct[ClassName] = "Regular"
)

หรือ

CalculateEconomyRegular =
CALCULATE ( [TotalRevenue], dProduct[ClassName] IN { "Economy", "Regular" } )

OR คนละ Field เริ่มยากละ

แต่ถ้าเราจะอ้างอิง Field คนละ Field กันแบบ OR มันจะไม่ยอม เช่น

ดังนั้นเราจะใช้ CALCULATE เปล่าๆ ไม่ได้แล้ว ต้องใช้ฟังก์ชันอื่นมาช่วย นั่นก็คือ FILTER กับ ALL มาช่วยนั่นเอง เช่น

CalculateEconomy_or_Online =
CALCULATE (
    [TotalRevenue],
    FILTER (
        ALL ( fSales ),
        RELATED ( dProduct[ClassName] ) = "Economy"
            || RELATED ( dStores[StoreType] ) = "online"
    )
)

สูตรยากกว่า CALCULATE สำเร็จรูปเยอะเลยเนอะ…

ซึ่งจะเห็นว่าในบางสถานการณ์ ความรู้เรื่อง FILTER และ ALL ที่ผมสอนในบทก่อนๆ ก็จำเป็น ไม่งั้นเราจะเขียนสูตรบางอย่างไม่ได้เลย เพราะ CALCULATE สำเร็จรูปไม่ได้รองรับทุกอย่างไปหมดหรอกครับ

เราน่าจะพอเห็นภาพการทำงานของ CALCULATE กันแล้ว คราวนี้ลองมาดูกันว่าถ้าใส่ CALCULATE ซ้อนกันจะเกิดอะไรขึ้น

CALCULATE ซ้อนกัน

สมมติผมเขียน Measure แบบนี้ ให้ทายว่ามันจะเปลี่ยน Filter ให้กลายเป็นแบบไหน?

Calculate2X =
CALCULATE (
    CALCULATE (
        [TotalRevenue],
        dProduct[ClassName] = "Economy",
        dStores[StoreType] = "online"
    ),
    dProduct[ClassName] = "Regular"
)

ถ้าดูเผินๆ คนทั่วไปน่าจะคิดว่า มันน่าจะได้เป็น Online และ Regular แต่ในความเป็นจริง Filter ที่หลงเหลือกลับได้เป็น Online และ Economy เหมือนเดิม!!

ทำไมถึงเป็นแบบนั้น? ทั้งๆ ที่การเขียนสูตร 2 ชั้นซ้อนกันมันต้องคำนวณตัวข้างในก่อนสิ ?

การเขียนสูตร 2 ชั้นจะคำนวณตัวในก่อนนั้นถูกต้องแล้วครับ แต่สาเหตุที่ผลลัพธ์เป็นแบบนี้ก็เพราะ CALCULATE จะประเมิน Filter ก่อน Expression เสมอครับ

มาดูกันให้ชัดๆ ว่าเกิดอะไรขึ้น

Calculate2X =
CALCULATE (
CALCULATE (
[TotalRevenue],
dProduct[ClassName] = "Economy",
dStores[StoreType] = "online"
),
dProduct[ClassName] = "Regular"
)

CALCULATE ตัวข้างใน (สีแดง) จะถูกคำนวณก่อน ซึ่งเดี๋ยวมันจะต้องคิดว่าจะต้องเปลี่ยน Filter ยังไงดี ทำให้มันต้องปลด Filter ClassName และ StoreType ออกให้หมดก่อน ทำให้ dProduct[ClassName] = “Regular” ที่อยู่ในชั้นนอกถูกทำลายไปโดยปริยาย

จากนั้นมันค่อยใส่ Filter ไปใหม่ว่า ClassName ให้เป็น Economy และ StoreType เป็น online นั่นเอง

กลายเป็นว่า เวลาใช้ CALCULATE ซ้อนกันหลายชั้น ชั้นในดันได้รับ Priority เรื่อง Filter มากว่านั่นเอง และจะทำลายชั้นนอกตัวที่ซ้ำกันด้านในทิ้งหมดเลย

เดี๋ยวลองเขียนเงื่อนไขชั้นนอกอีกอันแบบไม่ซ้ำกับข้างในบ้าง

Calculate2X_v2 =
CALCULATE (
    CALCULATE (
        [TotalRevenue],
        dProduct[ClassName] = "Economy",
        dStores[StoreType] = "online"
    ),
    dProduct[ClassName] = "Regular",
    dDate[Year] = 2012
)

จะเห็นว่า Filter ที่หลงเหลือก็คือ Online และ Economy และ ปี 2012 เพราะว่าเลขลดลงไปกว่า 2x ปกติ ดังนั้นจะเห็นว่า เงื่อนไข Filter ของ CALCULATE ชั้นนอกไม่ได้ถูกทำลายไปทั้งหมด แต่ถูกทำลายเฉพาะตัวที่ซ้ำกับด้านในเท่านั้น

หลายคนอาจเริ่มสงสัยแล้วว่า “ในชีวิตจริงใครจะไปเขียน CALCULATE 2 ตัวซ้อนกัน งงจะตายชัก??”

ครับ มันไม่มีใครตั้งใจเขียนแบบนั้นหรอก แต่เป็นเพราะความลับอีก 2 อย่างต่อจากนี้ต่างหากที่ทำให้เราต้องระวังเรื่องนี้ นั่นก็คือ…

• CALCULATE สามารถเปลี่ยน Row Context ให้เป็น Filter Context ได้ เรียกว่า Context Transition
• Measure ทุกตัวมี CALCULATE แฝงอยู่ข้างในเสมอ

ซึ่งเดี๋ยวผมจะพูดถึง 2 เรื่องนี้ในบทถัดไปครับ ซึ่งบอกเลยว่าเป็น Concept ของ CALCULATE ที่ลึกลับซับซ้อนที่สุดที่คนส่วนใหญ่ล้วนงงไปตามๆ กันเลยล่ะ!!

สารบัญ Series Power BI

• POWER BI ตอนที่ 01: POWER BI คืออะไร?
• POWER BI ตอนที่ 02: พื้นฐาน EXCEL ที่สำคัญก่อนจะเรียนรู้ POWER BI
• POWER BI ตอนที่ 03: ภาพรวมการใช้งาน POWER BI DESKTOP
• POWER BI ตอนที่ 04: สร้าง REPORT แรก ใน POWER BI
• POWER BI ตอนที่ 05: วิธีการ DRILL เพื่อเจาะลึกข้อมูลใน REPORT
• POWER BI ตอนที่ 06: การปรับแต่งสีใน VISUAL ด้วย CONDITIONAL FORMAT
• POWER BI ตอนที่ 07: เริ่ม GET DATA ตั้งแต่ไฟล์ยังว่างเปล่า
• POWER BI ตอนที่ 08: สร้าง DATA MODEL ที่เหมาะสม
• POWER BI ตอนที่ 09: สร้าง DATE TABLE ด้วย DAX
• POWER BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น
• POWER BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER
• POWER BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน
• POWER BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX
• Power BI ตอนที่ 14: Context Transition และ พลังแฝงใน Measure
• Power BI ตอนที่ 15: วิธีดึงค่าจาก Slicer มาคำนวณใน Report
• Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function
• Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual
• Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX
• Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension
• ส่วนเสริม
• การคำนวณต้นทุนแบบ FIFO ด้วย DAX
• แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)
• การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน
• เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี