“เอ๋! เราไม่ควรสร้างคอลัมน์เพิ่มใน Power BI หรือคะ?” น้องนิจิคนงามร้องด้วยความประหลาดใจ
“ใช่ครับ การสร้างคอลัมน์เพิ่มเป็นข้อห้ามใน Power BI”
“ทำไมล่ะคะ?”
“เพราะจะหน่วงโปรแกรมครับ”

ลองนึกภาพตารางการสั่งซื้อ 1 ล้านบรรทัด
ถ้าเพิ่ม 1 คอลัมน์ แปลว่า เพิ่ม 1 ล้านข้อมูล
ถ้าเพิ่ม 2 คอลัมน์ แปลว่า เพิ่ม 2 ล้านข้อมูล
ถ้าเพิ่ม 10 คอลัมน์ แปลว่า เพิ่ม 10 ล้านข้อมูล!

การเพิ่มคอลัมน์ทำให้ข้อมูลเยอะ ขนาดไฟล์ใหญ่ และคำนวณช้ามาก

(บทความนี้คือ ep.2 ใครอ่านแล้วงง อ่าน ep.1 ได้ ที่นี่ )

“อ้อ! คอลัมน์ Year, Quarter, Month, Day (Date Hierarchy) ที่โปรแกรมให้มาก็ไม่ควรใช้นะครับ” ผมเสริม

“เอ๋! คอลัมน์พวกนั้นก็ห้ามใช้หรือคะ?” สีหน้าน้องนิจิยิ่งแสดงความประหลาดใจ
“ใช่ครับ คอลัมน์พวกนั้นแม้มองใน Data View ไม่เห็น“

“แต่ความจริงแล้วโปรแกรมแอบใส่ที่ ‘หลังบ้าน’ Date Hierarchy มี 4 คอลัมน์ เทียบได้กับการเพิ่ม 4 ล้านข้อมูล ทำให้คำนวณช้าลงเช่นกัน” ผมอธิบาย

“งั้นถ้านิจิต้องการสรุปจำนวนออเดอร์ที่สั่งในแต่ละเดือน ว่าได้รับของเดือนละกี่ออเดอร์ ควรทำยังไงดีคะ?”

มีขั้นตอนการทำนิดนึง ขอสรุปเป็น 4 สเต็ปดังนี้ครับ

1. สร้างตาราง OrderDate
2. สร้างตาราง DeliverDate
3. สร้างความสัมพันธ์กับตาราง Order
4. สร้าง Measure คำนวณ

1. สร้างตาราง OrderDate

สร้างตารางข้อมูลเกี่ยวกับวันที่เกิดการสั่ง Order เช่น วัน เดือน ปี เดือน-ปี
อาจสร้างแบบแมนวลใน Excel แล้วค่อยดึงเข้ามา
หรือสร้างโดยการเขียนสูตร DAX โดยคลิกที่ Data View/ Table tools/ New Table

พิมพ์สูตร DAX

tOrderDate = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "OYear", YEAR([Date]),
    "OMonthNum",MONTH([Date]),
    "OMonth",FORMAT([Date],"mmm"),
    "OYearMonthNum",YEAR([Date])*12+MONTH([Date]),
    "OMonthYear",FORMAT([Date],"mmm-yy")
)

จะได้ตารางใหม่ทันที

เพื่อความชัดเจน ขอเปลี่ยนชื่อคอลัมน์จาก Date เป็น ODate (เพื่อสื่อว่าเป็น OrderDate) และปรับ Data type เป็น Date

ทำให้ตารางนี้เป็น Date Table โดยคลิก Table tools/ Mark as date table/ Mark as date table

จะมีหน้าต่างถามว่าคอลัมน์ใดเป็น Date column เลือกคอลัมน์ ODate จากนั้นคลิก OK

Note: Data Type ของ Date column ต้องเป็น Date ห้ามมีข้อมูลซ้ำ และห้ามข้ามวัน (เช่น ห้ามเว้นวันหยุด ห้ามเว้นวันเสาร์-อาทิตย์)

สังเกตว่าเมื่อ Mark as date table แล้ว ไอคอนของคอลัมน์ ODate จะเปลี่ยนเป็นแบบนี้

ปรับคอลัมน์ OMonthYear ให้เรียงตามคอลัมน์ OYearMonthNum
โดยคลิกคอลัมน์ OMonthYear เลือกเมนู Column tools/ Sort by column/ OYearMonthNum

2. สร้างตาราง DeliverDate

เมื่อสักครู่เราสร้างตารางสำหรับ Order Date คราวนี้มาสร้างตารางสำหรับ Delivery Date บ้าง โดยการเขียนสูตร DAX และทำทุกขั้นตอนเหมือนกัน
เริ่มจากคลิก Table tools/ New Table

พิมพ์สูตร

tDeliveryDate = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "DYear", YEAR([Date]),
    "DMonthNum",MONTH([Date]),
    "DMonth",FORMAT([Date],"mmm"),
    "DYearMonthNum",YEAR([Date])*12+MONTH([Date]),
    "DMonthYear",FORMAT([Date],"mmm-yy")
)

จะได้ตารางใหม่ทันที

เพื่อความชัดเจน ขอเปลี่ยนชื่อคอลัมน์จาก Date เป็น DDate (เพื่อสื่อว่าเป็น DeliveryDate) และปรับ Data type เป็น Date

ทำให้ตารางนี้เป็น Date Table โดยคลิก Table tools/ Mark as date table/ Mark as date table

จะมีหน้าต่างถามว่าคอลัมน์ใดเป็น Date column เลือกคอลัมน์ ODate จากนั้นคลิก OK

ปรับคอลัมน์ DMonthYear ให้เรียงตามคอลัมน์ DYearMonthNum
โดยคลิกคอลัมน์ DMonthYear เลือกเมนู Column tools/ Sort by column/ DYearMonthNum

3. สร้างความสัมพันธ์กับตาราง Order

สร้างความสัมพันธ์ระหว่างตาราง Order (tOrder) ตาราง Order Date (tOrderDate) และตาราง Delivery Date (tDeliveryDate)

โดยเชื่อม ODate กับ OrderDate (ใช้มือลากตรง ๆ ตามรูปด้านล่าง)

และเชื่อม DeliveryDate กับ DDate

สังเกตว่าเมื่อเชื่อมความสัมพันธ์แล้ว Date Hierarchy ที่คอลัมน์ OrderDate และ DeliveryDate จะหายไป

แปลว่าคอลัมน์เหล่านั้นไม่ถูกสร้างที่ ‘หลังบ้าน’ แล้ว ขนาดไฟล์ก็เล็กลง ^_^

4. สร้าง Measure คำนวณ

สเต็ปสุดท้ายคือการสร้าง Measure นับจำนวนออเดอร์ โดยคลิกตาราง Order (tOrder) Table tools/ New measure

ใช้สูตร DAX ง่าย ๆ คือ

#Order = COUNTROWS(tOrder)

ปรับให้ measure อ่านง่ายโดยใส่คอมมา หรือคลิก Measure tools/ เลือกไอคอนคอมมา ( , )

สร้าง Visual Matrix โดยกำหนดค่าดังนี้
Rows: OMonthYear (เพื่อใช้ Order Month Year เป็นข้อมูลในแนวนอน)
Columns: DMonthYear (เพื่อใช้ Delivery Month Year เป็นข้อมูลในแนวตั้ง)
Value: #Order (Measure ที่เพิ่งสร้าง)

สร้าง Slicer เพื่อให้วิเคราะห์ข้อมูลง่ายขึ้น โดยใช้ OYear (Order Year) เป็น Slicer

ทดลองคลิก Slicer ปี 2019 ได้ผลลัพธ์แบบนี้

จากภาพ จะพบว่าออเดอร์ที่สั่งในเดือน Nov-19 มีทั้งหมด 2,538 แต่ใช้เวลาส่ง (Delivery) 4 เดือน ด้วยจำนวนดังนี้
Nov-19: 682
Dec-19: 901
Jan-20: 918
Feb-20: 37

“ว้าว! แบบนี้เลยค่ะที่ต้องการ ดีจัง ไม่ต้องสร้างคอลัมน์เพิ่มด้วย” น้องนิจิประกบมือเข้าหากันเบา ๆ อิริยาบทของเธอน่ารักมาก
“ใช่แล้วครับ การสร้าง measure ดีกว่าสร้างคอลัมน์เพิ่ม แถมคำนวณเร็วกว่าด้วย” ผมรู้สึกภูมิใจเล็ก ๆ ยิ่งเห็นเธอยกมือปัดผมขึ้นมาทัดหู ยิ่งอยากยืดโมเมนต์นี้ให้นาน

“เอ๋!” จู่ ๆ น้องนิจิก็โพล่งขึ้น
“ทะ ทำไมหรือครับ” เสียงเธอทำผมตื่นจากภวังค์
“พอคลิก Slicer เป็นปี 2020 ทำไมมีคอลัมน์ว่างล่ะคะ?”

“อ๋อ เพราะออเดอร์นั้นยังไม่ถูกส่ง ข้อมูลใน DeliveryDate เป็นค่าว่าง จึงแสดงชื่อเดือนเป็นค่าว่างเช่นกันครับ” ผมตอบ
“นิจิเกรงว่า.. ถ้าขึ้นชื่อเดือนโล่ง ๆ แบบนี้คงไม่ดี มีทางเปลี่ยนเป็นคำว่า “Not Delivered” ไหมคะ?”
“มีครับ”
“จริงหรือคะ งั้นถ้าให้คอลัมน์นี้อยู่ขวาสุดก่อนคอลัมน์ Total ด้วยได้ไหมคะ?”

รีพอร์ตที่น้องนิจิพูดถึง หน้าตาแบบนี้ครับ

ทำด้วย Power BI ได้แน่นอน แต่ ep.2 ยาวมากแล้ว ขอเขียนต่อใน ep.3 ครับ ^_^

สำหรับผู้ที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาได้ที่ลิงก์นี้ครับ
https://drive.google.com/file/d/1HtZUZrm9u9GV1mEzhPX7N3pSetk6u92n/view?usp=sharing

“มันจึงเป็นความรักที่ไม่ถึงกับสุข เป็นความทุกข์ที่ไม่ถึงกับเศร้า เป็นความรักที่ทั้งซึ้งทั้งเหงาอยู่ด้วยกัน” ทำไมอยู่ดี ๆ ผมนึกถึงเพลงนี้ขึ้นมาล่ะ ..

อย่าลืมแชร์บทความให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^