Thep Excel
ALLลบ Filter หรือคืนค่าทุกแถว
ALL มีพฤติกรรมแตกต่างกัน 2 แบบ: ใช้เป็น Table Expression จะคืนทุกแถวในตาราง หรือทุกค่าในคอลัมน์ โดยไม่สนใจ Filter ใดๆ เหมาะสำหรับใช้ร่วมกับ FILTER, COUNTROWS, SUMMARIZE หรือใช้เป็น CALCULATE Modifier เพื่อลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ทำให้สามารถคำนวณ Grand Total หรือหาเปอร์เซ็นต์เทียบกับยอดรวมได้
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| [table_or_column]ไม่บังคับ | table/column | ตารางหรือคอลัมน์ที่ต้องการ – ถ้าระบุตาราง จะคืนทุกแถวหรือลบ Filter ออกจากตารางนั้น ถ้าระบุคอลัมน์ จะคืนทุกค่า unique หรือลบ Filter ออกจากคอลัมน์นั้น |
| [column]ไม่บังคับ | column | คอลัมน์ที่ต้องการคืนทุกค่า unique หรือลบ Filter ออกจากคอลัมน์นั้น (ต้องเป็น base column ไม่สามารถใช้ calculated column ได้ สามารถระบุหลายคอลัมน์ได้) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
ALL เป็นฟังก์ชันที่มี 2 บทบาทหลัก ที่แตกต่างกันโดยสิ้นเชิง ขึ้นอยู่กับว่าเราใช้มันในบริบทไหน
.
ที่เจ๋งคือฟังก์ชันเดียวกัน แต่พฤติกรรมต่างกันเลย ตอนแรกผมก็งงเหมือนกันครับ 😅 แต่พอเข้าใจแล้วจะรู้เลยว่ามันเป็น “หัวใจ” ของ DAX จริงๆ
.
บทบาทที่ 1: Table Function เมื่อใช้ ALL ในบริบทที่ต้องการตาราง (เช่น ใน FILTER, COUNTROWS) มันจะคืนตารางที่มีทุกแถว หรือทุกค่าในคอลัมน์ โดยไม่สนใจ Filter Context ที่มีอยู่
.
บทบาทที่ 2: CALCULATE Modifier เมื่อใช้ ALL ภายใน CALCULATE หรือ CALCULATETABLE มันจะไม่คืนตาราง แต่ทำหน้าที่ลบ Filter ออกจาก Filter Context แทน
.
ส่วนตัวผมคิดว่า ถ้าจะเข้าใจ DAX ให้ลึกจริงๆ ต้องเข้าใจ ALL ให้ชัดเจน เพราะมันเกี่ยวข้องกับ Filter Context ซึ่งเป็นแนวคิดพื้นฐานที่สำคัญที่สุดของ DAX เลยก็ว่าได้ 😎
How it works
คำนวณ Grand Total
ใช้ ALL ภายใน CALCULATE เพื่อลบ Filter ทั้งหมดและหายอดรวมสูงสุด ไม่ว่าจะมี Slicer หรือ Filter อะไรอยู่ก็ตาม
คำนวณเปอร์เซ็นต์เทียบยอดรวม
ใช้ ALL เพื่อหาตัวหารที่เป็นยอดรวมทั้งหมด เพื่อคำนวณสัดส่วนของแต่ละส่วนเทียบกับยอดรวม
วนลูปทุกแถวโดยไม่สนใจ Filter
ใช้ ALL เป็น Table Expression ส่งให้ FILTER หรือฟังก์ชัน iterator เพื่อประมวลผลทุกแถว โดยไม่สนใจ Filter Context ภายนอก
ลบ Filter เฉพาะบางคอลัมน์
ระบุคอลัมน์เฉพาะใน ALL เพื่อลบ Filter เฉพาะคอลัมน์นั้นๆ แต่ยังคง Filter อื่นๆ ไว้
Examples
ตัวอย่างที่ 1: Table Function พื้นฐาน – คืนทุกค่าในคอลัมน์All Product Categories = COUNTROWS( ALL(Products[Category]) )
สมมติตาราง Products มี Category: Electronics, Furniture, Clothing, Books, Toys (5 หมวดหมู่)
ALL(Products[Category]) คืนตารางที่มีทุกค่า unique ในคอลัมน์ Category โดยไม่สนใจว่า Slicer หรือ Filter ใดๆ เลือกอะไรอยู่ จากนั้น COUNTROWS นับจำนวนแถว ได้ 5
ที่เจ๋งคือแม้ Slicer กรองเหลือแค่ Electronics ผลลัพธ์ก็ยังคงเป็น 5 เสมอ เพราะ ALL ไม่สนใจ Filter ใดๆ เลย 😎
ตัวอย่างที่ 2: CALCULATE Modifier – หา Grand TotalTotal Sales All Products = CALCULATE( SUM(Sales[Amount]), ALL(Products) )
สมมติยอดขายรวมทั้งหมดคือ 1,250,000 บาท
ALL(Products) ทำหน้าที่ลบ Filter ทั้งหมดที่เกี่ยวกับตาราง Products ออกจาก Filter Context (ไม่ได้คืนตาราง!) จากนั้น CALCULATE จะคำนวณ SUM(Sales[Amount]) โดยไม่มี Filter ใดๆ จึงได้ Grand Total
ส่วนตัวผมใช้เทคนิคนี้บ่อยมากเวลาต้องการแสดง Grand Total ใน Card visual หรือใช้เป็นตัวหารเพื่อคำนวณเปอร์เซ็นต์ครับ 💡
ตัวอย่างที่ 3: คำนวณเปอร์เซ็นต์เทียบยอดรวม% of Total Sales = VAR CurrentSales = SUM(Sales[Amount]) VAR TotalSales = CALCULATE( SUM(Sales[Amount]), ALL(Products[Category]) ) RETURN DIVIDE(CurrentSales, T…
สมมติยอดขาย Electronics = 437,500 บาท, ยอดรวมทุกหมวด = 1,250,000 บาท
CurrentSales คำนวณตาม Filter Context ปัจจุบัน (ได้ 437,500)
TotalSales ใช้ ALL(Products[Category]) เพื่อลบ Filter หมวดสินค้า แต่ยังคง Filter อื่นๆ เช่น ปี หรือภูมิภาค (ได้ 1,250,000)
ผลลัพธ์: 437,500 / 1,250,000 = 0.35 หรือ 35%
สังเกตว่าเราใช้ VAR เพื่อแยกขั้นตอนให้ชัดเจน ส่วนตัวผมแนะนำให้ใช้ VAR เสมอเมื่อคำนวณที่ซับซ้อน อ่านโค้ดง่ายกว่าเยอะครับ 😎
ตัวอย่างที่ 4: Table Function ร่วมกับ FILTERHigh Value Products = CALCULATETABLE( FILTER( ALL(Products), Products[Price] > 10000 ) )
สมมติตาราง Products มีสินค้า 100 รายการ โดยมี 25 รายการที่ราคาเกิน 10,000 บาท
ALL(Products) คืนตารางที่มีทุกแถวในตาราง Products (100 แถว) โดยไม่สนใจ Filter Context
จากนั้น FILTER กรองเอาเฉพาะแถวที่ Price > 10000 (เหลือ 25 แถว)
ผลลัพธ์เป็นตารางที่มี 25 แถว ซึ่งสามารถนำไปใช้ต่อใน CALCULATETABLE หรือฟังก์ชันอื่นๆ ได้
เทคนิคนี้เหมาะมากเวลาต้องการสร้าง virtual table เพื่อคำนวณต่อ โดยไม่ต้องสร้าง Calculated Table จริงๆ ประหยัด memory ด้วยครับ 💡
ตัวอย่างที่ 5: ลบ Filter หลายคอลัมน์พร้อมกันSales Ignoring Category And Color = CALCULATE( SUM(Sales[Amount]), ALL(Products[Category], Products[Color]) )
สมมติมี Slicer กรอง Category = "Electronics" และ Color = "Black" อยู่
ALL(Products[Category], Products[Color]) ลบ Filter ออกจากทั้ง 2 คอลัมน์พร้อมกัน แต่ยังคง Filter อื่นๆ เช่น Date, Region ไว้
จากนั้น CALCULATE คำนวณ SUM(Sales[Amount]) โดยไม่มี Filter หมวดและสี (แต่ยังมี Filter อื่นๆ) ได้ 850,000 บาท
ที่ต้องระวังคือ ถ้าใช้ ALL(Products) แทน มันจะลบ Filter ทุกคอลัมน์ที่เกี่ยวกับ Products รวมถึง Size, Brand ด้วย ซึ่งอาจไม่ใช่สิ่งที่เราต้องการ 😅
FAQs
ALL ต่างจาก REMOVEFILTERS อย่างไร?+
ถ้าใช้เป็น CALCULATE Modifier ทั้งสองทำงานเหมือนกันเลย คือลบ Filter ออกจากตาราง/คอลัมน์ที่ระบุ
แต่ความต่างคือ ALL สามารถใช้เป็น Table Function ได้ด้วย (คืนตาราง) ส่วน REMOVEFILTERS ใช้ได้แค่ใน CALCULATE/CALCULATETABLE เท่านั้น
ส่วนตัวผมแนะนำให้ใช้ REMOVEFILTERS เมื่อต้องการลบ Filter ครับ เพราะชื่อชัดเจนกว่า อ่านโค้ดแล้วรู้เลยว่ากำลังทำอะไร ไม่ต้องมานั่งคิดว่า ALL ตรงนี้คือ Table Function หรือ Modifier 😎
ALL ต่างจาก ALLSELECTED อย่างไร?+
ALL ลบ Filter ทั้งหมด ได้ Grand Total แท้จริง
ALLSELECTED ลบ Filter เฉพาะภายใน Visual (Row Context, Column Context) แต่ยังคง Filter จาก Slicer, Page Filter, Report Filter ไว้
ตัวอย่าง: ถ้า Slicer เลือก Year = 2024 อยู่
– ALL จะได้ยอดรวมทุกปี (2020-2024)
– ALLSELECTED จะได้ยอดรวมเฉพาะ 2024 (ตาม Slicer)
ส่วนตัวผมใช้ ALL เวลาต้องการ "ยอดรวมสุดท้าย" จริงๆ ส่วน ALLSELECTED ใช้เวลาต้องการ "ยอดรวมตามที่ User เลือก" 💡
ทำไมใช้ ALL(Sales) แล้ว Filter ของ Date, Product หายไปด้วย?+
เรื่องนี้เจอบ่อยมากครับ 😅 เพราะ DAX มีแนวคิดที่เรียกว่า "Expanded Table"
เมื่อใช้ ALL กับ Fact Table (เช่น Sales) DAX จะลบ Filter ออกจากทั้ง Fact Table และ Dimension Table ทุกตารางที่มี Relationship แบบ one-to-many ด้วย
ตัวอย่าง: ถ้า Sales มี Relationship ไปยัง Date, Products, Customers
– ALL(Sales) จะลบ Filter จาก Sales, Date, Products, Customers ทั้งหมด
ถ้าต้องการลบเฉพาะบางคอลัมน์ ให้ใช้ ALL(Table[Column]) แทน เช่น ALL(Date[Year]) จะลบแค่ Filter ปีเท่านั้น
ALL() ไม่มี argument ใช้ตอนไหน?+
ALL() ไม่มี argument ใช้ได้เฉพาะใน CALCULATE/CALCULATETABLE เท่านั้น และจะลบ Filter ทั้งหมดออกจาก Filter Context
คำเตือน: ใช้ด้วยความระมัดระวังครับ เพราะมันลบ Filter ทุกอย่างเลย รวมถึง Filter จาก Relationship ด้วย อาจทำให้ได้ผลลัพธ์ที่ไม่ตรงตามที่คาดหวัง 😅
ส่วนตัวผมไม่ค่อยแนะนำให้ใช้ ALL() แบบไม่มี argument ครับ ควรระบุตาราง/คอลัมน์ให้ชัดเจนเสมอ เพื่อควบคุม Filter ได้แม่นยำกว่า
ทำไมใช้ ALL กับ Calculated Column ไม่ได้?+
ALL ต้องใช้กับ base column เท่านั้น ไม่สามารถใช้กับ calculated column ได้
เหตุผลคือ Calculated Column ถูกสร้างขึ้นจาก expression ไม่ใช่คอลัมน์จริงในตาราง ดังนั้น DAX ไม่สามารถ reference ได้แบบ direct
ถ้าต้องการใช้ ALL กับข้อมูลที่คำนวณมา ให้สร้างเป็น measure หรือใช้ table expression แทนครับ
ตัวอย่างที่ผิด: ALL(Products[Calculated Price])
ทางแก้: ใช้ FILTER(ALL(Products), [Price] * 1.1 > 10000) แทน 💡
ALL คืนแถวที่ซ้ำกัน (duplicate rows) ด้วยหรือไม่?+
ขึ้นอยู่กับว่าเราใช้แบบไหนครับ
ALL(Table) คืนทุกแถวรวมถึงแถวที่ซ้ำกันด้วย (duplicate rows)
ALL(Column) หรือ ALL(Column1, Column2, …) คืนเฉพาะค่า unique combinations ไม่มี duplicate
ตัวอย่าง: ถ้า Sales มี 1000 transactions
– ALL(Sales) คืน 1000 แถว (รวม duplicate)
– ALL(Sales[ProductID]) คืนเฉพาะ ProductID ที่ไม่ซ้ำกัน (สมมติ 50 products)
เรื่องนี้สำคัญมากเวลาใช้ COUNTROWS ครับ ต้องระวังว่าเรานับอะไรอยู่ 😎
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ ALL จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ALLCROSSFILTERED ใช้ล้างตัวกรองทั้งหมดออกจากตารางที่ระบุ รวมถึงตัวกรองที่ส่งผ่านมาจากตารางอื่น (Cross-filter) ใช้ได้เฉพาะเป็น Modifier ใน CALCULATE/CALCULATETABLE เท่านั้น ไม่สามารถคืนค่าเป็นตารางได้
ALLEXCEPT ใช้สำหรับ “ล้างตัวกรองเกือบทั้งหมด” โดยคงตัวกรองไว้เฉพาะคอลัมน์ที่ระบุ เหมาะกับการคำนวณ Sub-total/สัดส่วนภายในกลุ่ม เช่น ยอดขายต่อหมวดหมู่ โดยไม่สนตัวกรองอื่น ๆ แนวคิดหลักคือ ลบตัวกรองของตาราง แล้วคงตัวกรองของคอลัมน์ที่เลือกไว้
ALLNOBLANKROW ทำงานคล้าย ALL แต่มีจุดเด่นคือ “ตัดแถวว่าง” ที่ระบบสร้างขึ้นอัตโนมัติเมื่อข้อมูลหลุดความสัมพันธ์ เช่น คีย์ในตารางข้อเท็จจริงไม่พบในตารางมิติ จึงเหมาะกับการคำนวณยอดรวม/สัดส่วนที่ไม่ต้องการให้แถวว่างเข้ามาปนผลลัพธ์ และยังใช้เป็นตัวช่วยตรวจจับปัญหาคุณภาพข้อมูลในโมเดลได้ด้วย
ALLSELECTED เป็น DAX function ที่ลบ filter context จาก Visual (row และ column filters) แต่คง filter context จาก Slicer, Page Filter และ Report Filter ไว้ ทำให้สามารถคำนวณ Visual Total ได้ ซึ่งเป็นยอดรวมของข้อมูลที่ผู้ใช้เลือกดูในปัจจุบัน ไม่ใช่ Grand Total ทั้งหมด ฟังก์ชันนี้ทำงานผ่าน Shadow Filter Context ซึ่งเป็น filter context ที่ DAX Engine เก็บไว้ก่อนที่ Visual จะเพิ่ม row/column filter เมื่อเรียก ALLSELECTED จะเรียกคืน shadow context นี้ ใช้ร่วมกับ CALCULATE และ SUM, AVERAGE, DIVIDE เพื่อคำนวณ Visual Total, Percentage of Selected, Dynamic Benchmark, Ranking within Selection และ Time Intelligence ที่เคารพ Slicer ต้องระวังการใช้งานใน iterator functions เช่น SUMX, FILTER เพราะอาจให้ผลลัพธ์ที่ไม่คาดคิด และระวัง Expanded Table Caveat ที่จะลบ filter ของ related table ด้วย
CALCULATE เป็นฟังก์ชันหลักที่สำคัญที่สุดใน DAX ใช้สำหรับ evaluate expression ภายใต้ filter context ที่ถูกปรับเปลี่ยน สามารถเพิ่มตัวกรองใหม่ ลบตัวกรองเดิม หรือแทนที่ filter ที่มีอยู่ได้ รองรับทั้ง Boolean expression และ table expression เป็น filter arguments พร้อม filter modifier functions เช่น REMOVEFILTERS, ALL, ALLEXCEPT, KEEPFILTERS เพื่อควบคุมการกรองอย่างละเอียด มีพฤติกรรมพิเศษคือ context transition ที่เปลี่ยน row context เป็น filter context โดยอัตโนมัติ ทำให้เป็นเครื่องมือหลักในการสร้าง measure ที่ซับซ้อนและ calculated column ที่ต้องใช้ aggregation
CALCULATETABLE evaluate table expression ภายใต้ filter context ที่ถูกปรับเปลี่ยน แล้วคืนค่าเป็น table (ตาราง) ซึ่งแตกต่างจาก CALCULATE ที่คืนค่าเป็น scalar value รองรับ filter arguments 3 รูปแบบ: Boolean expression, table expression, และ filter modifier functions (REMOVEFILTERS, ALL, KEEPFILTERS, USERELATIONSHIP, CROSSFILTER) มีพฤติกรรมเหมือน CALCULATE ในทุกแง่มุมของการจัดการ filter context รวมถึง context transition แต่เหมาะสำหรับการสร้าง intermediate table ที่ถูกกรองแล้วส่งต่อให้ iterator functions หรือใช้ใน calculated table มักมี performance ดีกว่า FILTER ใน simple filtering scenarios เพราะ DAX engine สามารถทำ cardinality estimation และ optimization ได้ดีกว่า
FILTER ใช้กรองแถวในตารางตามเงื่อนไขที่ซับซ้อน โดยวนลูปทุกแถวและประเมินเงื่อนไข เหมาะกับการกรองด้วย Measure หรือ Expression ซึ่ง Boolean Expression ทำไม่ได้ แต่ต้องระวังเรื่อง Performance เพราะเป็น Iterator Function ที่ช้ากว่า Boolean Expression ดังนั้นควรใช้เฉพาะเมื่อจำเป็น และอย่าใช้ FILTER กับ RELATED เพื่อกรองข้ามตาราง ให้กรองที่ Dimension Table โดยตรงแทน
KEEPFILTERS เป็นฟังก์ชันปรับตัวกรองที่ใช้ภายใน CALCULATE เพื่อ “คงตัวกรองเดิมไว้” แล้วนำตัวกรองใหม่มารวมกันแบบ AND (ตัดกันเฉพาะส่วนที่ตรงกัน) แทนพฤติกรรมปกติที่มักเขียนทับตัวกรองเดิมของคอลัมน์เดียวกัน
REMOVEFILTERS ลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ใช้ได้ใน CALCULATE เท่านั้น เทียบเท่า ALL เมื่อใช้เป็น CALCULATE Modifier แต่ชัดเจนและอ่านโค้ดง่ายกว่า แนะนำใช้แทน ALL เพราะสื่อความหมายได้ดีกว่า
DISTINCT คืนค่าตารางที่มีคอลัมน์เดียว ซึ่งประกอบด้วยค่าที่ไม่ซ้ำกันจากคอลัมน์ที่ระบุ โดยไม่รวม Blank Row ที่เกิดจาก Invalid Relationship ความแตกต่างหลักจาก VALUES คือ DISTINCT จะซ่อน Data Quality Issues ในขณะที่ VALUES จะเตือนให้เห็น
FILTERS คืนค่าเป็นตาราง (table) ของค่าที่ถูกกรองโดยตรง (direct filters) บนคอลัมน์ที่ระบุ เช่น ค่าที่ผู้ใช้เลือกจากตัวแบ่งส่วนข้อมูล ต่างจาก ALL/ALLEXCEPT ที่เคลียร์ตัวกรอง FILTERS ช่วยตรวจสอบและจัดการบริบทตัวกรองอย่างชาญฉลาด
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 ดีกว่าอย่างมาก
VALUES ดึงค่าที่ไม่ซ้ำจากคอลัมน์ หรือดึงแถวทั้งหมดจากตาราง โดยรวมแถว BLANK ที่เกิดจากข้อผิดพลาดของความสัมพันธ์ (Referential Integrity Violations) ส่วน DISTINCT จะไม่รวมแถว BLANK นี้
COUNTROWS เป็นฟังก์ชันการรวม (Aggregation) ที่นับจำนวนแถวในตารางที่ระบุ ไม่ว่าจะเป็นตารางจริงในโมเดลหรือตารางเสมือน (Virtual Table) จากฟังก์ชันอื่นๆ เช่น FILTER, VALUES, DISTINCT นับเท่าไหร่? ผลลัพธ์ก็คือจำนวนแถวเต็ม ๆ
DIVIDE ทำการหารพร้อม Alternate Result หากตัวหารเป็นศูนย์หรือ BLANK ช่วยให้ Measure ไม่เกิด error และควบคุมค่าที่ต้องการแสดงได้
EXTERNALMEASURE ใช้สำหรับการวิเคราะห์ข้อมูล DAX
ISFILTERED ตรวจสอบว่าคอลัมน์ที่ระบุมีตัวกรองโดยตรงหรือไม่ ใช้เพื่อสร้าง Dynamic Measures ที่เปลี่ยนพฤติกรรมตามการกรองของผู้ใช้
RANKX เป็น iterator function ที่ iterate ทุกแถวใน table เพื่อประเมิน expression แล้วคืนอันดับของค่าใน current context เมื่อเทียบกับค่าทั้งหมด สามารถกำหนดทิศทางการเรียง (ASC/DESC) และวิธีจัดการอันดับเสมอ (Skip/Dense) ใช้บ่อยกับ ALL/ALLSELECTED เพื่อควบคุมชุดข้อมูลที่เปรียบเทียบ เหมาะสำหรับการจัดอันดับสินค้า ลูกค้า ภูมิภาค และ Top/Bottom analysis
TOTALYTD คำนวณยอดสะสมตั้งแต่ต้นปี (Year-to-Date) ของ expression ใดๆ โดยอิงจาก Date table และสามารถกำหนด fiscal year end date เพื่อรองรับปีงบประมาณที่ไม่ตรงกับปีปฏิทินได้
.
ส่วนตัวผมใช้บ่อยมากตอนต้องวิเคราะห์ยอดขาย KPI และรายงานทางการเงินที่ต้องการติดตามความก้าวหน้าตลอดทั้งปีครับ
USERELATIONSHIP ช่วยให้คุณใช้ relationship ที่ไม่ใช่ active หรือเปลี่ยน relationship ที่ใช้ในการคำนวณ เหมาะสำหรับสถานการณ์เช่น sales data ที่มีหลาย date columns (OrderDate, ShippingDate, DeliveryDate)
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่