Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension

บทความนี้จะสอนเรื่องการปรับค่า Cross Filter เพื่อให้สามารถคำนวณข้อมูลที่อยู่ในตาราง Dimension จากตารางอื่นได้ แต่ก่อนจะไปดูเรื่องนั้น มาทวนเรื่องการคำนวณกรณีที่ Measure หลักอยู่ใน Fact Table กันซะก่อนครับ

เมื่อ Measure หลัก คำนวณจากข้อมูลใน Fact Table

ในตอนที่ 14 เรื่องของ Context Transition ผมมีเขียนสูตรอธิบายการใช้ CALCULATE ผสมกับพวก SUMX AVERAGEX MAXX (เรียกว่า Iterator) ซึ่งจะสามารถเพิ่มการคำนวณมุมมองใหม่ๆ ให้กับ Measure หลัก ซึ่งคำนวณจากข้อมูลในตาราง Fact ผ่านมุมมองต่างๆ ของตาราง Dimension เช่น

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

จะเห็นว่า TotalRevenue คำนวณจากตาราง fSales ดังนั้นจึงสามารถ Filter จากตารางอื่นได้เมื่อเราใช้ พวก SUMX AVERAGEX MAXX สร้างตารางจำลองขึ้นมา แล้วเกิด Context Transition ขึ้น

TotalRevenue เฉลี่ยรายร้านค้า

AverageStoreRevenue = 
AVERAGEX(DISTINCT(dStores[StoreKey]),[TotalRevenue])

TotalRevenue เฉลี่ยราย Category สินค้า

AvgProductCategoryRevenue = 
AVERAGEX( DISTINCT(dProduct[ProductCategory]) , [TotalRevenue] )

TotalProfit ที่มากสุดรายสินค้า

MaxProductCategoryProfit = 
MAXX( DISTINCT(dProduct[ProductCategory]) ,[TotalGrossProfit] )

ซึ่งจะเห็นว่าเราสามารถสร้างมุมมองต่างๆ เพิ่มได้ไม่จำกัด หาก Measure หลักนั้นคำนวณจากข้อมูลซึ่งอยู่ที่ Fact Table แต่ถ้า Measure หลักไม่ได้คำนวณจากคอลัมน์ใน Fact Table ล่ะ? ในบทความนี้เราจะมาดูวิธีจัดการกันครับ

เมื่อ Measure หลัก คำนวณจากข้อมูลใน Dimension Table

ใน Data Model ด้านบน สมมติผมต้องการคำนวณ Max UnitPrice ก็เลยสร้าง Measure ขึ้นมาว่า

MaxUnitPrice = MAX(dProduct[UnitPrice])

ซึ่งจะเห็นว่ามันคำนวณจากข้อมูลในตาราง dProduct เต็มๆ เลย

ดังนั้นมันจึงไม่สามารถถูก Filter ได้ด้วยตารางอื่นเลยนอกจาก dProduct ตัวเดียวเท่านั้น (ทั้งนี้เพราะว่าทิศทางของ Relationship มันย้อนศรไม่ได้)

เวลาเราเอา Measure นี้ไปใช้ใน Visual mี่มีการ Filter จากตารางอื่น (เช่น dDate) ผลลัพธ์ก็จะผิดทันที ซึ่งจะเห็นชัดว่า ค่าทุกตัวเท่ากันหมดเพราะว่าไม่ได้มีการ Filter เกิดขึ้นเลย นั่นเอง

แนวทางแก้ไขมีอยู่ 2 วิธี คือ

1. ปรับทิศทาง Cross Filter ในเส้น Relationship ของ Data Model เป็น Both Direction เสมอ (ไม่แนะนำ)
2. ใช้ฟังก์ชัน CROSSFILTER มาช่วยใน CALCULATE เพื่อปรับเป็น Both เฉพาะการคำนวณ Measure นั้นๆ (แนะนำ)

ปรับทิศทาง Cross Filter ในเส้น Relationship ของ Data Model เป็น Both Direction

วิธีนี้ไม่ต้องเขียนสูตรอะไรทั้งสิ้น เพราะเป็นการไปปรับที่เส้น Relationship ใน Data Model เองเลย โดยให้ดับเบิ้ลคลิ๊กที่เส้นตัวเจ้าปัญหา

จากนั้นเลือก Cross Filter Direction เป็น Both เพื่อให้มันสามารถ Filter ได้ทั้ง 2 ทิศทาง

พอปรับเป็น both แล้ว ลูกศรก็จะหันไปทั้ง 2 ทิศทาง แปลว่ามันยอมให้เรา Filter จากตารางอื่นได้แล้ว

หากไปดูผลลัพธ์ใน Visual ก็จะถูกต้องแล้ว

อย่างไรก็ตามผมไม่แนะนำวิธีนี้ เนื่องจาก Data Model อาจเกิดความกำกวมได้ ว่าจะให้ Filter จากทิศไหนไปไหนกันแน่ ดังนั้นเรามาดูอีกวิธีกัน

ใช้ฟังก์ชัน CROSSFILTER มาช่วยใน CALCULATE เพื่อปรับเป็น Both เฉพาะการคำนวณ Measure นั้นๆ

วิธีนี้ ใน Data Model เราจะยังคงเป็นทิศทางเดียว คือ Single อยู่ แต่คราวนี้เราจะเขียน Measure ตัวใหม่ ให้รองรับการ Filter แบบ 2 ทิศทางได้ โดยใช้ CALCULATE+CROSSFILTER

CROSSFILTER ( <LeftColumnName>, <RightColumnName>, <CrossFilterType> )

โดยที่ CROSSFILTER มีความสามารถในการเปลี่ยนรูปแบบการ Cross Filter หรือ <CrossFilterType> จาก Relationship ที่มีอยู่แล้วให้เป็น Both, None, OneWay ได้ ซึ่งจะต้องใช้ฟังก์ชันนี้คู่กับ CALCULATE ในฐานะของ Filter เช่น

MaxUnitPriceV2 = 
CALCULATE(MAX(dProduct[UnitPrice]),
        CROSSFILTER(dProduct[ProductKey],fSales[ProductKey],Both)
        )

ซึ่งแม้ว่าใน Data Model เส้น Relationship จะยังเป็น Single อยู่ แต่ใน Measure MaxUnitPriceV2 นี้จะสามารถใช้ Relationship แบบ Both ได้ แบบนี้จะไม่ทำให้ตัว Model เสีย และยังสามารถคำนวณได้ถูกต้องอีกด้วย แบบนี้สิ แนะนำเลย!

สารบัญ 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

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี