เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX

เมื่อเราศึกษา DAX ใน Power BI/Excel Power Pivot ไปซักพัก บางทีก็จะเจอหลายฟังก์ชันที่ทำงานคล้ายกันมากๆ เช่น MAX กับ LASTDATE แต่การที่เราจะเข้าใจมันได้อย่างแท้จริงก็คือต้องลองหามุมคิดที่ว่า แต่ละฟังก์ชันที่คล้ายกันนั้น มีจุดเหมือนหรือจุดต่างกันอย่างไร

ป.ล. เว็บ SQLBI เคยเขียนสรุปไว้ในนี้ ผมขอนำมาตีความเพิ่มเติม รวมถึงใส่ภาพประกอบและตัวอย่างให้เพื่อนๆ เข้าใจง่ายขึ้น

โดยที่ช่วงแรกผมจะขอพูดการใช้งานในฐานะ Measure ซะก่อน แล้วอธิบายเวลาเอาไปใช้ใน Calculated Column ทีหลัง เพราะมีพฤติกรรมที่ซับซ้อนเกิดขึ้นกับ LASTDATE ในกรณี Column นะครับ

เอาล่ะมาเริ่มจากฟังก์ชันพื้นฐานอย่างMAX ซะก่อน

MAX แบบพื้นๆ

MAX จะหาค่าสูงสุด โดยให้ผลลัพธ์ออกมาเป็นค่าแบบ Scalar ก็คือเป็น Value ตัวเลขธรรมดาๆ ไปเลย ซึ่งใช้ได้กับ Data Type ได้หลากหลาย

• หากใช้กับเลขหรือวันที่ก็จะได้ค่ามากสุด (อันนี้ไม่แปลก )
• หากใช้กับText จะมองตามตัวอักษร A-Z (ซึ่งตรงนี้ไม่เหมือน Excel )
• แต่ถ้าใช้กับคอลัมน์ที่เป็น Logic จะได้ Error เลย

การใช้ MAX จะใช้ได้ 2 แบบคือ

• MAX (คอลัมน์) เพื่อให้ได้ค่าสูงสุดในคอลัมน์ที่เลือก
• MAX ( ค่า1,ค่า2) เพื่อให้ได้ค่าสูงสุดระหว่าง 2 ค่า

LASTDATE แบบพื้นๆ

LASTDATE ( <Dates> ) จะหาค่าวันที่มากสุดของคอลัมน์วันที่ที่กำหนด (แปลว่าใช้ได้กับ Field ที่เป็นวันที่เท่านั้น)

ซึ่ง LASTDATE เป็น Table Function ซึ่งจะให้ผลลัพธ์ออกมาเป็น Table ที่มี 1 Row และ 1 Column

แต่ทว่าใน DAX จะมีกฎพิเศษอยู่ว่า ค่าใดที่เป็น Table ที่มี 1 Row และ 1 Column DAX จะสามารถตีความให้เป็นค่า Scalar ธรรมดาๆ โดยอัตโนมัติได้ด้วย (แต่ในทางกลับกัน คือทำจาก Scalar เป็น Table อัตโนมัติไม่ได้นะ)

แปลว่า MAX ใช้ได้กับข้อมูลที่ต้องการผลลัพธ์เป็น Scalar เท่านั้น แต่ว่า LASTDATE ใช้ได้กับข้อมูลที่ต้องการ Scalar หรือ Table ก็ได้

หากลองทดสอบดูจะเห็นได้ชัดเลย

สรุปความรู้ ณ ตอนนี้

ต่อไปจะทดสอบกับข้อมูลวันที่ทั้งคู่

ใน Filter Argument ของ CALCULATE ต้องการ input ที่เป็น Table ซึ่งแปลว่าเราใส่ LASTDATE ได้ (เพราะมันให้ผลเป็น Table ได้) ดังนี้

ยอดขายวันสุดท้าย = CALCULATE([ยอดขายรวม],LASTDATE(dDate[Date]))

แต่เราไม่สามารถใส่ MAX ในกรณีนี้ได้

ยอดขายวันสุดท้ายMAX = CALCULATE([ยอดขายรวม],MAX(dDate[Date]))

แบบนี้ก็ไม่ได้

ยอดขายวันสุดท้ายMAX = CALCULATE (
    [ยอดขายรวม],
    dDate[Date] = MAX ( dDate[Date] )
)

ที่ทำแบบนี้ไม่ได้ เพราะ CALCULATE ไม่รองรับการเปรียบเทียบกับค่า Measure หรือ Expression ถ้าจะทำต้องใช้ VAR หรือไม่ก็หลบไปใช้ใน FILTER แทน ดังนี้

ถ้าหลบไปใช้ VAR ให้มันคำนวณ MAX( dDate[Date] ) ให้เป็นค่าคงที่ไปก่อน แล้วค่อยเอาค่าคงที่ไปใช้ในเงื่อนไขเปรียบเทียบ (ซึ่งผมชอบใช้วิธีนี้เพราะสูตรเข้าใจง่ายกว่า)

ยอดขายวันสุดท้ายMAX = 
VAR CurrentMaxDate= MAX( dDate[Date] )
RETURN
CALCULATE (
    [ยอดขายรวม],
    dDate[Date] = CurrentMaxDate
)

หรือถ้าจะใส่ MAX ใน FILTER ก็จะทำได้ เนื่องจาก FILTER รองรับการเปรียบเทียบกับ Measure หรือ Expression ได้ คือต้องใช้ในฐานะที่เป็น Scalar Value เท่านั้น

ยอดขายวันสุดท้ายMAX =
CALCULATE (
    [ยอดขายรวม],
    FILTER ( ALL ( dDate[Date] ), dDate[Date] = MAX ( dDate[Date] ) )
)

และ LASTDATE สามารถรองรับคอลัมน์วันที่ที่เป็นสูตรได้ เช่น ถ้าเขียน LASTDATE ผสมกับ ALL ก็จะไม่มีปัญหาอะไร แต่ถ้าใช้ MAX จะไม่ได้ ดังนี้

วันสุดท้ายLastDate = LASTDATE ( ALL ( 'dDate'[Date] ) )

วันสุดท้ายMAX = MAX ( ALL ( 'dDate'[Date] ) )

ที่ใช้ MAX ไม่ได้ เพราะ MAX ต้องใช้กับคอลัมน์ที่มีอยู่จริงตรงๆ เท่านั้น

ดูเหมือนว่าการใช้ LASTDATE จะดีกว่าในเกือบทุกกรณี แต่มีจุดที่ควรต้องระวังตรงที่ LASTDATE หากอยู่ใน Row Context จะทำให้เกิด Context Transition

สิ่งที่หลายคนไม่รู้คือ

LASTDATE ( 'dDate'[Date] )

ภายใน DAX เอง จะตีความเป็นแบบนี้โดยอัตโนมัติ

LASTDATE ( CALCULATETABLE ( DISTINCT ( 'dDate'[Date] ) ) )

ซึ่ง CALCULATETABLE มีพฤติกรรมแบบเดียวกับ CALCULATE คือเมื่อเจอ Row Context จะทำให้เกิด Context Transition ทันที เช่นถ้าไปใช้ในฐานะ Calculated Column ก็จะเห็นผลลัพธ์เลยแบบนี้ (สังเกตว่า LASTDATE ไมได้ค่ามากสุดของตาราง แต่เป็นมากสุดในแต่ละวันที่แทน เพราะมีการเปลี่ยน Row Context เป็น Filter Context)

LastDate = LASTDATE(dDate[Date])

MaxDate = MAX(dDate[Date])

จะเห็นวาส LastDate เจอ Context Transition เข้าเล่นงาน ทำให้มองไม่เห็นวันสุดท้ายจริง แต่ MaxDate เห็นวันสุดท้ายของคอลัมน์จริงๆ ดังนั้นถ้าจะใช้ LASTDATE ใน Row Context ต้องระวังจุดนี้ด้วย

สรุปความรู้

สารบัญ 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

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