Select Page

การที่เราจะคำนวณเรื่องต่างๆ ใน Power BI ให้ได้ดั่งใจนั้น หลายๆ ครั้งเรามักต้องการคำนวณโดยสนใจเฉพาะเงื่อนไขบางอย่าง ซึ่งเราจะต้องเข้าใจ Concept ของการ Filter ข้อมูลให้ได้อย่างลึกซึ้ง และในบทความนี้ผมจะสอนคุณให้เห็นวิธีการคำนวณที่หลากหลาย มาดูกันเลยว่ามีวิธีอะไรบ้างที่เราควรจะรู้จักเพื่อให้สามารถคำนวณค่าตามเงื่อนไขที่เราต้องการได้ ซึ่งหลายๆ เทคนิคในนี้เอาไปใช้กับ Power Pivot ของ Excel ได้เช่นกันครับ

ไฟล์ตัวอย่าง

ก่อนอื่น ให้โหลด Data ตัวอย่างได้จากไฟล์นี้ครับ

เริ่มสร้าง Measure หลัก

ในนั้นผมมีสร้าง Measure ไว้ตัวนึง เอาไว้ Sum จำนวน Sales Quantity ในตาราง fSales ดังนี้

TotalQty = SUM(fSales[SalesQuantity])

มาค่อยๆ ทำความเข้าใจกัน สมมติผมลาก TotalQty เข้า Visual ซักอันนึง มันจะได้ผลลัพธ์เป็น TotalQty รวมทั้งหมดในข้อมูล

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 1

ทีนี้เราจะเริ่มทำการ Filter (คัดกรอง) หรือ Slice (หั่น) ข้อมูลให้เหลือเฉพาะสิ่งที่เราต้องการ ซึ่งทำได้หลายวิธีมากๆ เลย

Filter ผ่านการแบ่ง Row/Column/Category ใน Visual นั้นๆ

ถ้าผมลาก Field ClassName จาก dProduct ลงมาใน Rows มันก็จะแบ่ง TotalQty ตาม ClassName นั้นๆ เช่น Filter ClassName เป็น Deluxe จะทำให้ TotalQty เหลือแค่ 329,043 ชิ้น ซึ่งนี่คือการ Filter ข้อมูลแบบนึง ซึ่งเป็นการ Filter ที่ทำผ่านการ Slice ข้อมูลตามมิติต่างๆ ผ่าน Relationship ที่มี

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 2

การ Filter นี้ทำงานได้แม้ตัว ClassName จะอยู่คนละตารางกัน เป็นเพราะมี Relationship ที่เชื่อมผ่าน ProductKey โดยมีทิศทางการ ไหลของ Filter ที่ยอมให้ Filter จาก dProduct มายัง fSales ได้นั่นเอง (ดูจากหัวลูกศร)

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 3

Filter ผ่าน Interaction

นอกจากที่เราจะ Filter ข้อมูลผ่านการลากลงไปใน Row/Column/Category ในVisual แล้ว เราก็ยังสามารถ Filter ข้อมูลโดยคลิ๊กที่ Visual ตัวอื่น ซึ่งมันจะทำการ Filter ผ่าน Interaction ระหว่าง Visual ทั้งสองตัวได้ด้วย เช่น ผมคลิ๊กที่ Asia ที่กราฟโดนัท ส่งผลให้ตารางถูก Filter ไปด้วย

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 4

Filter ผ่าน Filter Pane

เราสามารถใช้ Filter Pane เพื่อ Filter ข้อมูลเฉพาะ Visual ที่เลือก / Visual เฉพาะ Page นั้นๆ / Visual ทุก Page ได้

เช่น ถ้าผมเอา BrandName ลากเข้า Filter on This Page แล้วเลือก Contoso ก็จะได้ดังนี้

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 5

จะเห็นว่าตอนนี้ ตัวตารางถูก 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")
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 6

ผลลัพธ์คือ 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 เป็นอันใหม่ต่างหาก)

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 7

การใช้ 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 ข้อมูลใน Power BI 8

การปลด Filter ใน CALCULATE

เราสามารปลดเงื่อนไขการ Filter ได้ด้วยการใช้ ALL หรือ ALLSELECTED ลงไปในเงื่อนไขของ CALCULATE ซึ่ง ALL จะปลดเงื่อนไขการ Filter ทั้งหมดไม่ว่าจะมาจาก Visual ไหนก็ตาม แต่ ALLSELECTED จะปลดแค่จาก Visual ตัวเองเท่านั้น

AllQty = CALCULATE([TotalQty],ALL(dProduct[ProductSubcategory]))
AllselectedQty = CALCULATE([TotalQty],ALLSELECTED(dProduct[ProductSubcategory]))
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 9

อย่างไรก็ตามเราต้องปลดเงื่อนไขให้ตรง Field ที่มีการ Filter ด้วย เช่น ใน Visual มีการ Filter ด้วย ProductSub ก็ต้องปลดด้วย ProductSub ให้ตรงกัน ดังนั้นถ้าเปลี่ยน Row ใน Visual เป็นอันอื่น เช่น ProductCat สูตรมันจะปลด Filter ไม่ออกเลย

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 10

หมายเหตุ : หาก 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 ข้อมูลใน Power BI 11

อย่างไรก็ตามเวลาจะปลด Filter เฉยๆ ด้วย ALL จะใช้ปลดที่ Date เพื่อให้ปลดทั้งตารางไม่ได้ (มันจะเอาไม่ออก) ต้องใช้ FILTER มาช่วย ในการสร้างตารางให้เห็นวันที่ให้ครบดังนี้

AllDateQty = CALCULATE([TotalQty],FILTER(ALL(dDate[Date]),TRUE()))
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 12

Filter ผ่านฟังก์ชัน CALCULATE แบบ Filter เพิ่ม ไม่ปลดเงื่อนไขเดิม

ถ้าอยากจะคงเงื่อนไข Filter เดิมไว้ โดยไม่ปลด Filter ออกก่อน ก็สามารถทำได้ด้วยการใส่ KEEPFILTER เข้าไปในเงื่อนไขของ CALCULATE ดังนี้

TotalQty Audio Keep = CALCULATE([TotalQty],KEEPFILTERS(dProduct[ProductCategory]="Audio"))
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 13

Filter แบบเปลี่ยนทิศทาง Relationship เป็น 2 ทิศทาง

หากเราสร้าง Measure ขึ้นมาใหม่เพื่อคำนวณ UnitPrice สูงสุด แล้วเอาใส่ลงไปใน Visual ที่เอา Field มาจากตารางอื่น ผลจะออกมาเป็นเลขเดียวกันทั้งหมดดังนี้

MaxUnitPrice = MAX(dProduct[UnitPrice])
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 14

สาเหตุเป็นเพราะ 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 ข้อมูลใน Power BI 15

Filter แบบเปลี่ยนเส้น Relationship

ณ ตอนนี้ Data Model ของเรามีการเชื่อมความสัมพันธ์ระหว่างตารางวันที่ กับตารางหลักอยู่ 2 เส้น โดย

  • Active 1 เส้น คือ เชื่อม Date กับ OrderDate
  • ไม่ Active 1 เส้น คือ เชื่อม Date กับ DeliveryDate
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 16

แปลว่าเวลาเราลาก Field จากตาราง Date ลงมาใน Visual มันจะเป็นการ Filter ด้วย OrderDate (ตัวที่ Active) ดังนี้

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 17

เราสามารถทำให้การ Filter เปลี่ยนไปใช้ DeliveryDate ได้โดยการไปทำให้เส้น OrderDate ไม่ Active ก่อน แล้วสั่งให้ DeliveryDate Active ขึ้นมา แต่นั่นก็ไม่ใช่วิธีที่ผมแนะนำ

วิธีที่ผมอยากแนะนำคือให้เปลี่ยนเส้น Relationship โดยใช้ CALCULATE+USERELATIONSHIP ดังนี้

TotalQty DeliveryDate = CALCULATE([TotalQty],USERELATIONSHIP(dDate[Date],fSales[DeliveryDate]))
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 18

Filter ด้วย Context Transition

ตามปกติแล้ว หากเราสร้างคอลัมน์ใหม่ในตาราง dimension เช่น dProduct แล้วเขียนสูตรใน New Column เพื่อคำนวณค่าจากตาราง fact เช่น SUM(fSales[SalesQuantity]) มันจะออกมาได้ค่าเท่ากันหมด เพราะการเขียนสูตรในตารางนั้นมีแต่ Row Context ไม่มี Filter Context ซึ่งผลจะได้ดังนี้

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 19

แต่ถ้าเราใส่ CALCULATE ครอบลงไป หรือใช้การอ้างอิงด้วย Measure แทน (หากเขียนสูตรอ้างอิง Measure จะเสมือนว่ามี CALCULATE มาครอบโดยอัตโนมัติ) มันจะเกิด สิ่งที่เรียกว่า Context Transition ขึ้น คือจะเปลี่ยนเงื่อนไขในแต่ละแถว ให้กลายเป็น Filter จริงๆ ดังนี้

ProductQty = CALCULATE(SUM(fSales[SalesQuantity]))
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 20

ซึ่งให้ผลเหมือนกับแบบนี้เป๊ะ

ProductQty = [TotalQty]
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 21

Filter แบบเขียนสูตรเองไม่ง้อ Relationship

แบบนี้จะซับซ้อนสุด เพราะเราจะเขียนสูตรเพื่อ Filter เอง

สมมติผมอยากจะสร้าง Slicer ให้เลือกเดือนปีที่สิ้นสุด แล้วจะแสดงกราฟจำนวน Qty ย้อนหลัง 3 เดือนนับจากวันที่สิ้นสุดที่ผมเลือกไป ผมสามารถเอา YearMonth มาเป็น Slicer ตามนี้

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 22

จะเห็นว่าพอเลือกเดือนที่คิดว่าจะให้เป็นเดือนสิ้นสุดแล้ว มันดัน 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 ใดๆ ทั้งสิ้น

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 23

จากนั้นเราเปลี่ยนไปใช้ Slicer YearMonth จากตารางใหม่ จะพบว่าคราวนี้มันไม่มา Filter ตารางแล้ว (เพราะไม่มี Relationship กับ dDate)

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 24

คราวนี้เราจะสร้าง 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()) //คัดเลือกช่วงที่ต้องการ
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 25

ปกติแล้ว Visual ใน Power BI จะไม่แสดงค่าใน Row mี่มีค่าเป็น Blank ดังนั้นถ้าเราใส่แค่ Measure TotalQtyPrev3M ก็จะได้ผลลัพธ์เหลือแค่ 3 เดือนจริงๆ ดังนี้เลย

แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 26

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)
แนะนำสารพัดวิธี Filter ข้อมูลใน Power BI 27
แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 330
  •  
  •  
  •  
  •  
  • 330
  •  
  •  
  •  
  •