การที่เราจะคำนวณเรื่องต่างๆ ใน Power BI ให้ได้ดั่งใจนั้น หลายๆ ครั้งเรามักต้องการคำนวณโดยสนใจเฉพาะเงื่อนไขบางอย่าง ซึ่งเราจะต้องเข้าใจ Concept ของการ Filter ข้อมูลให้ได้อย่างลึกซึ้ง และในบทความนี้ผมจะสอนคุณให้เห็นวิธีการคำนวณที่หลากหลาย มาดูกันเลยว่ามีวิธีอะไรบ้างที่เราควรจะรู้จักเพื่อให้สามารถคำนวณค่าตามเงื่อนไขที่เราต้องการได้ ซึ่งหลายๆ เทคนิคในนี้เอาไปใช้กับ Power Pivot ของ Excel ได้เช่นกันครับ
ไฟล์ตัวอย่าง
ก่อนอื่น ให้โหลด Data ตัวอย่างได้จากไฟล์นี้ครับ
เริ่มสร้าง Measure หลัก
ในนั้นผมมีสร้าง Measure ไว้ตัวนึง เอาไว้ Sum จำนวน Sales Quantity ในตาราง fSales ดังนี้
TotalQty = SUM(fSales[SalesQuantity])มาค่อยๆ ทำความเข้าใจกัน สมมติผมลาก TotalQty เข้า Visual ซักอันนึง มันจะได้ผลลัพธ์เป็น TotalQty รวมทั้งหมดในข้อมูล
ทีนี้เราจะเริ่มทำการ Filter (คัดกรอง) หรือ Slice (หั่น) ข้อมูลให้เหลือเฉพาะสิ่งที่เราต้องการ ซึ่งทำได้หลายวิธีมากๆ เลย
Filter ผ่านการแบ่ง Row/Column/Category ใน Visual นั้นๆ
ถ้าผมลาก Field ClassName จาก dProduct ลงมาใน Rows มันก็จะแบ่ง TotalQty ตาม ClassName นั้นๆ เช่น Filter ClassName เป็น Deluxe จะทำให้ TotalQty เหลือแค่ 329,043 ชิ้น ซึ่งนี่คือการ Filter ข้อมูลแบบนึง ซึ่งเป็นการ Filter ที่ทำผ่านการ Slice ข้อมูลตามมิติต่างๆ ผ่าน Relationship ที่มี
การ Filter นี้ทำงานได้แม้ตัว ClassName จะอยู่คนละตารางกัน เป็นเพราะมี Relationship ที่เชื่อมผ่าน ProductKey โดยมีทิศทางการ ไหลของ Filter ที่ยอมให้ Filter จาก dProduct มายัง fSales ได้นั่นเอง (ดูจากหัวลูกศร)
Filter ผ่าน Interaction
นอกจากที่เราจะ Filter ข้อมูลผ่านการลากลงไปใน Row/Column/Category ในVisual แล้ว เราก็ยังสามารถ Filter ข้อมูลโดยคลิ๊กที่ Visual ตัวอื่น ซึ่งมันจะทำการ Filter ผ่าน Interaction ระหว่าง Visual ทั้งสองตัวได้ด้วย เช่น ผมคลิ๊กที่ Asia ที่กราฟโดนัท ส่งผลให้ตารางถูก Filter ไปด้วย
Filter ผ่าน Filter Pane
เราสามารถใช้ Filter Pane เพื่อ Filter ข้อมูลเฉพาะ Visual ที่เลือก / Visual เฉพาะ Page นั้นๆ / Visual ทุก Page ได้
เช่น ถ้าผมเอา BrandName ลากเข้า Filter on This Page แล้วเลือก Contoso ก็จะได้ดังนี้
จะเห็นว่าตอนนี้ ตัวตารางถูก Filter จากทั้ง Interaction ระหว่างกราฟ และ BrandName จาก Filter Pane เลย
Filter ผ่านฟังก์ชัน CALCULATE แบบเปลี่ยนเงื่อนไข
วิธี Filter ที่ผ่านมาทั้งหมด สามารถ Filter ได้ย่อยสุดแค่ระดับ Visual เท่านั้น ไม่สามารถ Filter เฉพาะระดับ Measure ที่ต้องการได้ ซึ่งหากเราอยากทำแบบนั้น จะต้องเขียนสูตรเท่านั้น ซึ่งหนึ่งในฟังก์ชันที่ทำได้ค่อนข้างสะดวกก็คือ CALCULATE นั่นเอง
เช่น ผมอยากจะสร้าง Measure ที่คำนวณ TotalQty ของ ProductCategory Audio เท่านั้น ผมสามารถเขียน Measure ได้ดังนี้ (ณ ตอนนี้ผมเอา Filter จากที่อื่นออกหมดแล้ว)
TotalQty Audio = CALCULATE([TotalQty],dProduct[ProductCategory]="Audio")
ผลลัพธ์คือ Power BI ทำการ Filter ProductCategory เป็น Audio เฉพาะ Measure นั้นๆ ทำใผ้ผลลัพธ์เหลือน้อยลง
อย่างไรก็ตาม การที่เขียนเงื่อนไข Filter ใน CALCULATE เป็นเงื่อนไขเชิง Logic เช่น dProduct[ProductCategory]=”Audio” จริงๆ แล้วเป็นการใส่เงื่อนไขเช่นเดียวกับสูตรนี้เป๊ะๆ
FILTER(ALL(dProduct[ProductCategory]),dProduct[ProductCategory]="Audio") // ให้ผลลัพธ์เป็นตารางที่มีคอลัมน์เดียวคือ ProductCategory และมีแถวเดียวคือ Audio // สามารถมองได้ว่าจริงๆ แล้วเงื่อนไขในการ Filter ของ CALCULATE นั้นคือ Table แบบนึง
ดังนั้นหากเราเปลี่ยน Row เป็น ProductCategory จะเห็นผลลัพธ์แบบนี้ ซึ่งได้เลขเดียวกันหมดเท่ากับ Audio เลย (ตรง Category อื่นไม่ใช่ช่องว่าง เพราะไม่ได้เป็นการ Filter ซ้ำ แต่เป็นการเปลี่ยนเงื่อนไขการ Filter เป็นอันใหม่ต่างหาก)
การใช้ CALCULATE แล้วใส่สูตรคำนวณในเงื่อนไขการเปรียบเทียบ
หากจะเขียนเงื่อนไขใน CALCULATE โดยอ้างอิงเปรียบเทียบค่ากับสิ่งที่ไม่ใช่ค่าคงที่ (เช่น สูตรหรือ Measure) เราจะเขียนสูตรตรงๆ ใน CALCULATE ไม่ได้ แต่จะต้องใช้การประกาศค่าใน VAR ให้หลายเป็นค่าคงที่ไปก่อน แล้วค่อยอ้างอิงค่าจาก VAR มาใช้ต่อใน CALCULATE อีกที เช่น
ถ้าจะคำนวณยอด TotalQty แต่เอาเฉพาะจากร้านค้าที่มีขนาดมากกว่าค่าเฉลี่ย
เราจะใส่สูตรแบบนี้ไม่ได้ เพราะมีการเปรียบเทียบกับค่าที่เป็นสูตร AVERAGE(dStores[SellingAreaSize])
AboveAverageAreaSize = CALCULATE([TotalQty],dStores[SellingAreaSize]>AVERAGE(dStores[SellingAreaSize]))
เราต้องเลี่ยงไปใช้ VAR แทน แบบนี้ จะไม่มีปัญหา เพราะเปลี่ยนการคำนวณ AVERAGE(dStores[SellingAreaSize]) ไว้ในค่าคงที่ชื่อ AvgArea แล้ว
AboveAverageAreaSize = VAR AvgArea=AVERAGE(dStores[SellingAreaSize]) RETURN CALCULATE([TotalQty],dStores[SellingAreaSize]>AvgArea)
การปลด Filter ใน CALCULATE
เราสามารปลดเงื่อนไขการ Filter ได้ด้วยการใช้ ALL หรือ ALLSELECTED ลงไปในเงื่อนไขของ CALCULATE ซึ่ง ALL จะปลดเงื่อนไขการ Filter ทั้งหมดไม่ว่าจะมาจาก Visual ไหนก็ตาม แต่ ALLSELECTED จะปลดแค่จาก Visual ตัวเองเท่านั้น
AllQty = CALCULATE([TotalQty],ALL(dProduct[ProductSubcategory]))AllselectedQty = CALCULATE([TotalQty],ALLSELECTED(dProduct[ProductSubcategory]))
อย่างไรก็ตามเราต้องปลดเงื่อนไขให้ตรง Field ที่มีการ Filter ด้วย เช่น ใน Visual มีการ Filter ด้วย ProductSub ก็ต้องปลดด้วย ProductSub ให้ตรงกัน ดังนั้นถ้าเปลี่ยน Row ใน Visual เป็นอันอื่น เช่น ProductCat สูตรมันจะปลด Filter ไม่ออกเลย
หมายเหตุ : หาก Field ที่เราตั้งใจจะปลด Filter นั้นมีการ Sort by Column อื่นอีก เราก็ต้องปลด Column ที่ไปอ้างอิงการ Sort ด้วยนะ
Calculate กับเงื่อนไข Filter บน Field Date
จากที่ผมได้บอกไปก่อนหน้าว่าเวลาจะปลด Filter นั้นจะต้องปลดให้ตรงกับ Field ที่มีการ Filter อยู่ แต่มันมีกรณียกเว้นคือ หากเราไปอ้างอิงคอลัมน์ Date ของตารางวันที่ใน Data Model มันจะเป็นการปลด Filter ออกทั้งตารางวันที่เลย เช่น
สมมติผมจะสร้าง Measure เพื่อคำนวณ Qty สะสมตั้งแต่มี Data มา ผมจะสามารถเปลี่ยนเงื่อนไข Filter ให้เลือกวันที่ <= วันสุดท้ายของ Filter Context นั้นๆ ได้เลย แม้ Field ที่ Visual จะเป็น YearMonth ก็ตาม
AccumQty = VAR LastVisibleDay=MAX(dDate[Date]) RETURN CALCULATE([TotalQty],dDate[Date]<=LastVisibleDay)
อย่างไรก็ตามเวลาจะปลด Filter เฉยๆ ด้วย ALL จะใช้ปลดที่ Date เพื่อให้ปลดทั้งตารางไม่ได้ (มันจะเอาไม่ออก) ต้องใช้ FILTER มาช่วย ในการสร้างตารางให้เห็นวันที่ให้ครบดังนี้
AllDateQty = CALCULATE([TotalQty],FILTER(ALL(dDate[Date]),TRUE()))
Filter ผ่านฟังก์ชัน CALCULATE แบบ Filter เพิ่ม ไม่ปลดเงื่อนไขเดิม
ถ้าอยากจะคงเงื่อนไข Filter เดิมไว้ โดยไม่ปลด Filter ออกก่อน ก็สามารถทำได้ด้วยการใส่ KEEPFILTER เข้าไปในเงื่อนไขของ CALCULATE ดังนี้
TotalQty Audio Keep = CALCULATE([TotalQty],KEEPFILTERS(dProduct[ProductCategory]="Audio"))
Filter แบบเปลี่ยนทิศทาง Relationship เป็น 2 ทิศทาง
หากเราสร้าง Measure ขึ้นมาใหม่เพื่อคำนวณ UnitPrice สูงสุด แล้วเอาใส่ลงไปใน Visual ที่เอา Field มาจากตารางอื่น ผลจะออกมาเป็นเลขเดียวกันทั้งหมดดังนี้
MaxUnitPrice = MAX(dProduct[UnitPrice])
สาเหตุเป็นเพราะ RegionCountryName ไม่สามารถไหลไป Filter คอลัมน์ UnitPrice ในตาราง dProduct ได้
วิธีที่ช่วยได้คือ การแก้ทิศทางเส้น Relationship ให้เป็นแบบ Bi-Directional (2 ทิศทาง) ซึ่งผมแนะนำว่าไม่ควรไปแก้ที่ Data Model โดยตรงเพราะมีความเสี่ยงที่ทำให้เกิดความกำกวมของ Model ว่าจะ Filter ตารางจากทิศทางไหนดี
วิธีที่ผมขอแนะนำคือ ให้เปลี่ยนทิศทาง FILTER ด้วย CALCULATE + CROSSFILTER โดยเลือก Direction เป็น Both ดังนี้
MaxUnitPrice = CALCULATE(MAX(dProduct[UnitPrice]), CROSSFILTER(dProduct[ProductKey],fSales[ProductKey],Both))
Filter แบบเปลี่ยนเส้น Relationship
ณ ตอนนี้ Data Model ของเรามีการเชื่อมความสัมพันธ์ระหว่างตารางวันที่ กับตารางหลักอยู่ 2 เส้น โดย
- Active 1 เส้น คือ เชื่อม Date กับ OrderDate
- ไม่ Active 1 เส้น คือ เชื่อม Date กับ DeliveryDate
แปลว่าเวลาเราลาก Field จากตาราง Date ลงมาใน Visual มันจะเป็นการ Filter ด้วย OrderDate (ตัวที่ Active) ดังนี้
เราสามารถทำให้การ Filter เปลี่ยนไปใช้ DeliveryDate ได้โดยการไปทำให้เส้น OrderDate ไม่ Active ก่อน แล้วสั่งให้ DeliveryDate Active ขึ้นมา แต่นั่นก็ไม่ใช่วิธีที่ผมแนะนำ
วิธีที่ผมอยากแนะนำคือให้เปลี่ยนเส้น Relationship โดยใช้ CALCULATE+USERELATIONSHIP ดังนี้
TotalQty DeliveryDate = CALCULATE([TotalQty],USERELATIONSHIP(dDate[Date],fSales[DeliveryDate]))
Filter ด้วย Context Transition
ตามปกติแล้ว หากเราสร้างคอลัมน์ใหม่ในตาราง dimension เช่น dProduct แล้วเขียนสูตรใน New Column เพื่อคำนวณค่าจากตาราง fact เช่น SUM(fSales[SalesQuantity]) มันจะออกมาได้ค่าเท่ากันหมด เพราะการเขียนสูตรในตารางนั้นมีแต่ Row Context ไม่มี Filter Context ซึ่งผลจะได้ดังนี้
แต่ถ้าเราใส่ CALCULATE ครอบลงไป หรือใช้การอ้างอิงด้วย Measure แทน (หากเขียนสูตรอ้างอิง Measure จะเสมือนว่ามี CALCULATE มาครอบโดยอัตโนมัติ) มันจะเกิด สิ่งที่เรียกว่า Context Transition ขึ้น คือจะเปลี่ยนเงื่อนไขในแต่ละแถว ให้กลายเป็น Filter จริงๆ ดังนี้
ProductQty = CALCULATE(SUM(fSales[SalesQuantity]))
ซึ่งให้ผลเหมือนกับแบบนี้เป๊ะ
ProductQty = [TotalQty]
Filter แบบเขียนสูตรเองไม่ง้อ Relationship
แบบนี้จะซับซ้อนสุด เพราะเราจะเขียนสูตรเพื่อ Filter เอง
สมมติผมอยากจะสร้าง Slicer ให้เลือกเดือนปีที่สิ้นสุด แล้วจะแสดงกราฟจำนวน Qty ย้อนหลัง 3 เดือนนับจากวันที่สิ้นสุดที่ผมเลือกไป ผมสามารถเอา YearMonth มาเป็น Slicer ตามนี้
จะเห็นว่าพอเลือกเดือนที่คิดว่าจะให้เป็นเดือนสิ้นสุดแล้ว มันดัน Filter Visual เราให้ Row เหลือแค่เดือนนั้นๆ ไปด้วย ซึ่งจะทำให้เราไม่สามารถแสดงข้อมูลของเดือนอื่นได้ ดังนั้นเราจะเปลี่ยนวิธีคือ สร้าง New Table ใหม่ให้มีแต่ค่า YearMonth แต่ไม่ต้องผูก Relationship ดังนี้
New Table
YM = ALL(dDate[YearMonth])Add Column 2 อัน คือ
FirstDate = DATE(LEFT(YM[YearMonth],4),RIGHT(YM[YearMonth],2),1)
LastDate = EOMONTH(YM[FirstDate],0)จะได้ผลลัพธ์ตารางใหม่แบบนี้ ซึ่งเราจะไม่เอาไปผูก Relationship ใดๆ ทั้งสิ้น
จากนั้นเราเปลี่ยนไปใช้ Slicer YearMonth จากตารางใหม่ จะพบว่าคราวนี้มันไม่มา Filter ตารางแล้ว (เพราะไม่มี Relationship กับ dDate)
คราวนี้เราจะสร้าง Measure ใหม่ เพื่อที่จะให้แสดงข้อมูลย้อนหลังแค่ 3 เดือนจากวันที่เลือก ดังนั้นเราจะเขียนสูตรแบบนี้
TotalQtyPrev3M = VAR LastDateSelected=SELECTEDVALUE(YM[LastDate]) //เอาค่าวันที่สุดท้ายจาก Slicer ที่เลือก VAR FirstVisibleDate=EDATE(LastDateSelected,-3)+1 // ย้อนกลับไป 3 เดือน เพื่อหาจุดเริ่มต้นที่ต้องการ VAR CurrentVisible=MAX(dDate[Date]) // เอาค่าจาก Filter Context ปัจจุบันใน visual RETURN IF(AND(CurrentVisible<=LastDateSelected,CurrentVisible>=FirstVisibleDate),[TotalQty],BLANK()) //คัดเลือกช่วงที่ต้องการ
ปกติแล้ว Visual ใน Power BI จะไม่แสดงค่าใน Row mี่มีค่าเป็น Blank ดังนั้นถ้าเราใส่แค่ Measure TotalQtyPrev3M ก็จะได้ผลลัพธ์เหลือแค่ 3 เดือนจริงๆ ดังนี้เลย
Filter โดยอาศัย TREATAS เพื่อเลียนแบบ Date Lineage
วิธีนี้เป็นวิธีที่ค่อนข้าง Advance นะครับ สมมติว่าผมอยากจะเขียน Measure เพื่อคำนวณยอดจำนวนสินค้าวันสุดท้ายของแต่ละร้านค้า หากเรารู้จัก TREATAS เราจะสามารถใช้มันสร้างตารางจำลองขึ้นมา Filter ข้อมูลตามเงื่อนไขที่ต้องการได้ แม้เราจะเขียนสูตรเพื่อสร้างคอลัมน์ใหม่ในตารางจำลองขึ้นมาก็ตาม
ปกติถ้าเขียนสูตรสร้างคอลัมน์ขึ้นมาเฉยๆ (ที่ไม่ใช่การใช้ ALL, DISTINCT, VALUES) มันจะไม่มีเรื่องของ Relationship กับตารางอื่น ทำให้ Filter ไม่ได้ แต่ถ้าเราใช้ TREATAS เราจะเลือกได้ว่าจะให้ตารางที่เราสร้างขึ้นมา ทำตัวเหมือนกับคอลัมน์ไหนใน Data Model (ซึ่งมี Relationship)
TotalQtyLastDayofStore = //สร้างตารางจำลองที่มีรายชื่อร้านค้า และวันสุดท้ายที่มีการขายของ VAR MyTable=ADDCOLUMNS(DISTINCT(dStores[StoreKey]), "LastDateSales",CALCULATE(MAX(fSales[OrderDate]))) // ใช้ TREATAS สั่งให้ MyTable (2คอลัมน์) มี Relationship เลียนแบบ dStores[StoreKey],dDate[Date] VAR LineageTable=TREATAS(MyTable,dStores[StoreKey],dDate[Date]) RETURN CALCULATE([TotalQty],LineageTable)
