Thep Excel
VALUESดึงค่าที่ไม่ซ้ำจากคอลัมน์ (รวม Blank จากข้อผิดพลาดความสัมพันธ์)
VALUES ดึงค่าที่ไม่ซ้ำจากคอลัมน์ หรือดึงแถวทั้งหมดจากตาราง โดยรวมแถว BLANK ที่เกิดจากข้อผิดพลาดของความสัมพันธ์ (Referential Integrity Violations) ส่วน DISTINCT จะไม่รวมแถว BLANK นี้
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| TableNameOrColumnName | Column or Table | ชื่อคอลัมน์หรือตารางที่ต้องการดึงค่าที่ไม่ซ้ำ ต้องเป็นข้อมูลจริงจากโมเดล Data ไม่สามารถใช้ตัวแปร VAR ได้ |
Additional Notes
VALUES เป็นฟังก์ชันหลักของ DAX ที่ใช้ดึงค่าที่ไม่ซ้ำ (distinct values) จากคอลัมน์ หรือดึงแถวทั้งหมดจากตาราง สิ่งที่เจ๋งของ VALUES คือมันสามารถจัดการกับข้อผิดพลาดของความสัมพันธ์ในข้อมูลได้
สำหรับตัวอย่าง หากคุณมีตาราง Sales ที่มีความสัมพันธ์กับตาราง Products แต่มีบางแถวใน Sales ที่อ้างอิงถึง Product ID ที่ไม่มีอยู่จริงใน Products VALUES ก็จะรวมแถว BLANK นี้ไว้ด้วย ในขณะที่ DISTINCT จะละเว้นมันไป
ส่วนตัวผม VALUES มีประโยชน์มากเมื่อคุณทำงานกับข้อมูลที่ไม่สะอาด หรือต้องการนับค่าที่ไม่ซ้ำพร้อมกับการตรวจสอบปัญหาของความสัมพันธ์ข้อมูลด้วย แม้ว่าใน DAX 2019 ขึ้นไป Microsoft แนะนำให้ใช้ DISTINCT เป็นหลักมากขึ้น แต่ VALUES ยังคงเป็นตัวเลือกที่ดีสำหรับกรณีพิเศษ 😎
How it works
ใช้เป็นตัวตั้งต้นในการวนลูป (Iterator)
ใช้ VALUES(Product[Color]) ใน SUMX เพื่อวนคำนวณยอดขายทีละสี
คืนค่าชื่อที่ถูกเลือกใน Slicer
ใช้แสดงข้อความว่าผู้ใช้กำลังเลือกดูข้อมูลของใครอยู่ (เช่น "Sales Report for: John")
Examples
นับจำนวนผลิตภัณฑ์ที่ไม่ซ้ำUnique Products = COUNTROWS(VALUES(Products[ProductID]))
VALUES ดึงรายการผลิตภัณฑ์ที่ไม่ซ้ำทั้งหมด แล้ว COUNTROWS นับจำนวนแถว หากมีข้อผิดพลาดของความสัมพันธ์ VALUES จะรวมแถว BLANK ไว้ด้วย ต่างจาก DISTINCT ที่จะตัด BLANK ออก
นับใบสั่งซื้อที่ไม่ซ้ำในแต่ละหมวดหมู่Unique Orders = CALCULATE( COUNTROWS(VALUES(Orders[OrderNumber])), Products[Category] = "Electronics" )
ใช้ CALCULATE รวมกับ VALUES เพื่อนับใบสั่งซื้อที่ไม่ซ้ำพร้อมกับเคารพ Filter Context ของการเลือกหมวดหมู่ Products
ตรวจสอบข้อผิดพลาดของความสัมพันธ์ข้อมูลIntegrity Check = VAR SalesProducts = COUNTROWS(VALUES(Sales[ProductID])) VAR MasterProducts = COUNTROWS(ALL(Products)) RETURN IF(SalesProducts > MasterProducts…
VALUES รวมแถว BLANK จากข้อมูลที่ไม่มีความสัมพันธ์ได้ สามารถนำมาเปรียบเทียบกับจำนวนสินค้าใน Master เพื่อตรวจสอบข้อผิดพลาด
ใช้ VALUES กับตารางทั้งหมดTotal Store Rows = COUNTROWS(VALUES(Store))
เมื่อส่งตารางทั้งหมดให้ VALUES มันจะดึงแถวทั้งหมด และหากมีแถว BLANK จากข้อผิดพลาดของความสัมพันธ์จะรวมไว้ด้วย
วนลูปคำนวณด้วย SUMXAverage Sales by Category = AVERAGEX( VALUES(Products[Category]), CALCULATE(SUM(Sales[Amount])) )
VALUES สร้างตารางหมวดหมู่ที่ไม่ซ้ำ แล้ว AVERAGEX วนลูปคำนวณยอดขายของแต่ละหมวดหมู่ ก่อนจะหาค่าเฉลี่ย
FAQs
VALUES กับ DISTINCT ต่างกันอย่างไร?+
VALUES รวมแถว BLANK ที่เกิดจากข้อผิดพลาดของความสัมพันธ์ (referential integrity violations) ขณะที่ DISTINCT ไม่รวม DISTINCT ยังสามารถทำงานกับตัวแปร VAR ได้ แต่ VALUES ไม่ได้ ส่วนใหญ่ DISTINCT มักใช้บ่อยกว่า VALUES ใน DAX ปัจจุบัน แต่ VALUES ยังมีประโยชน์สำหรับการตรวจสอบคุณภาพข้อมูล
ทำไม VALUES ต้องใช้เป็นฟังก์ชัน Nested ไม่สามารถใช้คนเดียวได้?+
VALUES ไม่ส่งค่ากลับเป็นสเกลาร์ (scalar value) มันส่งกลับ Table object ดังนั้นต้องใช้ร่วมกับฟังก์ชันอื่นเช่น COUNTROWS SUMX AVERAGEX หรือ FILTER เพื่อให้ได้ผลลัพธ์ที่มีประโยชน์
ใช้ VALUES กับ DirectQuery ได้ไหม?+
ไม่ได้ VALUES ไม่รองรับใน DirectQuery mode สำหรับ Calculated Columns หรือ RLS Rules คุณต้องใช้โหมด Import หรือ Dual
VALUES กับ SELECTEDVALUE ต่างกันอย่างไร?+
SELECTEDVALUE ส่งค่าเดี่ยวกลับ (scalar) เมื่อมีการเลือกเพียงค่าเดียว ในขณะที่ VALUES ส่งตาราง Table ของค่าทั้งหมด Microsoft แนะนำให้ใช้ SELECTEDVALUE แทน VALUES ในกรณีส่วนใหญ่ เพราะมีประสิทธิภาพและความปลอดภัยมากขึ้น
VALUES ทำงานกับ Filter Context อย่างไร?+
VALUES จะคำนึงถึง Filter Context ที่มีอยู่ หากคุณกำลังดูข้อมูลสำหรับหมวดหมู่เดี่ยว VALUES จะดึงค่าที่ไม่ซ้ำเฉพาะในหมวดหมู่นั้น หากต้องการไม่สนใจ Filter Context ให้ใช้ ALLEXCEPT หรือ ALL
ทำไมใช้ VALUES แล้ว Error "A table of multiple values was supplied"?+
เพราะ VALUES คืนค่าเป็นตาราง แต่คุณกำลังพยายามนำไปใช้ในที่ที่ต้องการค่าเดี่ยว (Scalar) วิธีแก้คือใช้ IF(HASONEVALUE(…), VALUES(…)) หรือใช้ SELECTEDVALUE แทน
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ VALUES จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ADDMISSINGITEMS เติมสมาชิกที่หายไปในผลลัพธ์สรุปแบบกลุ่ม เพื่อให้แสดงครบทุกสมาชิกของคอลัมน์ที่ระบุ เหมาะกับรายงานที่ต้องการเห็นรายการที่ไม่มีข้อมูลด้วย
CROSSJOIN สร้างตารางใหม่โดยการรวมแถวทั้งหมดจากตารางที่ระบุ โดยสร้างทุก Combination ที่เป็นไปได้ ผลลัพธ์คือตารางที่มีจำนวนแถวเท่ากับผลคูณของจำนวนแถวของแต่ละตาราง
DETAILROWS คืนตารางแถวรายละเอียดของ measure ภายใต้บริบทปัจจุบัน เหมาะกับการดูรายละเอียด/ตรวจสอบการคำนวณของ measure
DISTINCT คืนค่าตารางที่มีคอลัมน์เดียว ซึ่งประกอบด้วยค่าที่ไม่ซ้ำกันจากคอลัมน์ที่ระบุ โดยไม่รวม Blank Row ที่เกิดจาก Invalid Relationship ความแตกต่างหลักจาก VALUES คือ DISTINCT จะซ่อน Data Quality Issues ในขณะที่ VALUES จะเตือนให้เห็น
EXCEPT คืนตารางของแถวที่อยู่ใน LeftTable แต่ไม่อยู่ใน RightTable เหมาะกับการหาสิ่งที่ขาดหาย เช่น สินค้าที่ไม่เคยขาย ลูกค้าที่ไม่มีธุรกรรม
GENERATE วนทีละแถวใน Table1 แล้วประเมิน Table2 ในบริบทของแถวนั้น (row context) จากนั้นรวมผลทั้งหมดเป็นตารางเดียว ถ้า Table2 ว่างเปล่าในรอบไหน แถวนั้นจาก Table1 จะถูกตัดออก (ต่างจาก GENERATEALL)
INTERSECT คืนตารางของแถวที่อยู่ในทั้ง LeftTable และ RightTable เหมาะกับการหาสิ่งที่ซ้ำกัน/ร่วมกันระหว่างสองชุดข้อมูล โดยรักษาเอาแถวที่ซ้ำกันไว้ด้วย
NATURALJOINUSAGE ทำให้ table expression ถูกเพิ่มเข้าไปในตัวกรอง (filter context) แบบ natural join โดยจับคู่ตามคอลัมน์ชื่อเดียวกัน เป็นฟังก์ชันที่ออกแบบสำหรับการใช้งานขั้นสูงในโมเดลแบบประกอบ (composite models) และ SUMMARIZECOLUMNS
NONVISUAL ใช้ทำเครื่องหมายว่า filter expression นี้เป็น non-visual ซึ่งหมายความว่ามันจะส่งผลต่อการคำนวณ measure แต่จะไม่ไปทำให้แถวที่แสดงในตารางสรุปเปลี่ยนไป มักใช้ร่วมกับ SUMMARIZECOLUMNS เพื่อควบคุมพฤติกรรมของตัวกรองอย่างละเอียด
คืนตารางย่อยจากตารางต้นทางโดยแบ่งแกน X เป็นช่วงเท่า ๆ กัน และคงแถวที่ให้ค่า Measure ต่ำสุด/สูงสุดในแต่ละช่วง (ต่อซีรีส์) เพื่อช่วยลดจำนวนจุดสำหรับการแสดงผล
คืนตารางที่เป็นผลแบบ 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 แถวจากตารางที่กำหนด โดยเรียงตามนิพจน์หนึ่งตัวหรือมากกว่า สามารถระบุทิศทางการเรียงลำดับแยกต่างหากสำหรับแต่ละเกณฑ์
ดึงแถวจากตารางโดยข้ามจำนวนแถวที่กำหนดก่อน แล้วคืนแถวถัดไปตามการเรียงลำดับที่ระบุ มีประสิทธิภาพสูงสำหรับงาน pagination
TREATAS เป็นเสมือนการสร้าง "virtual relationship" โดยไม่ต้องแก้โมเดล ช่วยให้ส่งเงื่อนไขข้ามตารางที่ไม่เชื่อมกัน หรือเมื่อต้องการส่งหลายคีย์พร้อมกัน
UNION รวมหลายตารางให้เป็นตารางเดียวด้วยการต่อแถว โดยเก็บแถวซ้ำไว้ทั้งหมด เหมาะกับการรวมชุดข้อมูลที่มีโครงสร้างคอลัมน์สอดคล้องกัน
CONTAINS ค้นหาแถวในตารางและคืนค่า TRUE/FALSE เมื่อหาเจอค่าที่ตรงกับเงื่อนไขที่กำหนด เจ๋งเพราะมันเร็วกว่า COUNTROWS + FILTER มาก
CONTAINSROW ตรวจสอบว่ามีแถวที่ค่าคอลัมน์ตรงกันหมดหรือไม่ ถ้าเจอจะคืนค่า TRUE ไม่มีจะคืน FALSE
ตรวจว่าคอลัมน์ถูก filter โดยตรงให้เหลือค่าเดียวหรือไม่ เหมาะกับการควบคุมการแสดงผลตามสถานะการเลือกของผู้ใช้
HASONEVALUE ตรวจสอบว่า Column ที่ผ่านการ Filter มีเพียงค่าเดียวเท่านั้น Return TRUE หากตรงตามเงื่อนไข FALSE หากไม่ใช่ ใช้หลักเพื่อสร้าง Measure ที่ "ฉลาด" ตามการเลือก Filter ของผู้ใช้
ALL มีพฤติกรรมแตกต่างกัน 2 แบบ: ใช้เป็น Table Expression จะคืนทุกแถวในตาราง หรือทุกค่าในคอลัมน์ โดยไม่สนใจ Filter ใดๆ เหมาะสำหรับใช้ร่วมกับ FILTER, COUNTROWS, SUMMARIZE หรือใช้เป็น CALCULATE Modifier เพื่อลบ Filter ออกจากตารางหรือคอลัมน์ที่ระบุ ทำให้สามารถคำนวณ Grand Total หรือหาเปอร์เซ็นต์เทียบกับยอดรวมได้
ALLEXCEPT ใช้สำหรับ “ล้างตัวกรองเกือบทั้งหมด” โดยคงตัวกรองไว้เฉพาะคอลัมน์ที่ระบุ เหมาะกับการคำนวณ Sub-total/สัดส่วนภายในกลุ่ม เช่น ยอดขายต่อหมวดหมู่ โดยไม่สนตัวกรองอื่น ๆ แนวคิดหลักคือ ลบตัวกรองของตาราง แล้วคงตัวกรองของคอลัมน์ที่เลือกไว้
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 ได้ดีกว่า
COUNTROWS เป็นฟังก์ชันการรวม (Aggregation) ที่นับจำนวนแถวในตารางที่ระบุ ไม่ว่าจะเป็นตารางจริงในโมเดลหรือตารางเสมือน (Virtual Table) จากฟังก์ชันอื่นๆ เช่น FILTER, VALUES, DISTINCT นับเท่าไหร่? ผลลัพธ์ก็คือจำนวนแถวเต็ม ๆ
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่