เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
DAX Formula

Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน

distinct values all

เรื่องของ Table Function ใน DAX นอกจาก FILTER ที่แนะนำไปในบทความที่แล้ว ยังมีที่เราควรจะรู้จักอีก 3 ตัว ก็คือ DISTINCT, VALUES, ALL (และเพื่อนๆ ของมัน)

เนื่องจากทั้ง 3 ตัวนี้ทำงานคล้ายกันๆ แต่ไม่เหมือนกัน… ดูเผินๆ แล้วจะสับสนได้
ผมจึงขอทำตารางเปรียบเทียบดังนี้

ฟังก์ชันรองรับ Table/Col ที่เป็นสูตร (Expression)กรณีใช้กับ Tableกรณีใช้กับคอลัมน์เดียวกรณีใช้กับหลายคอลัมน์
DISTINCTได้•ได้ Table แบบค่าที่ไม่ซ้ำกัน•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้นทำไม่ได้
VALUESไม่ได้•ค่า Duplicates ยังอยู่
•ได้ Blank Row พิเศษ
•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้น
•ได้ Blank Row
พิเศษ
ทำไม่ได้
ALLไม่ได้•ค่า Duplicates ยังอยู่
•ได้ Blank Row พิเศษ
•ปลด Filter ออกหมด
•ได้ค่าไม่ซ้ำกันในคอลัมน์นั้น
•ได้ Blank Row พิเศษ
•ปลด Filter ออกหมด
•ได้ค่าไม่ซ้ำกันเมื่อมองผสมหลายคอลัมน์
•ได้ Blank Row พิเศษ •ปลด Filter ออกหมด

หมายเหตุ

  • Blank Row พิเศษ จะโผล่มากรณีที่ Relationship ไปเจอข้อมูลเป็น Key ในฝั่ง Many แต่ดันไม่มีใน Key ฝั่ง One (จะมีอธิบายในบทความด้านล่าง)
  • ALLSELECTED = เหมือน All แต่ปลดแค่ Filter ใน Visual นั้นๆ

ไฟล์ประกอบ

ใช้ไฟล์เดิมจากตอนก่อนหน้าได้เลยนะครับ หรือจะใช้อันนี้ก็ได้

สร้าง New Table ใหม่

เพื่อให้เห็นภาพมากขึ้น เดี๋ยวเราจะสร้างตารางใหม่ที่ชื่อว่า dStoresNew โดยจะเอาให้เหลือเฉพาะ StoreKey ตั้งแต่ 100 ขึ้นไปเท่านั้น (เพื่อตั้งใจสร้างสถานการณ์ให้เจอ Blank Row พิเศษครับ)

ให้เรา NewTable ขึ้นมาดังนี้

dStoresNew = FILTER(dStores,dStores[StoreKey]>100)
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 1

เราจะได้ dStoresNew ที่มี 207 Rows นะครับ

จากนั้นให้ผูก Relationship กับ Fact ที่เป็น fSales ด้วย

Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 2

เมื่อเตรียมข้อมูลพร้อมแล้ว มาดูรายละเอียดทีละตัวกันครับ

DISTINCT

“จะได้ item แบบไม่ซ้ำกัน”

DISTINCT ( <ColumnNameOrTableExpr> )

DISTINCT นั้นจะใช้กับ Table ก็ได้ หรือจะใช้กับ คอลัมน์เดียวก็ได้ จะได้ข้อมูลแบบไม่ซ้ำกัน

Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 3

*จะเห็นว่า DISTINCT ไม่มี Blank Row พิเศษโผล่ออกมาเหมือนกับ VALUES และ ALL ที่จะแสดงให้เห็นถัดไป

และสามารถใส่เป็นสูตรได้ด้วย (รองรับ input แบบ Expression) ตัวอย่างเช่น

DistinctTableExpression = DISTINCT(FILTER(dProduct,dProduct[BrandName]="fabrikam"))
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 4

VALUES

มี Blank row พิเศษ โดยถ้าใช้กับคอลัมน์จะได้ item แบบไม่ซ้ำกัน ถ้าใช้กับตารางจะไม่ได้เอาตัวซ้ำออกให้”

VALUES ( <TableNameOrColumnName> )
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 5

สังเกตว่าจะได้ Blank Row พิเศษติดมาด้วย ซึ่งจะเกิดขึ้นกรณีที่อีกฝั่งของตารางที่มันอ้างอิง เช่น fSales ดันมี StoreKey ที่ไม่มีในตารางนี้ด้วย (ที่เลข StoreKey <100)

เจ้า Blank Row พิเศษนี้ถูกสร้างขึ้นมาเพื่อจับคู่กับ key ตัวที่เหลือในฝั่ง fSales ที่หาคู่ใน dStoresNew ไม่ได้นั่นเอง

ดังนั้นสรุปได้ว่าเวลาใช้งานส่วนใหญ่ จะมองว่าใช้ VALUES กับคอลัมน์จะให้ผลคล้าย DISTINCT แต่ได้ Blank row พิเศษมาด้วย

และ VALUES ไม่รองรับ Table/Column Expression นะครับ ให้ input เป็นสูตรไม่ได้เลย มันจะไม่ยอม

ALL

ALL เป็นตัวที่ผมอยากลงรายละเอียดมากที่สุด เพราะเราจะได้เจอมันค่อนข้างบ่อยเลย

“การทำงานเหมือน VALUES แต่จะปลด Filter ออกทั้งหมดก่อน นอกจากนี้ยังอ้างอิงคอลัมน์อันเดียว หรือ หลายๆ อันก็ได้”

กรณีอ้างอิงทั้งตาราง

“จะปลด Filter ออก ถ้าใช้กับตารางจะไม่ได้เอาตัวซ้ำออก และก็มี Blank row พิเศษ

ALL ( TableName )

เช่น

AllTable1 = ALL ( dStoresNew )
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 6

Tips: สังเกตว่า การสร้าง New Table ถือว่าได้ตารางดิบอันใหม่มาเลย จะใช้ ALL ปลด Filter ที่เป็นตัวสร้าง dStoresNew ไม่ได้นะครับ ดังนั้น StoreKey ก็ยังคงมีเฉพาะตั้งแต่เลข 100 อยู่ดี

กรณีอ้างอิงคอลัมน์ ไม่ว่าจะอันเดียวหรือหลายคอลัมน์

“จะปลด Filter ออก และได้ combination ที่ไม่ซ้ำกัน แต่มี Blank row พิเศษ

ALL ( TableName[ColName1])

ใช้กับคอลัมน์จะได้ Blank Row พิเศษติดมาด้วยเช่นกัน

All_1Col = ALL(dStoresNew[ContinentName])
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 7
ALL ( TableName[ColName1], TableName[ColName2] , TableName[ColName3] )

และถ้าเราใช้ ALL อ้างอิงที่ระดับคอลัมน์ เราจะได้ Combination ของคอลัมน์นั้นๆ แบบไม่ซ้ำกันด้วย แต่ก็จะได้ Blank Row พิเศษติดมาด้วยเช่นกัน

AllMultiColumn = 
ALL(dStoresNew[StoreType],dStoresNew[ContinentName],dStoresNew[RegionCountryName])
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 8

ALLEXCEPT ก็คือเพื่อนของ ALL

สมมติว่าตาราง MiniTable มีคอลัมน์ชื่อ A,B,C,D,E,F แล้วเราอยากจะปลด FILTER และเลือก combination ทุกคอลัมน์ยกเว้น E กับ F เราสามารถเขียนได้ 2 แบบ คือ

=ALL(MiniTable[A],MiniTable[B],MiniTable[C],MiniTable[D])

หรือจะเขียนกลับด้านกันว่าปลดทุกอย่าง ยกเว้น E กับ F ก็ได้ และนั่นคือที่มาของ ALLEXCEPT

=ALLEXCEPT(MiniTable,MiniTable[E],MiniTable[F])

จุดเด่นที่สุดของ ALL คือการปลด Filter

สมมติว่าตารางที่ ALL อ้างอิงอยู่ มีการใส่ Filter เอาไว้ เช่น ใน Report

จะเห็นว่า ผมสร้าง Measure ตัวใหม่ขึ้นมาคือ AllRevenue โดยเขียนสูตรเหมือน TotalRevenue ทุกอย่างเลย ยกเว้นใส่คำว่า ALL(fSales) แทน fSales เฉยๆ

TotalRevenue = SUMX(fSales,fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))
AllRevenue = SUMX(ALL(fSales),fSales[SalesQuantity]* RELATED(dProduct[UnitPrice]))

ส่งผลให้ผลลัพธ์ที่ได้ ไม่ได้ถูก Filter จากตัวใดๆ เลย (ค่าเลยได้เท่ากับ Total แบบรวมทั้งหมด)

Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 9

แม้ว่าเรา Filter ปี 2012 เข้าไปด้วย AllRevenue ก็ยังคงไม่เปลี่ยน แต่ TotalRevenue ถูก Filter ได้

Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 10

หากอยากให้ปลด Filter ออกแค่ใน Visual นั้นๆ ให้ใช้ ALLSELECT แทน ALL ได้ครับ

AllSelectRevenue = SUMX(ALLSELECTED(fSales),fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 11

พอคำนวณค่าเหล่านี้ได้ เราจะสร้าง Ratio คล้ายๆ ในบทความที่แล้วก็ได้ครับ แต่อันนี้จะได้เป็น %เทียบกับยอดรวม แต่จะมีความยืดหยุ่นกว่ากด % of Total ธรรมดาๆ เพราะเราสามารถปลด FILTER ออกแบบไหนก็ได้ตามที่ต้องการ ไม่ใช่แค่ปลดที่ตัวที่อยู่ใน Visual อย่างเดียวเหมือนเครื่องมือสำเร็จรูป

และรูปแบบนึงที่อาจเกิดขึ้นได้ก็คือการปลด Filter ออก แล้ว Filter อีกตัวกลับเข้าไปใหม่

ตัวอย่างการปลด FILTER ออก แล้ว FILTER อีกตัวกลับเข้าไปใหม่

เช่น ถ้าเราอยากได้ยอด Revenue ของ Class Economy การ FILTER class=”economy” ลงไปเฉยๆ

FilterEconomyRevenue = SUMX(FILTER(fSales,RELATED(dProduct[ClassName])="economy"), fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 12

ผลลัพธ์จะเท่ากับเป็นการ Filter เพิ่มลงไปแบบ AND ใน Filter Context เดิม ทำให้กรณีที่ Row Label มี Class เป็นตัวอื่น เช่น Deluxe จะหลายเป็น Deluxe และ Economy ซึ่งเป็นไปไม่ได้ จึงไม่เหลือผลลัพธ์จากการ Filter เลย

ดังนั้นถ้าอยากจะได้ Economy Revenue ทุกช่อง จะต้องมีการใช้ ALL มาช่วยปลด Filter ออกไปซะดังนี้

FilterEconomyRevenue = SUMX(FILTER(ALL(fSales),RELATED(dProduct[ClassName])="economy"), fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))
Power BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน 13

เราถึงจะได้ผลลัพธ์ที่ปลด Filter ตาราง fSales ออกให้หมดก่อน จากนั้นค่อย FILTER ให้มี Class เป็น economy อีกทีนึง

ลักษณะการปลด Filter ออกแล้วใส่ Filter ใหม่ลงไป เรียกได้อีกอย่างว่าเป็นการ “เปลี่ยน Filter Context” ซึ่งเป็นสิ่งที่ต้องทำบ่อยมากในการทำ Report Power BI เจ๋งๆ และมันคือหน้าที่หลักของสุดยอดฟังก์ชันที่ชื่อว่า CALCULATE ซึ่งจริงๆ ฝังความสามารถของ FILTER และ ALL อยู่ข้างในตัวมันเองอีกด้วย!!

ตอนต่อไปเจอกับ CALCULATE

CALCULATE ถือได้ว่าเป็นฟังก์ชันที่ลึกลับซับซ้อนและทรงพลังที่สุดอันนึงใน DAX เลยล่ะ ซึ่งเราจะได้เรียนเจ้า CALCULATE นี้ในบทถัดไปแล้วครับ ^^

สารบัญ Series Power BI

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมีคอร์สออนไลน์ที่สอน Power BI ตั้งแต่พื้นฐาน สามารถไปดูรายละเอียดได้ที่นี่

This image has an empty alt attribute; its file name is Powerful-Data-Power-BI-1024x538.png
แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 128
  •  
  •  
  •  
  •  
  • 128
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

2 Comments

  • รบกวนสอบถามครับ
    การใช้ DISTINCT(FILTER(dProduct,dProduct[BrandName]=”fabrikam”))
    กับ FILTER(dProduct,dProduct[BrandName]=”fabrikam”)
    ต่างกันอย่างไรครับ ลองดูแล้ว เหมือนผลออกมาจะเหมือนกัน (ดูจากแถวที่เท่ากันครับ)

    • distinct จะบังคับให้ผลลัพธ์ไม่ซ้ำกันครับ แต่ถ้าเดิมไม่ซ้ำกันอยู่แล้ว ผลที่ได้ก็เท่ากันครับ

Leave a Reply