Principle of DAX : หลักการทำงานที่แท้จริงของ DAX ใน Power BI และ Excel

บทความนี้เป็นบทความที่ผมตั้งใจทำมากๆ เพื่อที่จะช่วยให้เพื่อนๆ ที่ศึกษา DAX ไม่ว่าจะเป็นใน Data Model ของ Power BI หรือ Excel ก็ตาม ได้เข้าใจการทำงานของมันให้แม่นยำและลึกซึ้งมากขึ้น เพราะหลายครั้งผมได้เจอคนใช้งาน Power BI ที่เหมือนจะเข้าใจผิดหรือไม่เข้าใจในการทำงานของมันจริงๆ ซึ่งจะยิ่งแย่เข้าไปใหญ่หากมีการนำความรู้ที่ไม่ถูกต้องไปถ่ายทอดต่ออีก (ไม่แปลกถ้าแรกๆ จะเข้าใจผิด ผมก็เคยมีหลายอย่างที่เข้าใจผิดกับ DAX มาก่อน แต่ถ้าเราเข้าใจได้ถูกต้องมันย่อมดีกว่าเนอะ)

DAX มีความคล้ายกับฟังก์ชันใน Excel มากๆ แต่ก็มีหลายฟังก์ชันที่ไม่เหมือนกัน และหลักการทำงานก็ไม่ค่อยเหมือนกันด้วยนะ…

การทำงานของ DAX นั้นมีหลักการพื้นฐานที่สำคัญมากๆ อยู่ที่คำ 2 คำ นั่นคือ Row Context และ Filter Context ซึ่งถ้าหากเราไม่แม่นเรื่องนี้ เราจะงงมากๆ เวลาเจอการทำงานของ DAX ที่เริ่มจะมีความซับซ้อนมากขึ้น (อย่าลืมว่า DAX เป็นภาษาที่สามารถเขียนได้ซับซ้อนมากๆ เช่น สูตรเดียวยาว 20-30 บรรทัดได้ ตัวอย่างเช่นในเว็บนี้ )

เราเขียน DAX ได้ที่ไหนบ้าง?

• New Measure : เป็นสูตรคำนวณที่เอาไว้ใส่ใน Values ของ Visual ใน Report มักเป็นการสรุปข้อมูล เช่น SUM, AVERAGE, DISTINCTCOUNT ซึ่งผลลัพธ์ของ Measure จะต้องเป็นข้อมูลที่มีค่าเดียว แต่จะเป็นตัวเลข หรือ ข้อความก็ได้ แต่จะเป็นตารางหรือ ข้อมูลที่มีหลายค่าไม่ได้
  • New Quick Measure (มีใน Power BI เท่านั้น) : ให้ Power BI ช่วยสร้าง Measure ให้แบบอัตโนมัติจากรูปแบบที่โปรแกรมเตรียมไว้ เช่น Year to Date Summary
• New Column ใน ตารางข้อมูล : เป็นการเขียนสูตรเพื่อสร้างคอลัมน์เพิ่มในตารางข้อมูล มักจะใช้เพื่อเอาไว้ลากลงส่วน Category/Axis ของ Visual ใน Report (หรือใช้ทดข้อมูลก่อนสร้าง Measure ก็ได้)
• New Table (มีใน Power BI เท่านั้น) : ไว้สร้างตารางขึ้นมาใหม่ด้วยสูตร เช่น Date Table

วิธีเขียน DAX เพื่ออ้างอิงสิ่งต่างๆ

• อ้างอิง Table : อ้างอิงด้วย TableName
• อ้างอิง Column : อ้างอิงด้วย TableName[ColumnName]
• อ้างอิง Measure : อ้างอิงด้วย [MeasureName]
  • สาเหตุที่เราไม่ใส่ชื่อตารางหน้า Measure เพราะ จะได้ไม่สับสนกับชื่อคอลัมน์ และ ตารางที่ Measure อยู่ก็ไม่มีผลกับการคำนวณ

จากนั้นเรามาดูความหมายของ Row Context และ Filter Context แบบเบื้องต้นกันก่อนครับ

Row Context vs Filter Context เบื้องต้น

Row Context ขั้นต้น

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

ซึ่งจะเกิดขึ้นโดยมีการคำนวณทีละ Row ของ Table คล้ายๆ การวน Loop ทีละบรรทัดของตารางจนครบ เช่น การเพิ่ม New Column โปรแกรมก็ต้องคำนวณค่าของแต่ละแถวออกมา หรือ การใช้ฟังก์ชันจำพวก iterator เช่น SUMX, FILTER ก็ต้องพิจารณาทีละแถวเป็นต้น (เดี๋ยวจะพูดถึงภายหลัง)

ตัวอย่างที่เข้าใจง่ายสุด คือ การ เพิ่ม New Column ชื่อว่า Revenue โดยจะคำนวณยอดขาย จาก คอลัมน์ Unit Price และ Quantity ในตาราง OrderDetail ซึ่งเราจะใส่สูตรแบบนี้

Revenue = OrderDetail[Quantity]*OrderDetail[Unit Price]

จะเห็นว่า แม้จะเขียนสูตรอ้างอิงคอลัมน์ทั้งคอลัมน์คูณกัน แต่ว่าค่าผลลัพธ์ของ Revenue ในแต่ละแถวของตารางนั้นสามารถได้ผลลัพธ์ออกมาเป็นค่าเดียวได้ (แถมค่าเป็นของแถวใครแถวมัน) สาเหตุเป็นเพราะมี Row Context อยู่นั่นเอง

หากว่าเราดันไปเขียนสูตรเดียวกันนี้เลยแต่ใส่ลงใน Measure มันก็จะไม่ Work… เพราะว่า Measure นั้นเป็นสิ่งที่ต้องได้ผลลัพธ์เป็นค่าเดียว เพื่อเอาไปแสดงใน Value การที่มันไม่สามารถคำนวณค่าเดียวออกมาได้ เพราะใน Report ไม่มี Row Context อยู่นั่นเอง ทำให้สูตรไม่สามารถแสดงค่าเดียวออกมาได้ (แค่จะเขียนว่า = OrderDetail[Quantity] เฉยๆ ยังไม่ได้เลย)

เอาล่ะ เราน่าจะพอเห็นภาพของ Row Context เบื้องต้นกันไปแล้ว เดี๋ยวไปทำความรู้จัก Filter Context กันก่อน

Filter Context ขั้นต้น

เป็นการพิจารณาว่ามีเงื่อนไขการ Filter อะไรบ้าง ในตำแหน่งที่สูตรกำลังคำนวณอยู่

ซึ่งการพิจารณา Filter จะเกิดขึ้นในหน้า Report นั่นเอง ซึ่งเราต้องพิจารณา Filter ทั้งในส่วนประกอบ Category/Axis ของ Visual นั้นๆ และทั้ง Visual/Page/Report Filter ใน Filter Pane รวมถึง Interaction ที่มาจาก Visual อื่นด้วย

ถ้าให้เข้าใจง่ายที่สุด หากผมเขียน Measure เพื่อจะคำนวณยอดขายรวม ดังนี้

Total Revenue = SUM(OrderDetail[Revenue])

หากผมลากลงไปใน Visual มันจะคำนวณค่าได้ถูกต้อง ตาม Category/Axis ของ Visual นั้นๆ แม้ทุกช่องจะคำนวณด้วยสูตรเดียวกัน ทั้งนี้เพราะแต่ละตำแหน่งมี Filter Context ไม่เหมือนกันนั่นเอง

อย่างเช่นในรูปข้างบน ตรง Brand Contoso ทวีป Asia ได้ยอด TotalRevenue เป็น 163,171,265 เป็นผลมาจากการที่มี Filter Context ซึ่งเปรียบเสมือนว่ามันทำการ Filter ข้อมูลให้ Brand เป็น Contoso และ Store Continent เป็น Asia (ซึ่งแม้จะอยู่คนละตารางกัน แต่ผลของมันวิ่งผ่าน Relationship ใน Data Model มา Filter ตาราง Order Detail ต่อได้) ทำให้คอลัมน์ Revenue มีจำนวนแถวลดลงก่อนจะทำการ SUM นั่นเอง

แต่ถ้าเราพยายามจะมาเขียนสูตร SUM Revenue ใน New Column ของตาราง มันจะได้ผลลัพธ์ที่น่าประหลาดใจ นั่นก็คือเราจะได้ค่าเท่ากันหมดเลย! (ซึ่งเป็นผลรวมของคอลัมน์ Revenue ทั้งตารางโดยไม่มีการ Filter ใดๆ) ทั้งนี้เป็นเพราะส่วนของตาราง Data จริงๆ นั้นมันไม่มี Filter Context นั่นเอง

หรือจะไปทำที่ตารางอื่น เช่น ตาราง Product (Dimension Table) แล้วคำนวณ Revenue จาก FactTable ก็ไม่ Work เช่นกันนะ

Row Context vs Filter Context ขั้นกลาง

หลังจากที่เราได้เริ่มรู้จัก Row Context และ Filter Context แบบเบื้องต้นกันไปแล้ว เดี๋ยวเราจะมาเรียนรู้ในระดับกลางๆ กันบ้าง

Row Context ขั้นกลาง

โดยผมจะขอเริ่มที่ Row Context ในกรณีที่ไม่ได้อยู่ที่ New Column แต่จะอยู่ในฟังก์ชันพวก Iterator อย่างตระกูล …X ทั้งหมดเช่น SUMX, AVERAGEX, CONCATENATEX (และอีกมากมาย) รวมถึงฟังก์ชันอย่าง FILTER ก็มี Row Context ด้วยเช่นกัน

สมมติว่าผมต้องการจะคำนวณ Measure เรื่อง Total Revenue โดยไม่ได้สร้างคอลัมน์ Revenue มาก่อน อันนี้ถ้าเราใช้ SUM ธรรมดาแล้วจะเอาสองคอลัมน์คูณกันในนั้นเลย มันจะไม่ Work เพราะ SUM มันอ้างอิงได้แค่คอลัมน์เดียวเท่านั้น

Total Revenue Wrong2 = SUM(OrderDetail[Quantity]*OrderDetail[Unit Price])

ครั้งจะ SUM แต่ละก้อนแล้วจับคูณกัน มันก็จะผิดอีก เพราะไม่ใช่สิ่งที่เราต้องการ…

ดังนั้นเราก็ต้องมีความรู้เพิ่มว่า เราสามารถใช้ SUMX มาช่วยในกรณีนี้ได้ ซึ่งมี Syntax ดังนี้

SUMX ( <Table>, <Expression> )

โดยเจ้า SUMX จะมองเข้าไปในแต่ละแถวของ <Table> ที่เราระบุ จากนั้น ในแต่ละแถวมันจะคำนวณตาม <Expression> ที่เราระบุ แล้วนำค่า Expression ของทุกแถวที่คำนวณได้มาหาผลรวมอีกที ซึ่งใน <Expression> ของ SUMX นี่แหละที่มี Row Context ตอนมันวน Loop เพื่อคำนวณแต่ละแถวใน <Table> ครับ ดังนั้นใน Expression เราจึงสามารถอ้างคอลัมน์คูณกันโดยได้ค่าของแต่ละแถวนั้นๆ ออกมาได้เลย แม้จะเขียนใน Measure ก็ตาม

Total Revenue SUMX = SUMX(OrderDetail,OrderDetail[Quantity]*OrderDetail[Unit Price])

หรือแม้แต่ตอนที่เราทำการคัดกรองข้อมูลด้วยฟังก์ชัน FILTER มันก็มี Row Context ใน <FilterExpression> เช่นกัน

FILTER ( <Table>, <FilterExpression> )

สมมติผมสร้าง New Table ด้วยสูตร FILTER ก็สามารถเขียนสูตรแบบนี้ได้ (เอาสูตรยาวๆ ไปใส่ใน https://www.daxformatter.com/ จะได้จัดย่อหน้าได้สวยๆ ดูง่ายขึ้น)

RichMaleOrder = 
FILTER (
    OrderDetail,
    ( OrderDetail[Unit Price] * OrderDetail[Quantity] ) > 250000
        && RELATED ( Customer[Gender] ) = "M"
)

ซึ่ง FILTER จะมองเข้าไปในแต่ละแถวของตาราง OrderDetail แล้วดึงมาเฉพาะแถวที่ <FilterExpression> เป็นจริงเท่านั้น โดยความหมายในสูตรที่เขียนคือ ให้เอาเฉพาะแถวที่ Unit Price*Quantity ได้มากกว่า 250000 และ เพศลูกค้าเป็นผู้ชาย

ซึ่งใน <FilterExpression> นี่แหละที่มี Row Context เราจึงสามารถอ้างอิงคอลัมน์ในตาราง OrderDetail ได้เลยโดยตรง แต่ถ้าจะอ้างอิงตารางอื่น เราจะต้องใช้ RELATED มาช่วย

Filter Context ขั้นกลาง

สำหรับลูกเล่นในระดับกลางของการเล่นกับ Filter Context ก็คือ การใช้ CALCULATE เพื่อทำการเปลี่ยนแปลง Filter Context นั่นเอง

CALCULATE ( <Expression> , [<Filter1>] , [<Filter2>],... )

ความสามารถของ CALCULATE โดยหลักการคือสามารถเปลี่ยน Filter Context ได้ก่อนจะคำนวณ Expression ที่ระบุ

ตัวอย่างง่ายๆ เลยของการใช้ CALCUCATE เพื่อเปลี่ยน Filter Context คือแบบนี้

Total Revenue Asia = CALCULATE([Total Revenue],Store[Store Continent]="Asia")

ความหมายคือ ก่อนจะคำนวณ [Total Revenue] ออกมา ให้ทำการเปลี่ยน Filter ของ Store[Store Continent] ให้เป็น Asia ซะ

ซึ่งจากผลลัพธ์ข้างบนจะเห็นว่า Filter อื่นๆ เช่น Brand จะยังคงทำงานอยู่ตามปกติ แต่ Filter เรื่อง Store[Store Continent] ที่เดิมเคยเป็นทวีปอื่น เช่น Europe ก็ได้ถูกเปลี่ยนให้กลายเป็น Asia ไปซะแล้ว ค่าก็เลยเปลี่ยนจากปกติที่ควรจะเป็น 159,023,205 ไปเป็น 163,171,265 ซึ่งเป็นค่าของทวีป Asia นั่นเอง

แล้วมีอะไรอีก?

ซึ่งจริงๆ แล้ว CALCULATE สามารถเปลี่ยน Filter Context ได้หลากหลายแบบมาก เช่น

• ปลด Filter ด้วย ALL, REMOVEFILTERS, ALLSELECTED, ALLEXCEPT
• การใส่ Filter คืนกลับ (หลังปลด) ด้วย DISTINCT, VALUE
• การใช้งานร่วมกับฟังก์ชันกลุ่ม Time Intelligence
• ปรับเปลี่ยนวิธีทำงานของ Relationship ก่อนจะ Filter ก็ได้ เช่น USERELATIONSHIP, CROSSFILTER
• ทำการเปลี่ยน Row Context ให้กลายเป็น Filter Context ด้วยเทคนิคที่เรียกว่า Context Transition
• การนำ Context Transition ไปใช้ใน Iterator เช่น SUMX, MAXX, MINX, CONCATENATEX, RANKX
• การสร้างและใช้ตารางจำลองเพื่อเป็นเงื่อนไขใน CALCULATE
• CALCULATETABLE คู่แฝดของ CALCULATE
• การทำงานของ Expanded Table

เรื่องพวกนี้ก็จะเป็นเรื่องที่เป็นเนื้อหาขั้นสูงขึ้นอีก ซึ่งในบทความนี้ผมจะขอแนะนำแค่ Context Transition ก่อนแล้วกันครับ

Context Transition คืออะไร?

กลับมาที่ผมบอกว่าในตาราง Data นั้นไม่มี Filter Context มีแต่ Row Context เวลาเราเพิ่มคอลัมน์ใหม่แล้ว SUM เลยได้ค่าเดียวกันหมด เช่น รูปเดิมของเราอันนี้

แต่ถ้าเราใช้ CALCULATE ไปครอบสูตรอันเดิมของเราซะ ผลลัพธ์จะกลายเป็น Total Revenue ของแต่ละ Product ได้อย่างถูกต้องเฉยเลย

Total Revenue Context = CALCULATE(SUM(OrderDetail[Revenue]))

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

แล้ว Filter นั้นก็จะส่งผลไปที่ตาราง OrderDetail จนทำให้เกิดผลรวมของแต่ละ Product นั้นๆ ได้อย่างถูกต้อง!

ที่มันเจ๋งกว่านั้นอีก คือ ถ้าเราใส่สูตรโดยอ้างอิง Measure โดยตรงไปเลย มันจะได้ค่าแบบเดียวกับที่มี CALCULATE ครอบเลย

Total Revenue Context = [Total Revenue]

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

แต่ถ้าไม่รู้จะหาความรู้จากไหน ผมมีคอร์สสอนเรื่องนี้อยู่แล้วครับ 2 คอร์ส คือ Power BI Basic กับ คอร์ส Advance ที่เน้น DAX แบบเน้นๆ เลย (ขอปิดท้ายด้วยการขายของหน่อย 555)

คอร์สออนไลน์ Powerful Data with Power BI การวิเคราะห์และนำเสนอข้อมูลขั้นเทพ

• คุณจะได้เรียนรู้เครื่องมือ Power BI ซึ่งเป็นเครื่องมือ Business Intelligent ชั้นยอดจากค่าย Microsoft
• ใช้ทำ Interactive Dashboard ที่มี Visual ที่เจ๋งกว่า Excel หลายเท่า
• มีความสามารถในการแชร์ Dashboard ไปให้คนอื่นใช้ได้ง่าย/ปลอดภัย
  ในคอร์สนี้คุณจะได้เรียนรู้ทั้ง 3 แกนหลักของโปรแกรมนี้ ทั้ง
  • การ Get/Transform Data ด้วย Query Editor (เนื้อหาน้อยกว่าในคอร์ส Power Query)
  • การทำ Data Model และเขียนสูตร DAX ในระดับที่ใช้งานทั่วไป
    • เรียนรู้ Data Model แบบพื้นฐาน
    • รองรับการทำ Data Actual vs Budget ได้
    • สูตร DAX จะสอนนี้ก็จะลงลึกถึง CALCULATE และ Time Intelligent
  • การสร้าง Report ด้วยการสร้างกราฟและปรับแต่ง Visual ต่างๆ รวมถึงการทำให้เกิดความ Interactive มากขึ้น
• ราคา 2,290 บาท (อัปเดทเนื้อหาให้ฟรีทุกปี)

Power BI DAX Advance

เพื่อให้สามารถเขียนสูตร DAX ที่มีความซับซ้อนมากขึ้น เพื่อตอบโจทย์ทางธุรกิจได้ตรงจุดมากขึ้น
สามารถต่อยอดความรู้ด้วยการอ่านเนื้อหายากๆ จากเว็บ https://www.daxpatterns.com/ ได้
เน้นเรื่องการเขียน DAX โดยเฉพาะ โดยมีทฤษฎีขั้นสูงมากมาย เช่น

• Context Transition
• Date Table & Time Intelligence
• DAX กับการปรับ Data Model
• Data Lineage และ TREATAS
• Concept ของ Expanded Table
• การสร้าง Virtual Table
• การใช้ DAX Studio เบื้องต้น / Calculation Group

มีตัวอย่างเคสมากมาย เช่น

• ABC Analysis
• การคำนวณต้นทุนแบบ FIFO
• Same Store Sales Growth
• แบ่ง Segment ด้วย RFM Analysis
• Semi-Additive ยอด Balance

ราคา 2,490 บาท (อัปเดทเนื้อหาให้ฟรีทุกปี)