ThepExcel Logo
  • บทความ
    • Excel
      • Excel ทั่วไป
      • Excel Pivot Table
      • Excel Power Pivot
      • Power Query
      • Excel Array Formula
      • Excel VBA
      • Excel for Business
      • Excel and Maths
      • ฟังก์ชัน Excel ทั้งหมด
    • Power BI
      • Power Query
      • Data Model
      • DAX Formula
      • Power BI Report
    • Coding
      • Excel VBA
      • Python
      • Power Query M Code
    • AI
      • ChatGPT
      • Stable Diffusion
      • MidJourney
    • Highlights : บทความแนะนำ
    • คลิปวีดีโอ
  • อบรม
    • อบรมลูกค้าองค์กร
    • คอร์สออนไลน์ SkillLane
    • แนะนำวิทยากร
    • Excel/Power BI Skill Map
    • Quiz
  • Shop
    • คอร์สออนไลน์
    • สินค้าทั้งหมด
    • หนังสือเล่ม
    • E-Book
    • Cart
  • Download
    • Download ไฟล์จากเทพเอ็กเซล
    • ThepExcel-Mfx : M Code สำเร็จรูป
    • Date Table สำเร็จรูป
    • กราฟ My Skill
    • github.com/ThepExcel
  • รวม Link
    • รวม Link สอน Excel & Power BI ทั้งไทยและเทศ
    • รวม Link สอน Python / Programming
    • หนังสือแนะนำ
    • Facebook ThepExcel
    • YouTube ThepExcel
    • DAX Formatter
  • Contact
    • แนะนำ เทพเอ็กเซล (Thep Excel)
    • แนะนำวิทยากร : อาจารย์ ศิระ เอกบุตร (ระ)
    • นโยบายการคุ้มครองข้อมูลส่วนบุคคล (Privacy Policy)
  • Facebook
  • YouTube

เจาะลึก CALCULATE ใน DAX แบบลึกสุดใจ : Part 2

Categories 📂

DAX Formula

Tags 🏷️

calculate, dax

ตอนนี้เป็นเนื้อหาที่ต่อจาก https://www.thepexcel.com/calculate-dax-in-depth/ ซึ่งจำเป็นจะต้องอ่านก่อน ไม่งั้นไม่มีทางเข้าใจบทความนี้ได้เลยครับ ถ้าอ่านบทความก่อนจบแล้ว ก็เชิญมาต่อที่นี่ได้เลย

ใน Part2 นี้ผมจะปูความรู้ 2 เรื่องที่สำคัญมากๆ นั่นคือ Context Transition และ CALCULATETABLE ให้ก่อน ใน Part ถัดไปจะเอาทุกอย่างมายำกันละ

สารบัญ

  • Context Transition
    • ทำความเข้าใจ Context Transition เบื้องต้น
    • Context Transition ใน Measure แบบพื้นฐาน
      • ทำความเข้าใจสิ่งที่เกิดขึ้น
    • Context Transition ใน Measure แบบซับซ้อนขึ้น
      • Case ที่ 1 : เขียนเงื่อนไขที่ไม่ซ้ำกับ Row Context
      • Case ที่ 2 : มีการปลด Filter ที่ Row Context ออก
      • Case ที่ 3 : เขียนเงื่อนไขซ้ำกันกับ Row Context
      • ผลลัพธ์ของการใช้ Measure ทั้ง 3 จะเป็นแบบนี้
  • การใช้ CALCULATETABLE
    • ทำความรู้จัก CALCULATETABLE
    • จริงๆ แล้ว Time Intelligence นั้นแฝงไปด้วย CALCULATETABLE
  • Case ซับซ้อน :
    • อยากได้ยอดปีก่อนหน้า โดยสนใจวันประจำสัปดาห์ ด้วย CALCULATETABLE
    • ทำไม Time Intelligence ปกติถึงไม่ Work?
    • วิธีที่ Work
  • ตอนต่อไป

Context Transition

ทำความเข้าใจ Context Transition เบื้องต้น

ปกติแล้วถ้าเราเขียนสูตรใน New Column ของตาราง มันจะไม่มี Filter อะไรเกิดขึ้นเลย ดังนั้นถ้าเราอยู่ที่ตาราง Store แล้วเขียน SUM Quantity ก็จะได้ค่าเท่ากันหมดเลยเสมอ ซึ่งก็คือการ SUM คอลัมน์ที่เราเลือกทั้งตารางนั่นเอง

Store Qty = SUM(OrderDetail[Quantity])

แต่ถ้าหากเราเอา CALCULATE ไปครอบสูตรนั้น ผลลัพธ์จะเปลี่ยนไป กลายเป็นว่าเราได้ Qty เฉพาะร้านค้านั้นๆ

Store Qty = CALCULATE(SUM(OrderDetail[Quantity]))

สาเหตุคือ CACLCULATE จะทำการเปลี่ยนค่าในแต่ละคอลัมน์ของแถวนั้นๆ ทุกคอลัมน์ (Row Context) ให้กลายเป็น Filter Context ซึ่งกระบวนการนี้เรียกว่า Context Transition (นั่นคือจะมี Filter เกิดขึ้นมากมายตามจำนวนคอลัมน์ของข้อมูลเลย)

ทำให้เดิมทีที่ไม่มี Filter ใน Table กลายเป็นมี Filter ก่อนจะคำนวณ SUM(OrderDetail[Quantity]) ออกมา เราก็เลยได้ Quantity เฉพาะร้านค้านั้นๆ ไปโดยปริยาย ซึ่งก็เจ๋งดี

แต่ความแปลกที่แท้จริง คือ ถ้าเราเปลี่ยนสูตรไปอ้างอิง Measure Total Qty แทน ซึ่งมีสูตรดังนี้

Total Qty = SUM(OrderDetail[Quantity])

เช่น ในคอลัมน์ Store Qty เราเปลี่ยนสูตรเป็นแบบนี้

Store Qty = [Total Qty]

เราจะได้ผลลัพธ์เหมือนมี CALCULATE มาครอบทันที!! ซึ่งเกิดขึ้นเพราะว่าเมื่อใดก็ตามที่เราอ้างอิง Measure มันจะแอบเอา CALCULATE มาครอบให้โดยอัตโนมัติ!

Context Transition ใน Measure แบบพื้นฐาน

อย่างไรก็ตาม ปกติแล้วเราจะไม่ค่อยใช้ Context Transition ในตารางที่มีตัวตนจริงๆ หรอก แต่เราจะใช้ในตารางจำลองที่สร้างใน Measure อีกทีต่างหาก โดยเฉพาะเวลาใช้กับ Iterator เช่น SUMX, MAXX, RANKX, FILTER, ADDCOLUMNS อะไรแบบนี้เป็นต้น

พฤติกรรมแบบนี้ทำให้เราสามารถเขียนสูตรที่ซับซ้อนได้โดยเขียนสูตรที่สั้นขึ้นมากๆ เช่น สมมติผมต้องการคำนวณ Total Qty สูงสุดในระดับ Product ผมสามารถเขียน Measure สั้นๆ แบบนี้ได้เลย

MAX Qty per Product = MAXX('Product',[Total Qty])
  • สิ่งที่มันทำคือจะสร้างตาราง Product ขึ้นมาในสูตร (1 บรรทัด 1 Product)
  • แล้ว Add Column จำลองใหม่ขึ้นมา โดยใช้สูตร [Total Qty]
  • ซึ่งจะได้ Quantity รวมโดยมี Context Transition เกิดขึ้น
  • คอลัมน์จำลอง เลยได้ Quantity รวมในระดับ Product เกิดขึ้นมาในแต่ละแถว
  • จากนั้นหาค่ามากสุดออกมาเพราะเราใช้ MAXX (ตอนจบ หาค่ามากสุดของคอลัมน์จำลอง)

แล้วเราก็สามารถเอา Measure นี้ไปใช้ในรายงาน โดย Filter ด้วยอะไรก็ได้ (ตราบเท่าที่มัน Filter เจ้าตัว [Total Qty] ได้)

แต่ถ้าเราต้องการ Max Total Qty ในระดับที่ใหญ่กว่าระดับที่เรามีในตารางจริง เราจะใช้การอ้างอิงตารางทื่อๆ ไม่ได้ แต่ต้องสร้างตารางจำลองขึ้นมาในสูตรแทน ซึ่งทำได้ง่ายๆ ด้วย DISTINCT หรือ VALUES นั่นเอง เช่น

ผมต้องการ คำนวณ Total Qty สูงสุดในระดับ Brand ผมก็เขียนแบบนี้ได้เลย

MAX Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Total Qty])

ซึ่งจริงๆ แล้วต่อให้เราอยากได้ในระดับ Product หากเราใช้ DISTINCT หรือ VALUES จะทำให้คำนวณเร็วกว่าการอ้างอิงทั้งตารางแน่นอน ดังนั้นจึงสามารถแก้สูตรเดิมเป็นอันนี้ก็จะดีขึ้นครับ

MAX Qty per Product = MAXX(DISTINCT('Product'[ProductKey]),[Total Qty])

ทำความเข้าใจสิ่งที่เกิดขึ้น

สมมติผมจะวิเคราะห์ผลลัพธ์ใน MAX Qty per Brand ของลูกค้าทวีป Asia ในรายงาน ด้วยสูตรนี้

MAX Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Total Qty])

หลังจากมันสร้างตารางจำลองระดับ Brand ขึ้นมา ให้ 1 บรรทัดคือ 1 Brand แล้วสร้างคอลัมน์ใหม่ที่ใส่สูตรว่า [Total Qty] ลงไป

สมมติว่า ผมจำลองการมองที่บรรทัดของ “ตารางจำลอง” ที่ Brand เป็น Litware ให้ดู

แบ่งกลุ่ม

  1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    • Customer[Cust Continent]=”Asia”
  2. Row Context ดั้งเดิม
    • ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
  3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ
    • ไม่มี เพราะสูตรมีแค่ CALCULATE(SUM(OrderDetail[Quantity]))
  4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    • ไม่มี เพราะสูตรมีแค่ CALCULATE(SUM(OrderDetail[Quantity]))

ขั้นตอนการทำงาน

  • เก็บ Filter Argument เอาไว้ในใจ
    • ไม่มี
  • Copy Filter Context เดิมมาเป็น New Filter Context
    • Customer[Cust Continent]=”Asia”
  • Row Context
    • ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
  • CALCULATE Modifier
    • ไม่มี
  • เอา Filter Argument ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    • Customer[Cust Continent]=”Asia”
    • ‘Product'[ฺBrand]=”Litware“

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทั้ง Brand Litware และจากทวีป Asia ด้วยนั่นเอง

ดังนั้น ตารางจำลองที่ถูกสร้างขึ้นมามันจะคล้ายกับ Table อันนี้มากๆ ซึ่งค่าที่มากสุดคือ 38,384 นั่นเอง

แปลว่าคอลัมน์ใหม่ในตารางจำลองทั้งหมด ได้รับผล Filter จากทวีปนั้นๆ อย่างถูกต้อง จึงออกมาเป็น Total Qty ภายใต้ทวีปและ brand นั้นๆ ผลลัพธ์ตอน MAXX ที่ได้จึงเป็น Max Total Qty per Brand ภายใต้แต่ละทวีปอย่างถูกต้อง ซึ่งก็คือ 38,384 นั่นเองครับ

Context Transition ใน Measure แบบซับซ้อนขึ้น

ทีนี่ถ้าใน Measure ที่เราทำ Context Transition ดันมี CALCULATE ที่มีเงื่อนไขอยู่แล้วล่ะ? บอกไว้ก่อนว่าอันนี้ทำเพื่อการศึกษาเท่านั้น ในชีวิตจริงบาง Measure ที่เขียนในนี้จะดูไม่ Make Sense มากๆ 55

Case ที่ 1 : เขียนเงื่อนไขที่ไม่ซ้ำกับ Row Context

ถ้าเราใช้ Measure นี้ใน Context Transition

Person Cust Total Qty = CALCULATE([Total Qty],Customer[Customer Type]="Person")

เช่นสมมติว่า ผมเขียน Measure ที่จะหาว่า Quantity รวมของลุกค้าบุคคลที่เยอะที่สุดต่อ 1 Brand คือเท่าไหร่ ซึ่งเขียนได้แบบนี้

MAX Person Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Person Cust Total Qty])

ดังนั้นการวิเคราะห์ การมองที่บรรทัดของ “ตารางจำลอง” ที่ Brand เป็น Litware จะเป็นแบบนี้

แบ่งกลุ่ม

  1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    • Customer[Cust Continent]=”Asia”
  2. Row Context ดั้งเดิม
    • ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
  3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ
    • Customer[Customer Type]=”Person” (จาก CALCULATE ใน Measure)
  4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    • ไม่มี

ขั้นตอนการทำงาน

  • เก็บ Filter Argument เอาไว้ในใจ
    • Customer[Customer Type]=”Person”
  • Copy Filter Context เดิมมาเป็น New Filter Context
    • Customer[Cust Continent]=”Asia”
  • Row Context
    • ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
  • CALCULATE Modifier
    • ไม่มี
  • เอา Filter Argument ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    • Customer[Cust Continent]=”Asia”
    • ‘Product'[ฺBrand]=”Litware“
    • Customer[Customer Type]=”Person”

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทั้ง Brand Litware และจากทวีป Asia และมาจากลูกค้าบุคคล ด้วยนั่นเอง

Case ที่ 2 : มีการปลด Filter ที่ Row Context ออก

ถ้าเราใช้ Measure นี้ใน Context Transition

Total Qty All Brand = CALCULATE([Total Qty],REMOVEFILTERS('Product'[Brand]))

เช่นสมมติว่า ผมเขียน Measure ที่จะหาว่า Quantity รวมของลุกค้าบุคคลที่เยอะที่สุดต่อ 1 Brand คือเท่าไหร่ ซึ่งเขียนได้แบบนี้

MAX Person Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Total Qty All Brand])

ดังนั้นการวิเคราะห์ การมองที่บรรทัดของ “ตารางจำลอง” ที่ Brand เป็น Litware จะเป็นแบบนี้

แบ่งกลุ่ม

  1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    • Customer[Cust Continent]=”Asia”
  2. Row Context ดั้งเดิม
    • ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
  3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ
    • ไม่มี
  4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    • REMOVEFILTERS(‘Product'[Brand])

ขั้นตอนการทำงาน

  • เก็บ Filter Argument เอาไว้ในใจ
    • ไม่มี
  • Copy Filter Context เดิมมาเป็น New Filter Context
    • Customer[Cust Continent]=”Asia”
  • Row Context
    • ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
  • CALCULATE Modifier
    • REMOVEFILTERS(‘Product'[Brand]) ทำให้ New Filter Context เรื่อง Brand หายไป
  • เอา Filter Argument ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    • Customer[Cust Continent]=”Asia”

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทวีป Asia อย่างเดียว ผลที่ได้จึงไม่ต่างจากการใช้ Total Qty เฉยๆ นั่นเอง

Case ที่ 3 : เขียนเงื่อนไขซ้ำกันกับ Row Context

ถ้าเราใช้ Measure นี้ใน Context Transition

Contoso Total Qty = CALCULATE([Total Qty],'Product'[Brand]="Proseware")

เช่นสมมติว่า ผมเขียน Measure ที่จะหาว่า Quantity รวมของ Brand Proseware ที่เยอะที่สุดต่อ 1 Brand คือเท่าไหร่ ซึ่งเขียนได้แบบนี้

MAX Proseware Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Proseware Total Qty])

ดังนั้นการวิเคราะห์ การมองที่บรรทัดของ “ตารางจำลอง” ที่ Brand เป็น Litware จะเป็นแบบนี้

แบ่งกลุ่ม

  1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    • Customer[Cust Continent]=”Asia”
  2. Row Context ดั้งเดิม
    • ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
  3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ
    • ‘Product'[Brand]=”Proseware” (จาก CALCULATE ใน Measure)
  4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    • ไม่มี

ขั้นตอนการทำงาน

  • เก็บ Filter Argument เอาไว้ในใจ
    • ‘Product'[Brand]=”Proseware”
  • Copy Filter Context เดิมมาเป็น New Filter Context
    • Customer[Cust Continent]=”Asia”
  • Row Context
    • ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
  • CALCULATE Modifier
    • ไม่มี
  • เอา Filter Argument ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    • Customer[Cust Continent]=”Asia”
    • ‘Product'[Brand]=”Proseware” ซึ่งจะมาทับ Litware ไปเลยเนื่องจากอ้างอิงถึง Field เดียวกัน

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทั้ง Brand Prosware และจากทวีป Asia นั่นเอง

ผลลัพธ์ของการใช้ Measure ทั้ง 3 จะเป็นแบบนี้

การใช้ CALCULATETABLE

ทำความรู้จัก CALCULATETABLE

CALCULATETABLE คือฟังก์ชันที่มีความสามารถเปลี่ยน Filter Context ได้ ซึ่งคล้าย CALCULATE มากๆ ต่างกันนิดเดียวคือ CALCULATETABLE ต้องให้ผลลัพธ์ออกมาเป็นตาราง แทนที่จะเป็นค่าค่าเดียวแบบที่ CALCULATE ทำได้

CALCULATETABLE ( <ตาราง> , [ Filter1 ] , [ Filter2],... )

ยกตัวอย่างเช่น ถ้าผมต้องการตาราง OrderDetail เฉพาะรายการที่มาจากลูกค้าบริษัท และมาจาก ร้านค้าทวีป Asia และซื้อสินค้าที่ราคาแพงกว่า 1000 บาท ผมสามารถเขียนแบบนี้ได้เลย

Person Order in Asia Store = 
CALCULATETABLE(OrderDetail,
    Customer[Customer Type]="Person",
    Store[Store Continent]="Asia",
    'Product'[Unit Price]>1000
    )

ซึ่งผลลัพธ์ที่ได้จะออกมาเป็นตาราง คล้ายกับการใช้ FILTER เลย แต่ว่าผมสามารถเขียนเงื่อนไขจาก Field ไหนก็ได้ใน Model โดยไม่ต้องใช้ RELATED เหมือนกรณีทำด้วย FILTER ด้วยซ้ำ

สำหรับข้อเสียของ CALCULATETABLE ก็เป็นเช่นเดียวกับ CALCULATE คือ การเขียนอ้างอิงเงื่อนไข จะต้องอ้างถึงคอลัมน์ที่มีตัวตนใน Data Model เท่านั้น เช่น ผมจะเขียนเงื่อนไขว่าเอาเฉพาะรายการที่อัตรากำไรเกิน 60% แบบนี้ไม่ได้ เพราะผมไม่ได้มีคอลัมน์นั้นจริงๆ (แต่ FILTER ทำได้)

การใช้ CALCULATETABLE ปกติเค้าไม่ได้ใช้สร้างตารางที่มีตัวตนจริงๆ หรอกครับ แต่เค้าใช้สร้างตารางจำลองใน Measure มากกว่า ซึ่งจะมีประโยชน์มากๆ เลย และในเมื่อมันให้ผลลัพธ์เป็นตาราง มันจึงสามารถใช้เป็นเงื่อนไขของ CALCULATE ได้ด้วย!!

จริงๆ แล้วเราก็สามารถใช้ Table Function อะไรก็ตามใน CALCULATETABLE ได้หมดเลย รวมถึงเงื่อนไข Filter ของ CALCULATETABLE จริงๆ แล้วก็เป็นตารางเช่นเดียวกับ CALCULATE ด้วยนั่นเอง ดังนั้นสูตรข้างบนสามารถเขียนได้แบบนี้ (เหมือนกรณี CALCULATE)

Person Order in Asia Store = 
CALCULATETABLE(OrderDetail,
    FILTER(ALL(Customer[Customer Type]),Customer[Customer Type]="Person"),
    FILTER(ALL(Store[Store Continent]),Store[Store Continent]="Asia"),
    FILTER(ALL('Product'[Unit Price]),'Product'[Unit Price]>1000)
    )

ซึ่ง Performance จะดีกว่าการ FILTER ตรงๆ ด้วย เนื่องจากมีการใช้ ALL ทำให้เห็นค่าไม่ซ้ำก่อนจะคิดเงื่อนไข ทำให่การ Scan ใช้เวลาน้อยลง

จริงๆ แล้ว Time Intelligence นั้นแฝงไปด้วย CALCULATETABLE

ฟังก์ชัน Time Intelligence ถ้ามีการอ้างอิงถึงคอลัมน์ Date ใน Date Table จริงๆ นั้นแฝงไปด้วย CALCULATETABLE อยู่นะ เช่น

เช่น SAMEPERIODLASTYEAR ที่ดูง่ายๆ เหมือนไม่มีอะไรนั้น หาเขียนแบบนี้

SAMEPERIODLASTYEAR(dDate[Date])

จะหมายถึง

SAMEPERIODLASTYEAR( CALCULATETABLE ( DISTINCT (dDate[Date]) ) )

ซึ่งก็จะให้ผลลัพธ์เป็นตารางที่ย้อนเวลากลับไป 1 ปีนั่นเอง (และอาจมี Context Transition ได้)

Case ซับซ้อน :

อยากได้ยอดปีก่อนหน้า โดยสนใจวันประจำสัปดาห์ ด้วย CALCULATETABLE

สมมติว่าผมต้องการแสดงยอด Total Qty เฉพาะวันที่เลือกในแต่ละปีของ Brand ที่สนใจ แล้วผมเลือก Slicer วันจันทร์จะได้แบบนี้

ทีนี้ผมอยากเอาปีมาเป็น Slicer แล้วเลือกปี 2020 ไว้ จากนั้นผมอยากจะแสดงยอดของปีก่อนหน้าด้วย จึงใช้ SAMEPERIODLASTYEAR ทำ Time Intelligence ออกมา ได้แบบนี้ แล้วลองเอาไปใส่ในรายงานที่ต้องการ

Total Qty Prev Year = CALCULATE([Total Qty],SAMEPERIODLASTYEAR(dDate[Date]))

ทำไม Time Intelligence ปกติถึงไม่ Work?

ปรากฏว่าเลขออกมาไม่ถูกต้อง!! เพราะเลข Prev Year ที่ได้ไม่เห็นตรงกับของปี 2019 ในตารางข้างบนเลย??

สาเหตุที่ไม่ตรง เป็นเพราะ SAMEPERIODLASTYEAR เริ่มต้นจาก Filter Context ที่เป็นวันจันทร์ทั้งหมด แล้วย้อนเวลากลับไป 1 ปี ให้ลงเลขวันเดิม ซึ่งอาจจะไม่ใช่วันจันทร์แล้วนั่นเอง ดังนั้นผลที่ได้จึงไม่ใช่วันจันทร์ของปี 2019

แล้วถ้าเราต้องการวันจันทร์ของปีก่อนหน้าด้วยจริงๆ เราจะทำยังไงล่ะ?

ผมจะลองใช้วิธีสร้างตารางวันที่ในปีปัจจุบันทั้งหมด แล้วย้อนเวลากลับไป 1 ปีจากนั้นค่อยเลือกให้เป็นวันจันทร์ โดยผมจะใช้วิธีสร้างตัวแปรมาเก็บค่าก่อน จะได้ไม่ซับซ้อนจนเกินไป แล้วลองใช้ COUNTROWS เช็คดูว่าผลลัพธ์เป็นอย่างไร

สมมติผมเขียนแบบนี้ ว่าให้ลองดูคอลัมน์วันที่แล้วนับจำนวนแถวออกมา

Total Qty Prev Year SelectedDay = 
VAR PrevYearDate =CALCULATETABLE(DISTINCT(dDate[Date]))
RETURN COUNTROWS(PrevYearDate)

ผลจะได้ออกมาเป็น 52 เพราะในปี 2020 ที่เลือกอยู่นั้นมีวันจันทร์ 52 วันนั่นเอง

ทีนี้ผมต้องการจะทำการปลด Filter เรื่องวันออกไปซะ ก่อนจะทำการ DISTINCT วันออกมา ดังนั้นสามารถใช้ CALCULATETABLE เขียนแบบนี้ได้

Total Qty Prev Year SelectedDay = 
VAR PrevYearDate =CALCULATETABLE(DISTINCT(dDate[Date]),REMOVEFILTERS(dDate[DayName]))
RETURN COUNTROWS(PrevYearDate)

ผลลัพธ์จำนวนวันที่จะได้ 366 วัน นั่นคือจำนวนวันในปี 2020 นั่นเอง

ทีนี้ผมจะทำการย้อนเวลากลับไป 1 ปี ก็เลวอยากใช้ SAMEPERIODLASTYEAR หรือ DATEADD มาช่วย

แต่ผมจะสร้างตัวแปรใหม่แบบนี้ไม่ได้นะ เพราะตัวแปรใน VAR มันจะถูกคำนวณทิ้งไว้จนกลายเป็นค่าคงที่แล้ว ถึงจะเปลี่ยน Filter Context ก่อนคำนวณค่าคงที่ก็ไม่มีผลอะไร (มันจะได้ 366 วันซึ่งผิด)

Total Qty Prev Year SelectedDay = 
VAR PrevYearDate =CALCULATETABLE(DISTINCT(dDate[Date]),REMOVEFILTERS(dDate[DayName]))
VAR PrevYearDate2=CALCULATETABLE(PrevYearDate,SAMEPERIODLASTYEAR(dDate[Date]))
RETURN COUNTROWS(PrevYearDate2)

วิธีที่ถูกต้องคือต้องเขีนยสูตรที่สามารถเปลี่ยน Filter Context ตอนคิดตัวแปรแรกเลย

แต่จะเขียนแค่นี้ก็ไม่ Work อีก…

Total Qty Prev Year SelectedDay =
VAR PrevYearDate =
    CALCULATETABLE (
        DISTINCT ( dDate[Date] ),
        REMOVEFILTERS ( dDate[DayName] ),
        SAMEPERIODLASTYEAR ( dDate[Date] )
    )
RETURN
    COUNTROWS ( PrevYearDate )

สาเหตุที่ไม่ Work เราสามารถใช้วิธีการคิดของ CALCULATE มาพิจารณาได้ครับ เช่น

แบ่งกลุ่ม

  1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    • dDate[Year]=2020
    • dDate[DayName]=”Monday”
  2. Row Context ดั้งเดิม
    • ไม่มี
  3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ
    • SAMEPERIODLASTYEAR ( dDate[Date] )
  4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    • REMOVEFILTERS ( dDate[DayName] ) ที่เราตั้งใจเขียน
    • REMOVEFILTERS ( dDate ) ที่แถมมาจาก Time Intelligence เช่น SAMEPERIODLASTYEAR

ขั้นตอนการทำงาน

  • เก็บ Filter Argument เอาไว้ในใจ
    • SAMEPERIODLASTYEAR ( dDate[Date] ) อันนี้มันจะมองวันจันทร์ปี 2020 ก่อน แล้วย้อนกลับไป 1 ปี ซึ่งจะไม่ใช่วันจันทร์ (ปัญหาเดิม)
  • Copy Filter Context เดิมมาเป็น New Filter Context
    • dDate[Year]=2020
    • dDate[DayName]=”Monday”
  • Row Context
    • ไม่มี
  • CALCULATE Modifier
    • REMOVEFILTERS ( dDate[DayName] )
    • REMOVEFILTERS ( dDate )
    • สรุปแล้ว Filter ในตาราง Expanded ของ dDate หายหมด
  • เอา Filter Argument ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    • SAMEPERIODLASTYEAR ( dDate[Date] ) (ปัญหาเดิม)

นี่คือสาเหตุที่ไม่ Work ครับ เพราะเรายังติดปัญหาเดิมอยู่ดี Step การทำงานของมันไม่ได้ทำงานตามลำดับที่เราคาดหวังไว้ เพราะตัว SAMEPERIODLASTYEAR มันมาจำกัดวันที่ให้เห็นแค่วันจันทร์ ในขณะที่จริงๆ ผมอยากได้วันที่ของปีที่แล้วแบบไม่สนใจวันประจำสัปดาห์

วิธีที่ Work

ถ้าเราเข้าใจลำดับการคำนวณเวลาใช้ CALCULATE ซ้อนกันดีแล้ว และเข้าใจพฤติกรรมของ VAR แล้ว เราจะสามารถกำหนดลำดับที่ถูกต้องได้แบบนี้

แม้การคิดสูตรใดๆ ก็ตามจะต้องคิดที่ตัวในสุดก่อน แต่ CALCULATE หรือ CALCULATETABLE ตัวในสุดมันจะมองสภาพแวดล้อมของ Filter Context ที่มันทำงานอยู่ (ซึ่งมีผลจาก CALCULATE/CALCULATETABLE ตัวนอกได้) ดังนั้นสรุปแล้ว เวลาคิดเราจะต้องไล่เปลี่ยน Filter ของ CALCULATETABLE ชั้นนอกก่อน แล้วค่อยไล่เปลี่ยนชั้นใน ดังนั้นเราสามารถเขียนแบบนี้ได้เลย

Total Qty Prev Year SelectedDay = 
VAR MonthList=DISTINCT(dDate[Month])      //Step1 เก็บค่าเดือน Context ปัจจุบันไว้เป็นค่าคงที่ 
VAR DayList=DISTINCT(dDate[DayName])      //Step1 เก็บค่าวัน Context ปัจจุบันไว้เป็นค่าคงที่
VAR PrevYearDate =
    CALCULATETABLE(
        CALCULATETABLE (
        CALCULATETABLE (
            DISTINCT ( dDate[Date] ),
            MonthList,                           //Step4 ทำให้เห็นเฉพาะวันที่เห็นใน Context
            DayList                              //Step4 ทำให้เห็นเฉพาะเดือนที่เห็นใน Context
        ),
        SAMEPERIODLASTYEAR ( dDate[Date] )),     //Step3 ย้อน 1 ปี เห็นวันที่ปีก่อนทั้งปีเสมอ
        ALLEXCEPT(dDate,dDate[Year])             //Step2 เห็นวันที่ในปีปัจจุบันทั้งปีเสมอ
    )
VAR Result= CALCULATE([Total Qty],PrevYearDate)  //Step5 เอาตารางวันที่ในปีก่อนหน้ามาคำนวณ
RETURN Result

แปลว่าขั้นตอนการคิดจะเป็นแบบนี้ นั่นคือไล่ Filter Context จากชั้นนอกไปใน

  • ตัวแปร MonthList และ DayList เก็บค่าเป็นตาราง ณ Context แรกสุดเอาไว้ก่อนเป็นค่าคงที่ ไม่เปลี่ยนแปลง
  • ALLEXCEPT ( dDate, dDate[Year] ) มันจะปลด Filter ทุกอย่างยกเว้นปี ทำให้เห็นเฉพาะปีปัจจุบัน แบบเต็มปีเสมอ
  • SAMEPERIODLASTYEAR ( dDate[Date] ) ในเมื่อเดิมมองเห็นเต็มปีแล้ว ก็จะย้อนกลับไป 1 ปี แบบเต็มปีด้วย
  • จากสั้นใส่ Filter ตาม MonthList และ DayList เข้าไป เพื่อให้เหลือช่วงเดือนและวันประจำสัปดาห์ที่ถูกต้อง
  • แล้วค่อย List คอลัมนืวันที่ออกมาเป็นตารางด้วย DISTINCT ( dDate[Date] ) แล้วเก็บไว้ในตัวแปร PrevYearDate
  • แล้วเอาตัวแปร PrevYearDate ไปใช้ใน CALCULATE เพื่อคำนวณ [Total Qty] ออกมาเป็นอันจบเลย

สุดท้ายเราจะได้ค่าที่ถูกต้องดังนี้ เหนื่อยเลย

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

สำหรับบทความนี้ยาวและยากมากๆ แล้ว ผมขอจบเท่านี้ก่อน

ตอนต่อไป

ตอนต่อไปจะเอาทุกอย่างมายำกันแบบสุดๆ แล้ว น่าจะเป็นตอนจบแล้วล่ะครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ
120    
120    

ติดตามเทพเอ็กเซล

  • Facebook
  • YouTube

อบรมกับเทพเอ็กเซล

🔥 คอร์สใหม่ล่าสุด 🔥

การทำ Optimization ด้วย Excel Solver
สำหรับงานวางแผน
คอร์สออนไลน์ เทพเอ็กเซล
คอร์สออนไลน์ จากเทพเอ็กเซล ดูกี่รอบก็ได้
อบรม Excel / Power BI ให้องค์กรของคุณ

บทความล่าสุด

  • วิธีสั่ง Prompt และตั้งค่าใน Stable Diffusion ให้รูปสวยโดนใจ [Part3]
  • วิธีเรียกใช้งาน Model เจ๋งๆ ใน Stable Diffusion [ตอนที่2]
  • วิธีใช้งาน AI สร้างรูปสุดเจ๋งและฟรีด้วย Stable Diffusion ฉบับมือใหม่ [ตอนที่1]
  • 10 ไอเดีย เรียนรู้ Excel ผ่าน ChatGPT AI สุดเจ๋ง
  • การทำ Simulation ด้วย Excel
  • แกะเคล็ดวิชา Excel Wizard ในการแข่ง Speed Run Excel ระดับโลก
  • เจาะลึก CALCULATE ใน DAX แบบลึกสุดใจ : Part 2

บทความแนะนำ

🔥ฟังก์ชันทั้งหมดใน Excel 🔥

  • แกะเคล็ดวิชา Excel Wizard ในการแข่ง Speed Run Excel ระดับโลก
  • เจาะลึก CALCULATE ใน DAX แบบลึกสุดใจ : Part 1
  • Series สอนดึงข้อมูลจากเว็บ ด้วย Power Automate Desktop
  • สรุปการใช้ LAMBDA ฟังก์ชันที่ใช้สร้างฟังก์ชันใน Excel 365 และผองเพื่อน
  • วิธีใช้ Excel คำนวณระยะเวลาการทำงานรวม แถมระบุเวลาพักได้แบบยืดหยุ่น
  • วิธีจัดการข้อมูลแย่ๆ ด้วย Power Query ทั้งข้อมูลปนกัน ข้อมูลอยู่บนหัวตาราง
  • แยกข้อมูลที่อยู่สุดเน่า ด้วย Excel Power Query

Categories

Tags

ai collection concepts copy database Data Model data table data validation date dax dropdown error excel filter finance find format formula function game graph IF index intro irr len link logic lookup match m code merge mid overview paste pivot power query row sort speed split substitute table text textjoin time tips trim vba vlookup

Archives

  • March 2023 (2)
  • February 2023 (2)
  • January 2023 (1)
  • October 2022 (1)
  • September 2022 (3)
  • August 2022 (3)
  • July 2022 (1)
  • June 2022 (3)
  • May 2022 (1)
  • April 2022 (2)
  • February 2022 (1)
  • December 2021 (2)
  • November 2021 (10)
  • September 2021 (2)
  • August 2021 (6)
  • July 2021 (2)
  • June 2021 (2)
  • May 2021 (10)
  • April 2021 (3)
  • March 2021 (3)
  • February 2021 (4)
  • January 2021 (8)
  • December 2020 (5)
  • November 2020 (13)
  • October 2020 (5)
  • September 2020 (11)
  • August 2020 (4)
  • July 2020 (13)
  • June 2020 (17)
  • May 2020 (16)
  • April 2020 (16)
  • March 2020 (10)
  • February 2020 (15)
  • January 2020 (16)
  • December 2019 (4)
  • November 2019 (3)
  • October 2019 (9)
  • September 2019 (1)
  • August 2019 (7)
  • June 2019 (3)
  • May 2019 (9)
  • April 2019 (9)
  • March 2019 (2)
  • February 2018 (1)
  • January 2018 (3)
  • November 2017 (3)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (1)
  • May 2017 (6)
  • April 2017 (6)
  • March 2017 (7)
  • February 2017 (1)
  • January 2017 (2)
  • December 2016 (1)
  • October 2016 (2)
  • September 2016 (3)
  • August 2016 (2)
  • July 2016 (2)
  • June 2016 (1)
  • May 2016 (1)
  • April 2016 (1)
  • March 2016 (2)
  • February 2016 (1)
  • January 2016 (2)
  • December 2015 (2)
  • November 2015 (5)
  • October 2015 (3)
  • June 2015 (2)
  • May 2015 (1)
  • April 2015 (26)
  • January 2015 (1)
  • December 2014 (1)
  • November 2014 (2)
  • October 2014 (1)
  • September 2014 (2)
  • August 2014 (1)
  • June 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (3)
  • February 2014 (12)
  • January 2014 (7)
  • December 2013 (2)
  • November 2013 (8)
  • October 2013 (2)

เทพเอ็กเซล : Thep Excel

copyright © 2022

  • Facebook
  • YouTube
เว็บไซต์นี้ใช้คุกกี้ (Cookies)
บริษัท เทพเอ็กเซล จำกัด ให้ความสำคัญต่อข้อมูลส่วนบุคคลของท่าน เพื่อการพัฒนาและปรับปรุงเว็บไซต์รวมถึงสินค้าและบริการต่างๆ หากท่านใช้บริการเว็บไซต์นี้ โดยไม่มีการปรับตั้งค่าใดๆ แสดงว่าท่านยินยอมที่จะรับคุกกี้บนเว็บไซต์ และนโยบายสิทธิส่วนบุคคลของเรา
ตั้งค่าคุกกี้ยอมรับทั้งหมดอ่านเพิ่มเติม
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT