Thep Excel
ALLEXCEPT – ล้างตัวกรองทั้งหมดยกเว้นคอลัมน์ที่ระบุ
ALLEXCEPT ใช้สำหรับ “ล้างตัวกรองเกือบทั้งหมด” โดยคงตัวกรองไว้เฉพาะคอลัมน์ที่ระบุ เหมาะกับการคำนวณ Sub-total/สัดส่วนภายในกลุ่ม เช่น ยอดขายต่อหมวดหมู่ โดยไม่สนตัวกรองอื่น ๆ แนวคิดหลักคือ ลบตัวกรองของตาราง แล้วคงตัวกรองของคอลัมน์ที่เลือกไว้
=ALLEXCEPT(<table>, <column1>)
Function Metrics
Popularity
7/10
Difficulty
6/10
Usefulness
8/10
Syntax & Arguments
=ALLEXCEPT(<table>, <column1>)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| table | table | Yes | ตารางที่ต้องการล้างตัวกรอง (เช่น Sales, Product) | |
| column1 | column | Yes | คอลัมน์แรกที่ต้องการ “ยกเว้น” ไม่ให้ถูกล้างตัวกรอง (คง Filter ไว้) ต้องอยู่ในตารางเดียวกับ table | |
| column2 | column | Optional | ไม่มีคอลัมน์เพิ่มเติม | คอลัมน์เพิ่มเติมที่ต้องการคงตัวกรองไว้ สามารถใส่ได้หลายคอลัมน์ |
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]) )
Category Total =
CALCULATE(
SUM(Sales[Amount]),
ALLEXCEPT(Sales, Product[Category])
)
ยอดขายรวมต่อหมวดหมู่
ตัวอย่างที่ 2: CALCULATE Modifier – % Share ใน Category
% of Category = DIVIDE( [Sales Amount], CALCULATE( [Sales Amount], ALLEXCEPT(Sales, Product[Category]) ) )
% of Category =
DIVIDE(
[Sales Amount],
CALCULATE(
[Sales Amount],
ALLEXCEPT(Sales, Product[Category])
)
)
% ยอดขายเทียบกับหมวดหมู่
ตัวอย่างที่ 3: CALCULATE Modifier – ยกเว้นหลายคอลัมน์
Sales By Year Category = CALCULATE( SUM(Sales[Amount]), ALLEXCEPT( Sales, 'Date'[Year], Product[Category] ) )
Sales By Year Category =
CALCULATE(
SUM(Sales[Amount]),
ALLEXCEPT(
Sales,
'Date'[Year],
Product[Category]
)
)
ยอดขายรวมต่อปีและหมวดหมู่
ตัวอย่างที่ 4: Table Function – นับสินค้าในหมวดหมู่
Products In Category = COUNTROWS( ALLEXCEPT( Product, Product[Category] ) )
Products In Category =
COUNTROWS(
ALLEXCEPT(
Product,
Product[Category]
)
)
จำนวนสินค้าในหมวดหมู่เดียวกัน
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
Resources
-
SQLBI: Managing ALL functions in DAX
guide -
DAX Guide: ALLEXCEPT
documentation -
Microsoft Learn: ALLEXCEPT
documentation
Related functions
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