Power BI ตอนที่ 12: 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)

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

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

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

DISTINCT

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

DISTINCT ( <ColumnNameOrTableExpr> )

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

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

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

DistinctTableExpression = DISTINCT(FILTER(dProduct,dProduct[BrandName]="fabrikam"))

VALUES

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

VALUES ( <TableNameOrColumnName> )

สังเกตว่าจะได้ 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 )

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

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

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

ALL ( TableName[ColName1])

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

All_1Col = ALL(dStoresNew[ContinentName])

ALL ( TableName[ColName1], TableName[ColName2] , TableName[ColName3] )

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

AllMultiColumn = 
ALL(dStoresNew[StoreType],dStoresNew[ContinentName],dStoresNew[RegionCountryName])

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 แบบรวมทั้งหมด)

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

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

AllSelectRevenue = SUMX(ALLSELECTED(fSales),fSales[SalesQuantity]*RELATED(dProduct[UnitPrice]))

พอคำนวณค่าเหล่านี้ได้ เราจะสร้าง 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]))

ผลลัพธ์จะเท่ากับเป็นการ 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]))

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

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

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

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

สารบัญ Series Power BI

• POWER BI ตอนที่ 01: POWER BI คืออะไร?
• POWER BI ตอนที่ 02: พื้นฐาน EXCEL ที่สำคัญก่อนจะเรียนรู้ POWER BI
• POWER BI ตอนที่ 03: ภาพรวมการใช้งาน POWER BI DESKTOP
• POWER BI ตอนที่ 04: สร้าง REPORT แรก ใน POWER BI
• POWER BI ตอนที่ 05: วิธีการ DRILL เพื่อเจาะลึกข้อมูลใน REPORT
• POWER BI ตอนที่ 06: การปรับแต่งสีใน VISUAL ด้วย CONDITIONAL FORMAT
• POWER BI ตอนที่ 07: เริ่ม GET DATA ตั้งแต่ไฟล์ยังว่างเปล่า
• POWER BI ตอนที่ 08: สร้าง DATA MODEL ที่เหมาะสม
• POWER BI ตอนที่ 09: สร้าง DATE TABLE ด้วย DAX
• POWER BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น
• POWER BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER
• POWER BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน
• POWER BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX
• Power BI ตอนที่ 14: Context Transition และ พลังแฝงใน Measure
• Power BI ตอนที่ 15: วิธีดึงค่าจาก Slicer มาคำนวณใน Report
• Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function
• Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual
• Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX
• Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension
• ส่วนเสริม
• การคำนวณต้นทุนแบบ FIFO ด้วย DAX
• แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)
• การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน
• เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX

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