Thep Excel
SUMMARIZECOLUMNSสร้างตารางสรุปแบบ Dynamic Query
SUMMARIZECOLUMNS เป็นฟังก์ชันหลักสำหรับสร้างตารางสรุปผล โดยจัดกลุ่มตามคอลัมน์ที่กำหนด พร้อมเพิ่มคอลัมน์คำนวณจากเมเชอร์ และสามารถใส่เงื่อนไขกรองได้ทันที
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| groupBy_columnName | Column | คอลัมน์สำหรับจัดกลุ่ม (Group By) ต้องระบุแบบ Fully Qualified เช่น Table[Column] สามารถระบุได้หลายคอลัมน์ แต่ห้ามซ้ำกัน (แต่ละคอลัมน์จะถูก Cross-Join ถ้าต่างตาราง หรือ Auto-Exist ถ้าตารางเดียวกัน) |
| [filterTable]ไม่บังคับ | Table | ตารางเงื่อนไขที่ใช้กรองบริบทก่อนทำการ Group By สามารถใช้ฟังก์ชันอย่าง FILTER, TREATAS, KEEPFILTERS, REMOVEFILTERS, NONVISUAL ได้ |
| [name]ไม่บังคับ | Text | ชื่อคอลัมน์ใหม่ที่ต้องการเพิ่มในตารางผลลัพธ์ (จับคู่กับ expression) |
| [expression]ไม่บังคับ | Expression | นิพจน์ DAX ที่คืนค่าเดี่ยว (ไม่ใช่ตาราง) สำหรับคำนวณค่าในคอลัมน์ใหม่ สามารถระบุได้หลายคู่ name/expression |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
SUMMARIZECOLUMNS เป็นฟังก์ชันสำหรับสร้างตารางสรุปผล (summary table) แบบ Dynamic โดยการจัดกลุ่มตามคอลัมน์ที่ระบุ พร้อมเพิ่มคอลัมน์คำนวณจากนิพจน์หรือเมเชอร์ และที่เจ๋งคือสามารถใส่ตารางเงื่อนไข (filter table) เพื่อจำกัดบริบทได้ทันทีโดยไม่ต้องห่อด้วย CALCULATE
ฟังก์ชันนี้ทำงานได้ทั้งใน Calculated Table, Measure และ Calculated Column (ยกเว้น DirectQuery) และเป็นฟังก์ชันหลักที่ใช้สำหรับ Query DAX ใน DAX Studio หรือ Power BI Report Builder
ที่น่าสนใจคือ SUMMARIZECOLUMNS มีฟีเจอร์พิเศษคือสามารถสร้าง Subtotal/Rollup ได้โดยใช้ ROLLUPADDISSUBTOTAL และควบคุมระดับการ Rollup ด้วย ROLLUPGROUP
ส่วนตัวผมคิดว่า SUMMARIZECOLUMNS เป็นฟังก์ชันที่ทรงพลังมาก แต่ก็ซับซ้อนพอสมควร โดยเฉพาะเรื่อง Filter Context และ Auto-Exists Behavior ถ้าจะใช้ให้เก่ง แนะนำให้เข้าใจหลักการ Filter Context และ Lineage ของ DAX ให้ดีก่อน 😎
How it works
สร้างตารางสรุปเพื่อใช้ตรวจสอบ/ส่งออก
สร้างตารางผลรวมตามมิติที่ต้องการ แล้วใช้ EVALUATE เพื่อดูผลลัพธ์ หรือส่งต่อให้ฟังก์ชันอื่น
สรุปผลพร้อมจำกัดบริบทด้วย criteria table
ใส่ตารางเงื่อนไขเพื่อกำหนดว่าให้สรุปเฉพาะบางกลุ่ม/บางค่าที่สนใจ
Examples
ตัวอย่างพื้นฐาน – สรุปยอดขายตามหมวดสินค้าEVALUATE SUMMARIZECOLUMNS( 'Product'[Category], "Total Sales", SUM(Sales[Amount]) )
ระบุ Group By Column คือ Product[Category] และเพิ่มคอลัมน์คำนวณชื่อ "Total Sales" โดยใช้ SUM(Sales[Amount])
ตัวอย่างการจัดกลุ่มหลายมิติEVALUATE SUMMARIZECOLUMNS( 'Product'[Category], 'Date'[Year], "Total Sales", [Sales Amount], "Total Quantity", SUM(Sales[Quantity]) )
จัดกลุ่มทั้ง Product[Category] และ Date[Year] พร้อมเพิ่ม 2 คอลัมน์คำนวณ (Total Sales และ Total Quantity)
ตัวอย่างการใช้ Filter Table ด้วย TREATASEVALUATE SUMMARIZECOLUMNS( 'Product'[Category], TREATAS({"Bikes", "Accessories"}, 'Product'[Category]), "Sales", [Sales Amount] )
ใช้ TREATAS เพื่อจำกัดบริบทให้แสดงเฉพาะหมวดที่กำหนด โดยไม่ต้องใช้ CALCULATE
ตัวอย่างการใช้ FILTER เพื่อกรองเงื่อนไขEVALUATE SUMMARIZECOLUMNS( 'Product'[Category], 'Customer'[Education], FILTER('Customer', 'Customer'[FirstName] = "John"), "Sales", [Sales Amount] )
ใช้ FILTER เพื่อจำกัดเฉพาะลูกค้าที่ชื่อ John โดย Filter จะมีผลเฉพาะเมื่อ Customer[Education] อยู่ใน GroupBy Columns (ตาม SelfValue Semantics ของ DAX 2024+)
ตัวอย่างการสร้าง Subtotal ด้วย ROLLUPADDISSUBTOTALEVALUATE SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL('Product'[Category], "IsSubtotal"), "Total Sales", SUM(Sales[Amount]) )
ROLLUPADDISSUBTOTAL จะเพิ่มแถว Subtotal โดยมีคอลัมน์ IsSubtotal เป็น TRUE สำหรับแถว Subtotal และ FALSE สำหรับแถวรายละเอียด
ตัวอย่างการใช้ NONVISUAL เพื่อแยก Filter กับ MeasureEVALUATE SUMMARIZECOLUMNS( 'Date'[CalendarYear], NONVISUAL(TREATAS({2023, 2024}, 'Date'[CalendarYear])), "Sales", [Sales Amount], "All Years Sales", CALCULATE([…
NONVISUAL ทำให้ TREATAS มีผลเฉพาะกับ GroupBy Column แต่ไม่กระทบกับการคำนวณ Measure ทำให้ All Years Sales คำนวณจากทุกปีได้
ตัวอย่าง Edge Case – แถวที่ Expression เป็น BLANK จะถูกตัดออกEVALUATE SUMMARIZECOLUMNS( 'Product'[Category], "High Value Sales", IF(SUM(Sales[Amount]) > 100000, SUM(Sales[Amount])) )
SUMMARIZECOLUMNS จะคืนเฉพาะแถวที่มีอย่างน้อยหนึ่ง Expression ไม่เป็น BLANK ถ้าต้องการให้แสดงทุกแถว ใช้ IGNORE() ห่อ Expression
FAQs
SUMMARIZECOLUMNS ต่างจาก SUMMARIZE อย่างไร?+
SUMMARIZECOLUMNS เป็นฟังก์ชันใหม่กว่าและออกแบบมาสำหรับการสร้างตารางสรุปโดยเฉพาะ มีประสิทธิภาพดีกว่า SUMMARIZE และรองรับ Filter Table, ROLLUP และ Context Transition ได้ดีกว่า แนะนำให้ใช้ SUMMARIZECOLUMNS แทน SUMMARIZE ในทุกกรณีที่ทำได้
ทำไมบางแถวไม่แสดงในผลลัพธ์?+
SUMMARIZECOLUMNS จะคืนเฉพาะแถวที่มีอย่างน้อยหนึ่ง Expression คืนค่าไม่เป็น BLANK ถ้าทุก Expression เป็น BLANK แถวนั้นจะถูกตัดออก ถ้าต้องการให้แสดงทุกแถว ให้ใช้ IGNORE() ห่อ Expression ที่อาจเป็น BLANK
ใช้ SUMMARIZECOLUMNS ใน Measure ได้ไหม?+
ได้ครับ ตั้งแต่ DAX 2024 (June 2024 Update) ขึ้นไป SUMMARIZECOLUMNS รองรับ Context Transition ใน Measure แล้ว แต่ต้องระวังเรื่อง Performance เพราะ SUMMARIZECOLUMNS จะสร้างตารางทั้งหมดทุกครั้งที่ Evaluate
Filter Table ทำงานอย่างไร?+
Filter Table จะถูกนำไปใช้กรอง Filter Context ก่อนทำการ Cross-Join/Auto-Exist ของ GroupBy Columns แต่ตาม SelfValue Semantics (2024+) Filter จากตารางภายนอกจะไม่มีผลกับ GroupBy Columns ยกเว้นว่าคอลัมน์นั้นอยู่ในตารางเดียวกับ Filter หรือเป็น GroupBy Column เอง
DirectQuery รองรับ SUMMARIZECOLUMNS ไหม?+
รองรับแต่มีข้อจำกัด คือใช้ใน Calculated Column หรือ RLS Rules ไม่ได้ แต่ใช้ใน Measure และ Calculated Table ได้ปกติ
ROLLUPADDISSUBTOTAL ทำอะไร?+
เพิ่มแถว Subtotal/Grand Total ในตารางผลลัพธ์โดยอัตโนมัติ พร้อมเพิ่มคอลัมน์ Boolean เพื่อระบุว่าแถวไหนเป็น Subtotal (TRUE) หรือรายละเอียด (FALSE) เหมาะสำหรับสร้างรายงานที่ต้องการ Summary ทุกระดับ
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ SUMMARIZECOLUMNS จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ADDCOLUMNS เป็นฟังก์ชัน table transformation ที่ใช้สำหรับเพิ่มคอลัมน์ใหม่ (Calculated Columns) เข้าไปในตารางที่มีอยู่เดิม โดยคำนวณค่าในแต่ละแถวผ่าน Row Context และส่งคืนตารางเดิมพร้อมคอลัมน์ใหม่ต่อท้าย
ADDMISSINGITEMS เติมสมาชิกที่หายไปในผลลัพธ์สรุปแบบกลุ่ม เพื่อให้แสดงครบทุกสมาชิกของคอลัมน์ที่ระบุ เหมาะกับรายงานที่ต้องการเห็นรายการที่ไม่มีข้อมูลด้วย
GROUPBY สร้างตารางสรุปโดยจัดกลุ่มตามคอลัมน์ที่กำหนด และเพิ่มคอลัมน์คำนวณแบบกลุ่มต่อกลุ่มได้ โดยใช้ CURRENTGROUP() เข้าถึงแถวภายในกลุ่ม เหมาะกับการคำนวณซ้อนหรือคำนวณจากคอลัมน์ชั่วคราว (local columns)
IGNORE ใช้ภายใน SUMMARIZECOLUMNS เพื่อบอกว่า expression/measure นี้ต้องถูก “ละเว้น” ตอนตัดสินว่าแถวไหนเป็น non-blank ทำให้แถวในผลลัพธ์ไม่ถูกตัดทิ้งเพียงเพราะ measure ที่เลือกละเว้นเป็น BLANK
NATURALJOINUSAGE ทำให้ table expression ถูกเพิ่มเข้าไปในตัวกรอง (filter context) แบบ natural join โดยจับคู่ตามคอลัมน์ชื่อเดียวกัน เป็นฟังก์ชันที่ออกแบบสำหรับการใช้งานขั้นสูงในโมเดลแบบประกอบ (composite models) และ SUMMARIZECOLUMNS
NONVISUAL ใช้ทำเครื่องหมายว่า filter expression นี้เป็น non-visual ซึ่งหมายความว่ามันจะส่งผลต่อการคำนวณ measure แต่จะไม่ไปทำให้แถวที่แสดงในตารางสรุปเปลี่ยนไป มักใช้ร่วมกับ SUMMARIZECOLUMNS เพื่อควบคุมพฤติกรรมของตัวกรองอย่างละเอียด
ROLLUP บอก SUMMARIZE ให้สร้างแถว subtotal เพิ่มเติมตามคอลัมน์ที่กำหนด ทำให้ได้ทั้งรายละเอียดและรวมย่อยในผลลัพธ์เดียว
ROLLUPADDISSUBTOTAL ใช้ใน SUMMARIZECOLUMNS เพื่อเพิ่มแถว roll-up/subtotal และเพิ่มคอลัมน์ IsSubtotal สำหรับแต่ละระดับที่กำหนด ทำให้แยกแถว subtotal ออกจากรายละเอียดได้ง่าย
ROLLUPGROUP ระบุชุดคอลัมน์ที่จะถูกพิจารณาเป็น “กลุ่มเดียว” สำหรับการสร้าง subtotal/roll-up ช่วยลด subtotal บางระดับ และมักใช้ร่วมกับ ROLLUP ใน SUMMARIZE
ROLLUPISSUBTOTAL ใช้ใน ADDMISSINGITEMS เพื่อจับคู่กลุ่ม roll-up กับคอลัมน์ IsSubtotal ที่สร้างจาก ROLLUPADDISSUBTOTAL ทำให้การเติมรายการที่ขาดหายทำงานได้ถูกต้องเมื่อมี roll-up/subtotal rows
คืนตารางย่อยจากตารางต้นทางโดยแบ่งแกน X เป็นช่วงเท่า ๆ กัน และคงแถวที่ให้ค่า Measure ต่ำสุด/สูงสุดในแต่ละช่วง (ต่อซีรีส์) เพื่อช่วยลดจำนวนจุดสำหรับการแสดงผล
สร้างตารางใหม่โดยเลือกเฉพาะคอลัมน์ที่ต้องการจากตารางต้นฉบับและเพิ่มคอลัมน์ที่คำนวณได้ ต่างจาก ADDCOLUMNS ตรงที่ SELECTCOLUMNS เริ่มจากตารางว่างแล้วเพิ่มเฉพาะคอลัมน์ที่ระบุ ทำให้สามารถปรับโครงสร้างตารางและเลือกข้อมูลที่จำเป็นได้อย่างยืดหยุ่น
คืนตารางที่เป็นผลแบบ semi-join ระหว่าง 2 ตาราง พร้อมแทนคอลัมน์ร่วมด้วยดัชนี 0-based ซึ่งคำนวณจากลำดับแถวของตารางอ้างอิงหลังจัดเรียง
SUMMARIZE สร้างตารางสรุปโดยจัดกลุ่มข้อมูลตามคอลัมน์ที่กำหนด คล้าย GROUP BY ใน SQL หรือ Pivot Table ใน Excel คืนค่าตารางที่มีหนึ่งแถวต่อหนึ่ง unique combination ของคอลัมน์ที่เลือก สามารถอ้างถึงคอลัมน์จาก related table ได้โดยตรงโดยไม่ต้องใช้ RELATED มักใช้สร้าง virtual table ใน measure เพื่อทำ intermediate calculations ก่อนใช้ iterator functions อย่าง SUMX, AVERAGEX คำนวณต่อ ⚠️ Best Practice: ใช้ ADDCOLUMNS ครอบ SUMMARIZE แทนการใส่ extension columns ตรงๆ เพื่อ performance และ filter context control ที่ดีกว่า สำหรับ calculated table แนะนำใช้ SUMMARIZECOLUMNS แทนเนื่องจากมี performance ดีกว่าอย่างมาก
คืนตาราง Top N แถวจากตารางที่กำหนด โดยเรียงตามนิพจน์หนึ่งตัวหรือมากกว่า สามารถระบุทิศทางการเรียงลำดับแยกต่างหากสำหรับแต่ละเกณฑ์
ฟังก์ชันเชิงเครื่องมือสำหรับเลือกจำนวนแถวสูงสุด (Top N) ต่อ ระดับในโครงสร้างลำดับชั้น (hierarchy) ที่มีการขยาย/ยุบโหนด
ดึงแถวจากตารางโดยข้ามจำนวนแถวที่กำหนดก่อน แล้วคืนแถวถัดไปตามการเรียงลำดับที่ระบุ มีประสิทธิภาพสูงสำหรับงาน pagination
TREATAS เป็นเสมือนการสร้าง "virtual relationship" โดยไม่ต้องแก้โมเดล ช่วยให้ส่งเงื่อนไขข้ามตารางที่ไม่เชื่อมกัน หรือเมื่อต้องการส่งหลายคีย์พร้อมกัน
FIRSTNONBLANKVALUE คืนค่าแรกที่ไม่เป็น BLANK ของ Expression เมื่อประเมินตามลำดับของ Column ซึ่งมีประโยชน์ในการหาค่าเริ่มต้นหรือค่าแรกที่มีข้อมูลจริง เช่นยอดขายของวันแรกที่มีการขาย
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่