Thep Excel
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| table | table | ตารางที่ต้องการล้างตัวกรอง (เช่น Sales, Product) | |
| column1 | column | คอลัมน์แรกที่ต้องการ “ยกเว้น” ไม่ให้ถูกล้างตัวกรอง (คง Filter ไว้) ต้องอยู่ในตารางเดียวกับ table | |
| [column2]ไม่บังคับ | column | ไม่มีคอลัมน์เพิ่มเติม | คอลัมน์เพิ่มเติมที่ต้องการคงตัวกรองไว้ สามารถใส่ได้หลายคอลัมน์ |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
ALLEXCEPT เป็นฟังก์ชันที่มี 2 พฤติกรรมแตกต่างกัน ขึ้นอยู่กับบริบทการใช้งาน:
1. Table Function (ใช้นอก CALCULATE)
คืนค่า ตารางจริง ที่มีแถวทั้งหมดในตาราง โดยไม่สนตัวกรองใดๆ เหมือน ALL แต่ยังคง Filter ที่ระบุไว้ในคอลัมน์ที่เลือก มักใช้ใน FILTER, COUNTROWS
2. CALCULATE Modifier (ใช้ภายใน CALCULATE)
ทำหน้าที่ ลบตัวกรองทั้งหมดยกเว้นคอลัมน์ที่ระบุ ไม่ได้คืนค่าตาราง แต่แก้ไข Filter Context ให้ CALCULATE มีประโยชน์มากในการคำนวณ Sub-total ตาม Dimension เฉพาะ
หมายเหตุ: ALLEXCEPT(Table, Columns) = ALL(Table) ลบคอลัมน์ยกเว้น + เพิ่ม Filter คอลัมน์ที่ยกเว้นกลับ แต่ระวังเรื่อง Expanded Table
How it works
ใช้เป็น Table Expression เพื่อวนลูป (คงตัวกรองบางคอลัมน์)
ใช้ ALLEXCEPT ส่งคืนตารางที่ยังคงตัวกรองของคอลัมน์ที่ระบุไว้ แต่ล้างตัวกรองอื่น ๆ ออก เหมาะกับการทำงานร่วมกับ FILTER และ COUNTROWS เมื่อต้องการยึด “กลุ่ม” บางมิติไว้
ใช้ใน CALCULATE เพื่อคำนวณยอดรวมภายในกลุ่ม (Sub-total)
ใช้ ALLEXCEPT ภายใน CALCULATE เพื่อลบ Filter ทั้งหมดยกเว้น Category หายอดรวมต่อ Category โดยไม่สน Filter อื่นๆ
คำนวณ % ภายในกลุ่มเดียวกัน
คำนวณสัดส่วน % ภายในกลุ่ม เช่น % ยอดขายของแต่ละสินค้าเทียบกับหมวดหมู่ของมันเอง
Examples
ตัวอย่างที่ 1: CALCULATE Modifier – Sub-total ต่อหมวดหมู่Category Total = CALCULATE( SUM(Sales[Amount]), ALLEXCEPT(Sales, Product[Category]) )
ALLEXCEPT(Sales, Product[Category]) ลบ Filter ทั้งหมดจาก Sales ยกเว้น Product[Category] ทำให้ได้ยอดรวมของทั้งหมวดหมู่ แม้จะมี Filter สินค้า/ปี/ภูมิภาคอื่นๆ
ตัวอย่างที่ 2: CALCULATE Modifier – % Share ใน Category% of Category = DIVIDE( [Sales Amount], CALCULATE( [Sales Amount], ALLEXCEPT(Sales, Product[Category]) ) )
ตัวหารใช้ ALLEXCEPT เพื่อหายอดรวมของหมวดหมู่เดียวกัน แต่ละสินค้าจะแสดง % ว่าขายได้เท่าไรเทียบกับหมวดหมู่ตัวเอง
ตัวอย่างที่ 3: CALCULATE Modifier – ยกเว้นหลายคอลัมน์Sales By Year Category = CALCULATE( SUM(Sales[Amount]), ALLEXCEPT( Sales, 'Date'[Year], Product[Category] ) )
ALLEXCEPT ยกเว้นทั้ง Year และ Category ลบ Filter อื่นๆ ทั้งหมด (สินค้า, เดือน, ภูมิภาค) ได้ Sub-total ตาม 2 มิติ
ตัวอย่างที่ 4: Table Function – นับสินค้าในหมวดหมู่Products In Category = COUNTROWS( ALLEXCEPT( Product, Product[Category] ) )
ALLEXCEPT(Product, Product[Category]) คืนตารางสินค้าทั้งหมดในหมวดหมู่เดียวกันกับแถวปัจจุบัน COUNTROWS นับจำนวน
FAQs
พฤติกรรมของ ALLEXCEPT แตกต่างกันอย่างไรระหว่าง Table Function และ CALCULATE Modifier?+
เมื่อใช้นอก CALCULATE (เช่น ใน FILTER, COUNTROWS) ALLEXCEPT จะคืนตารางจริงที่มีทุกแถวแต่ยังคง Filter บางคอลัมน์ แต่เมื่อใช้ใน CALCULATE มันจะไม่คืนตาราง แต่ทำหน้าที่ลบ Filter ทั้งหมดยกเว้นคอลัมน์ที่ระบุ
ALLEXCEPT เทียบเท่ากับอะไร?+
ALLEXCEPT(Table, Col1, Col2) เทียบเท่ากับ ALL(Table) + VALUES(Col1) + VALUES(Col2) แต่สั้นกว่าและอ่านง่ายกว่า เป็น Shortcut ที่นิยมใช้มาก
ทำไมต้องระวังเรื่อง Expanded Table?+
ALLEXCEPT(Sales, Product[Category]) จะลบ Filter จากทั้ง Sales และ Dimension Tables ที่เชื่อมด้วย Relationship (Date, Customer, Store) แต่จะเพิ่ม Filter Product[Category] กลับ ถ้าต้องการควบคุมแม่นยำกว่า แนะนำใช้ REMOVEFILTERS แทน
ควรใช้ ALLEXCEPT หรือ REMOVEFILTERS?+
ALLEXCEPT สั้นกว่าเมื่อต้องการยกเว้นคอลัมน์น้อยๆ แต่ REMOVEFILTERS ชัดเจนกว่าเมื่อต้องการลบคอลัมน์เฉพาะ ขึ้นอยู่กับกรณี ถ้ามีคอลัมน์เยอะแนะนำใช้ REMOVEFILTERS
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ ALLEXCEPT จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ALL มีพฤติกรรมแตกต่างกัน 2 แบบ: ใช้เป็น Table Expression จะคืนทุกแถวในตาราง หรือทุกค่าในคอลัมน์ โดยไม่สนใจ Filter ใดๆ เหมาะสำหรับใช้ร่วมกับ FILTER, COUNTROWS, SUMMARIZE หรือใช้เป็น CALCULATE Modifier เพื่อลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ทำให้สามารถคำนวณ Grand Total หรือหาเปอร์เซ็นต์เทียบกับยอดรวมได้
ALLCROSSFILTERED ใช้ล้างตัวกรองทั้งหมดออกจากตารางที่ระบุ รวมถึงตัวกรองที่ส่งผ่านมาจากตารางอื่น (Cross-filter) ใช้ได้เฉพาะเป็น Modifier ใน CALCULATE/CALCULATETABLE เท่านั้น ไม่สามารถคืนค่าเป็นตารางได้
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 โดยตรงแทน
REMOVEFILTERS ลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ใช้ได้ใน CALCULATE เท่านั้น เทียบเท่า ALL เมื่อใช้เป็น CALCULATE Modifier แต่ชัดเจนและอ่านโค้ดง่ายกว่า แนะนำใช้แทน ALL เพราะสื่อความหมายได้ดีกว่า
FILTERS คืนค่าเป็นตาราง (table) ของค่าที่ถูกกรองโดยตรง (direct filters) บนคอลัมน์ที่ระบุ เช่น ค่าที่ผู้ใช้เลือกจากตัวแบ่งส่วนข้อมูล ต่างจาก ALL/ALLEXCEPT ที่เคลียร์ตัวกรอง FILTERS ช่วยตรวจสอบและจัดการบริบทตัวกรองอย่างชาญฉลาด
VALUES ดึงค่าที่ไม่ซ้ำจากคอลัมน์ หรือดึงแถวทั้งหมดจากตาราง โดยรวมแถว BLANK ที่เกิดจากข้อผิดพลาดของความสัมพันธ์ (Referential Integrity Violations) ส่วน DISTINCT จะไม่รวมแถว BLANK นี้
COUNTROWS เป็นฟังก์ชันการรวม (Aggregation) ที่นับจำนวนแถวในตารางที่ระบุ ไม่ว่าจะเป็นตารางจริงในโมเดลหรือตารางเสมือน (Virtual Table) จากฟังก์ชันอื่นๆ เช่น FILTER, VALUES, DISTINCT นับเท่าไหร่? ผลลัพธ์ก็คือจำนวนแถวเต็ม ๆ
RANKX เป็น iterator function ที่ iterate ทุกแถวใน table เพื่อประเมิน expression แล้วคืนอันดับของค่าใน current context เมื่อเทียบกับค่าทั้งหมด สามารถกำหนดทิศทางการเรียง (ASC/DESC) และวิธีจัดการอันดับเสมอ (Skip/Dense) ใช้บ่อยกับ ALL/ALLSELECTED เพื่อควบคุมชุดข้อมูลที่เปรียบเทียบ เหมาะสำหรับการจัดอันดับสินค้า ลูกค้า ภูมิภาค และ Top/Bottom analysis
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่