สรุปเรื่องของ DAX ฉบับเทพเอ็กเซล

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

ลักษณะของบทความนี้จะเป็นการที่ผมนั่งทด นั่งคุยกับตัวเองถึงประเด็นสำคัญๆ และประเด็นที่ต้องระวัง ดังนั้นมันอาจจะกระโดดไปมาบ้าง ยากบ้าง ง่ายบ้าง แต่หวังว่ามันจะมีประโยชน์กับเพื่อนๆ ที่แวะมาดูนะครับ อีกอย่างผมจะพยายามมาปรับปรุงบทความนี้อยู่เรื่อยๆ ตามความรู้ที่ผมมี ดังนั้นแวะมาอ่านบ่อยๆ ได้เลยครับ

ผลลัพธ์ของสูตร DAX

ผลลัพธ์ของ DAX มีทั้งออกมาเป็นค่าเดียว (Scalar) และออกมาเป็นตาราง (Table)

• ถ้าเขียน New Measure ผลลัพธ์สุดท้ายต้องเป็น Scalar
• ถ้า Add New Column ผลลัพธ์ในแต่ละบรรทัดเป็น Scalar
• ถ้า Add New Table ผลลัพธ์ต้องเป็น Table
• อย่างไรก็ตาม รายละเอียดในส่วนประกอบปลีกย่อยจะเป็นอะไรก็ได้ และประกาศตัวแปรด้วย VAR ได้ทุกประเภท

หมายเหตุ : Table ที่มีแค่ 1 Column และ 1 Row ตัว DAX จะสามารถแปลงเป็น Scalar ให้โดยอัตโนมัติ (มองได้ 2 แบบ)

tips : การสร้าง New Col ใน DAX ได้ผลเร็วกว่าสร้างใน Power Query แต่จะทำให้ขนาด Model ใหญ่ขึ้น (เพราะไม่สามารถ optimize ได้เต็มที่)

การอ้างอิงสูตร DAX

• อ้างอิงตาราง = TableName
• อ้างอิงคอลัมน์ =TableName[ColumnName]
• อ้างอิง Measure = [MeasureName] (ไม่ต้องมีชื่อตาราง จะได้ไม่สับสนกับคอลัมน์)

Tips : แนะนำว่าถ้าสร้างคอลัมน์จำลองในสูตร ควรตั้งชื่อคอลัมน์ด้วย “@ColumnName” เพื่อให้อ้างอิงด้วย [@ColumnName] จะได้ไม่สับสนกับ Measure

Evaluation Context คือ หัวใจของ DAX

การใช้ DAX นั้นต่างจากสูตร Excel ตรงที่เราต้องพิจารณาถึง Evaluation Context อยู่ตลอดเวลา ซึ่งการใช้สูตรแต่ละที่ก็จะมีบริษทที่แตกต่างกัน ซึ่งบริบทที่ว่ามีอยู่ทั้งหมด 2 แบบคือ Filter Context และ Row Context

Filter Context = ณ จุดนั้นมีการ Filter อะไรอยู่บ้าง?

• ในตาราง Data : ปกติจะไม่มีการ Filter ดังนั้นถ้า Add Column แล้วเขียนว่า =SUM(TableName[ColumnName]) จะได้ค่าเท่ากันหมดทุกบรรทัด (เพราะไม่มีการ Filter นั่นเอง)

• ใน Report : อาจจะมีการ Filter ตาม Category/Axis หรือ Filter ที่ใส่ลงไป หรือแม้กระทั่ง Filter จาก Interaction ในรายงาน และนั่นคือสาเหตุที่เขียนสูตร Measure ว่า =SUM(TableName[ColumnName]) เหมือนกัน แต่ในแต่ละ Category จะได้ค่าไม่เท่ากัน (เพราะ Filter ไม่เหมือนกัน)

Row Context = การวน Loop เพื่อพิจารณาทีละแถวของตาราง

• ในตาราง Data : การเขียนสูตรเพื่อวน Loop ในแต่ละบรรทัดของตารางจะมี Row Context เพื่อพิจารณาทีละแถว เช่น New Column, หรือสูตรพวก Iterator อย่าง SUMX, FILTER, ADDCOLUMNS ดังนั้นการเขียนสูตรว่า =TableName[ColumnName] จึงมองเห็นแค่บรรทัดนั้นๆ ของคอลัมน์ที่ระบุเท่านั้น
• ใน Report : ปกติจะไม่มี Row Context ดังนั้นจะเขียน Measure ลง Report ว่า =TableName[ColumnName] ไม่ได้ เพราะมันไม่ได้ดูทีละแถว

การจะตรวจสอบ Level ของ Measure

เนื่องจากในรายงาน ไม่มี Row Context ดังนั้น หากเราต้องการตรวจสอบว่า Measure กำลังทำงานอยู่ Category ที่ไหน ระดับไหน

• ISINSCOPE / ISFILTERED เพื่อเช็คว่าอยู่ Level ไหน แต่ ISINSCOPE จะตรวจสอบ Level ได้ชัดเจนกว่า (ISFILTERD จะได้รับผลมาจาก Visual อื่นได้ ทำให้สับสน)
• SELECTEDVALUE เพื่อตรวจสอบว่ากำลังอยู่แกนชื่อว่าอะไร (ถ้า SELECTEDVALUE มองเห็นคอลัมน์ที่ระบุแค่ค่าเดียว จะได้ค่านั้นกลับมา)

FILTER

• เป็น Table Function ที่คัดกรองผลลัพธ์ให้เหลือแถวน้อยลง โดยมองเห็นคอลัมน์ในตารางต้นฉบับ (Row Context ของตารางต้นฉบับ)
• เงื่อยไขใส่ได้ตัวเดียว แต่ใช้ AND, OR ช่วยได้ (แต่ได้คู่เดียว)
• ใช้ && แทน AND จะยืดหยุ่นกว่า
• ใช้ II แทน OR จะยืดหยุ่นกว่า

Product[Color] IN {"Red","Blue","Yellow"}

มีค่าเท่ากับ

CONTAINSROW ( {"Red","Blue","Yellow"} , Product[Color] )

ALL vs VALUES vs DISTINCT

กรณีอ้างอิงไปที่คอลัมน์เดียว

• ALL ได้ผลลัพธ์แบบไม่ซ้ำ แบบมี Blank Row พิเศษได้ และ ปลด Filter ด้วย
• VALUES ได้ผลลัพธ์แบบไม่ซ้ำ แบบมี Blank Row พิเศษได้ (ไม่ปลด Filter)
• DISTINCT ได้ผลลัพธ์แบบไม่ซ้ำ (ไม่มี Blank Row พิเศษ ไม่ปลด Filter)

Blank Row พิเศษเกิดขึ้นในฝั่งตารางที่เป็นเลข 1 เวลาจับคู่กับอีกตารางผ่าน Relationship ไม่ได้ (ในอีกตารางฝั่ง * มีค่าบางอย่างที่ตารางฝั่งเลข 1 ไม่มี) เลยสร้าง Blank Row มาจับคู่แทน (แต่จะสร้าง Blank Row ขึ้นมาตัวเดียว แม้จะจับไม่ได้หลายคู่)

CALCULATE / CALCULATETABLE

• เป็นฟังก์ชันพวกเดียวที่สามารถเปลี่ยนแปลง Filter Context ได้
• Filter Argument ของ CALCULATE จริงๆ แล้วถูกมองเป็น Table เสมอ
  เช่น การเขียนเป็นเงื่อนไข (เรียกว่า predicate) ว่า

Product[Brand]="Contoso" 

มีค่าเทียบเท่ากับ

FILTER( ALL( Product[Brand] ), Product[Brand]="Contoso" )

• คอลัมน์ที่จะอ้างถึง ต้องมีจริงใน Data Model เท่านั้น (ถ้าไม่มีจริง ต้องเขียนด้วยการใช้ FILTER มาช่วย)
• แต่ถ้าใช้ KEEPFILTERS ครอบจะไม่มีการปลด Filter เดิมออกเลย
• การใช้ ALL, REMOVEFILTERS ใน argument ของ CALCULATE แบบตรงๆ จะทำหน้าที่เป็น Calculate Modifier ซึ่งทำหน้าที่ปลด Filter (ไม่ได้ทำหน้าที่เป็น Table Function ที่สร้างตารางผลลัพธ์ที่ไม่ซ้ำเหมือนปกติ)
• เวลาเขียน CALCULATE ซ้อนกัน วิธีคิดจะแปลก เพราะ เนื่องจากการใช้ฟังก์ชันซ้อนกัน ปกติจะคิดตัวในก่อน ซึ่งพอไปมองไปที่ CALCULATE ตัวใน CALCULATE จะต้องตีความ Filter Context ที่มีผลต่อมันก่อน (ซึ่งมาจาก CALCULATE ตัวนอก) ดังนั้น ผล Filter ของ CALCULATE ตัวนอกจึงถูกคิดก่อน แล้วค่อยมาคิด Condition ตัวในทีหลัง (ซึ่งมันอาจจะทับตัวนอกได้)

Context Transition

• CALCULATE/CALCULATETABLE สามารถเปลี่ยน Row Context ให้กลายเป็น Filter Context ได้ (แต่ต้องระวังบรรทัดที่ข้อมูลเบิ้ล ผลลัพธ์จะผิด)
• เวลาอ้างอิง Measure จะมี CALCULATE ครอบอยู่เสมอ ทำให้เกิด Context Transition เสมอ

• Context Transition เกิดขึ้นก่อน Calculate Modifier แปลว่า เราสามารถใช้ ALL ปลดผลจาก Context Transition ได้ (ถ้าเราใส่ ALL เพิ่มเข้าไป Engine จะฉลาดและรู้ว่าไม่ต้องทำ Context Transition แล้ว)
• เวลาเกิด Context Transition หลายคอลัมน์ ระวังเรื่อง Circular Dependency เพราะมันจะอ้างอิงกันเอง ซึ่งแก้ไขได้โดยให้ในตารางมีคอลัมน์ที่มี Unique Value อยู่และทำให้ Engine รู้ นั่นคือ ทำให้อยู่ฝั่งเลข 1 ของ Relationship ซะ หรือ Mark as Date Table ก็ได้

CALCULATE Modifier

• USERELATIONSHIP เลือกเส้น Relationship ที่จะ Active
• CROSSFILTER เลือกวิธีการไหลของ Relationship
• ALL ทั้งหลาย และ REMOVEFILTERS ใช้ปลด Filter

ลำดับการคำนวณของ CALCULATE

1. พิจารณาเงื่อนไขใน filter argument ของ calculate เอง (และ row context) ภายใต้ context original แล้วจำไว้ก่อน
2. ทำการแปลง row context (ถ้ามี) เป็น filter context แล้วเอาไปรวมกับ filter argument ที่จำไว้
3. พิจารณา filter context ดั้งเดิมทั้งหมด
4. ทำ Calculate modifier เช่น ALL, REMOVEFILTERS, USERELATIONSHIP, CROSSFILTER ซึ่งจะดัดแปลง context เดิมได้
5. ทำตาม filter argument ที่จำเอาไว้ทั้งหมด ซึ่งอาจเปลี่ยนแปลง context เดิมได้ สุดท้ายกลายเป็น filter context ใหม่
6. คำนวณ expression ออกมาภายใต้ filter context ใหม่

เทคนิคพิเศษกับ CALCULATE

• เราสามารถปลด Filter ออกด้วย ALL() ก่อน แล้วใส่ Filter กับเข้าไปใหม่ด้วย VALUES หรือจะใช้ ALLEXCEPT ก็ได้
• ALLEXCEPT คือการปลดออก ไม่ได้มีการใส่อะไรเข้าไปแทน
• ALL + VALUES คือ ปลดออก + ใส่กลับ

การอ้างอิงคอลัมน์ Date ใน Date Table เป็นการปลด Filter ทั้งตาราง Date (ปกติต้องอ้างคอลัมน์นั้นตรงๆ ถึงจะปลดได้ แต่กับคอลัมน์ Date จะเป็นกรณีพิเศษ ซึ่งหลักการนี้ใช้กับ Time Intelligent ด้วย)

VAR

• VAR ถูกคำนวณค่าครั้งเดียวใน Scope ของ VAR นั้นๆ แล้วเก็บผลลัพธ์ไว้เป็น Constant (แปลว่าเปลี่ยนด้วย CALCULATE ไม่ได้)
• VAR หลายทีได้ อ้างอิงตัวก่อนหน้าได้
• VAR ซ้อนกันหลายชั้นได้ เช่น

test = 
VAR aaa = 1
VAR bbb = 2 * ( 
                  VAR xxx = 3
                  VAR yyy = 4

                  RETURN xxx+yyy-aaa  // ในนี้มองเห็น aaa
                )
VAR ccc = 5
RETURN aaa/bbb+ccc  // ในนี้มองไม่เห็น xxx

• เรามักใช้ VAR เก็บค่าที่ต้องการเอาไว้ ซึ่งจะเก็บ Scalar หรือ Table ก็ได้
• VAR ช่วยให้อ่านสูตรง่ายขึ้น แบ่งสูตรเป็นขั้นเป็นตอน

RANKX

RANKX ( <Table>, <Expression> ,[<Value>] ,[<Order>] ,[<Ties>] )

1. RANKX จะสร้าง LookupTable จาก <Table> แล้ว ประเมินค่า <Expression> ใน Row Context ของตาราง LookupTable
2. จะประเมินค่า <Value> ใน Filter Context ปัจจุบัน ไปเทียบอันดับใน Lookup Table
3. ถ้าไม่ได้ระบุ <Value> จะเอา <Expression> ใน Filter Context ปัจจุบัน ไปเทียบอันดับใน Lookup Table แทน

Tips : เวลาใช้ใน Measure อย่าลืมปลด Filter ของ <Table> ด้วย เดี๋ยวจะมองไม่เห็นค่าใน Category อื่น

Time Intelligence

• อย่าลืมสร้าง Date Table และ Mark as Date Table ด้วย เพื่อให้การปลด Filter ที่คอลัมน์ Date จะเทียบเท่ากับการปลดทั้ง Date Table เสมอ Time Intelligent จึงจะทำงานถูกต้อง
• Time Intelligence ด้วยหลักการ คือ การเปลี่ยนช่วงเวลาของ Filter Context (เอาไว้ใช้ใน CALCULATE)
• การอ้างถึงคอลัมน์ Date ใน DateTable ของฟังก์ชัน Time Intelligent จริงๆ แล้วคือการย่อของการอ้างอิงถึงตาราง ดังนี้

DATESYTD( dDate[Date] )

มีค่าเท่ากับ

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

• สังเกตว่ามี CALCULATETABLE แปลว่าเกิด Context Transition ได้
• และการที่มันรับข้อมูลเป็นตาราง แปลว่าเราซ้อน Time Intelligent หลายตัวผสมกันได้ (เพราะผลของ Time Intelligent คือตารางที่มีคอลัมน์วันที่)

ฟังก์ชัน Time Intelligence

• DATESYTD = แก้ช่วงเวลาให้เริ่มตั้งแต่ วันแรกของปีเดียวกับปีสุดท้ายของ Filter Context ถึง วันสุดท้ายของ Filter Context
  (วันแรกของปี คือวันถัดจากวันสุดท้ายของปีงบประมาณ ซึ่งจะมีปัญหาถ้าสิ้นปีงบประมาณที่สิ้นกุมภา)
• DATEADD =
  • เลื่อนเวลาไปโดยกำหนดช่วงเวลาและหน่วยได้อิสระ (ยืดหยุ่นกว่า SAMEPERIODLASTYEAR)
  • ผลลัพธ์คือช่วงวันที่ที่มีอยู่ในตารางวันที่ (มันมองไม่เห็นวันที่อยู่นอกช่วง)
  • ถ้าเลื่อนไปแล้ววันเกิน จะเอาวันสิ้นเดือนมาแทน
  • ถ้าต้นฉบับมี 2 วันสุดท้ายของเดือน วันที่เลื่อนไปจะได้วันเหมือนต้นฉบับจนถึงวันสิ้นเดือนด้วย
• DATESBETWEEN = ได้ช่วงวันที่ระหว่างวันที่กำหนด
• DATESINPERIOD = ได้ช่วงวันที่ตั้งแต่วันที่หนด ด้วยระยะเวลาที่กำหนด
• PARALLELPERIOD = ได้ช่วงที่ครบ Period ที่ระบุตั้งแต่ต้นจบจบ
• FIRSTDATE / LASTDATE = เหมือน MIN/MAX แต่ว่าได้ผลเป็นตารางที่มี 1 วัน และมี Context Transition ด้วย

ต้องระวัง Time Intelligent ว่า ปกติมันจะ ปลด Filter ทั้ง Date Table ตอนที่เปลี่ยนช่วงเวลา ทำให้บางครั้งผลลัพธ์ก็อาจจะผิด เช่น มีการ Filter วันประจำสัปดาห์ด้วย แบบนี้ก็จะผิด เพราะของปีก่อนหน้าก็ไม่ใช่วันจันทร์แล้ว และ YTD ก็ไม่ได้มีแต่วันจันทร์ (แค่ต้นปีจนถึงจันทร์สุดท้ายเฉยๆ)

Date Lineage และ TREATAS

• เมื่อค่าใดๆ อ้างถึง Column ที่มีอยู่จริง มันจะได้ Data Lineage ของคอลัมน์นั้นมาด้วย ดังนั้นการ Filter จะมีความหมายตามคอลัมน์นั้นจริงๆ
• แต่ถ้าเรามีการสร้างคอลัมน์ใหม่ขึ้นมา หรือเอาคอลัมน์เดิมมาคำนวณเพิ่ม Data Lineage จะหายไป มันจะมองเป็นคอลัมน์ที่ไม่ได้มีความหมายในเชิง Model อะไร
• ฟังก์ชันที่จะเปลี่ยน Data Lineage ได้ก็คือ TREATAS (เอาไปใช้ใน CALCULATE ได้ หรือจะเก็บใน VAR ก่อนใช้ก็ได้)

TREATAS ( <Expression>, <ColumnName> , [<ColumnName>],... )

TREATAS จะตามด้วย <Expression> ซึ่งคือ ตารางที่มีคอลัมน์ที่อาจยังไม่มี Data Lineage แล้วตามด้วยคอลัมน์ที่อยากจะไปดึง Data Lineage มาใช้ ซึ่งต้องระบุให้ครบทุกคอลัมน์ของตารางใน Expression ด้วย เช่น

TREATAS ( 
   {
       (2007,"December"),
       (2008,"February")
   }
   , 
   dDate[Year],
   dDate[MonthName],
   )  
// แปลว่าเอาให้ตารางที่มีค่าคำว่า Red กับ Blue ได้ Data Lineage เทียบเท่ากับเป็นคอลัมน์ Product[Color]

Advanced Table Function

• ADDCOLUMNS ใช้เพิ่มคอลัมน์ใหม่เข้าไปในตาราง
• SUMMARIZE สร้างตารางขึ้นมา โดยทำการ Scan ตารางที่ระบุ แล้ว Group Related Column ที่ระบุอีกที
  • ดีกว่าพวก ALL ตรงที่ SUMMARIZE จะเอาจากตารางเดียวกันหรือตารางอื่นก็ได้ เจ๋งมากๆ
  • แม้จะสร้างคอลัมน์คำนวณขึ้นมาใหม่ได้ แต่ไม่ควรทำ เพราะสร้างทั้ง Row Context และ Filter Context ขึ้นพร้อมกัน ให้ใช้ SUMMARIZE + ADDCOLUMNS ดีกว่า
• CROSSJOIN ใช้สร้าง Combination ทุกอันที่เป็นไปได้ของค่าใน Input Table
  • อาจใช้ CROSSJOIN + FILTER แทนการ SUMMARIZE Fact ที่จำนวนบรรทัดมากๆ
• UNION ใช้ Append ตารางเข้าด้วยกัน
  • ไม่ได้ตัดตัวซ้ำ ถ้าจะตัดก็ใช้ DISTINCT ครอบอีกที
  • จะได้ Data Lineage ก็ต่อเมื่อ ตารางต้นฉบับมี Data Lineage เดียวกันทั้งคู่
  • ถ้าไม่ได้ Data Lineage มา อาจใช้ TREATAS หรือ CALCULATE มาช่วยจัดการภายหลัง
• INTERSECT ได้ตัวซ้ำระหว่าง 2 ตาราง
  • ได้ Data Lineage ของตารางแรกมาเสมอ
• EXCEPT เอาตารางแรกตั้ง ลบออกด้วยตารางที่สอง
  • ได้ Data Lineage ของตารางแรกมาเสมอ
• SELECTCOLUMNS ใช้เลือกคอลัมน์ที่ต้องการ เปลี่ยนชื่อคอลัมน์ได้ (ไม่ได้ลดจำนวนแถวเหมือน SUMMARIZE)
  • ได้ Data Lineage ถ้าอ้างอิงคอลัมน์ตรงๆ ไม่ได้เป็น Expression
• TOPN ได้ N แถวแรกของตาราง เรียงตามที่กำหนด
  • ถ้ามีค่าเท่ากันหลายบรรทัด อาจได้แถวมากกว่า N ที่กำหนดได้
    (แก้โดยเพิ่มคอลัมน์เข้าไปใน Sort ของ TOPN จนได้บรรทัดที่ไม่ซ้ำกัน)
• GENERATE ทำการ Iterate เข้าไปในแต่ละแถวของตารางที่ระบุ (row context) แล้วเชื่อมกับผลลัพธ์ของ table expression
• GENERATEALL เหมือน Generate แต่ถ้า table expression เป็นค่าว่าง จะไม่ skip แต่จะปล่อยว่างไป

Expanded Table

• การอ้างอิง Table หมายถึง Expanded Table เสมอ
• ตาราง Expand ออกไปทางตารางที่เป็นเลข 1 (ไม่เกี่ยวกับ Filter Direction)
• เมื่อการ Filter ถูกทำไปที่คอลัมน์ที่กำหนด ทุก Expanded Table ที่มีคอลัมน์นั้นจะถูก Filter ทันที
• RELATED จริงๆ คือคำส่งที่ใช้เข้าถึงคอลัมน์ใน Expanded Table
• การ Filter ด้วยตาราง ต้องระวังว่ามันหมายถึง Expanded Table
  • การ Filter ด้วยเงื่อนไขคอลัมน์ กับ การระบุด้วยตาราง เป็นคนละเรื่องโดยสิ้นเชิง
  • ทำให้เวลาใช้ใน CALCULATE มันอาจจะมีผลกับ Filter บางอย่างที่เราอยากจะเก็บเอาไว้ก็ได้ เช่น
  • ปลด Filter ที่ Fact ก็คือการปลด Filter ทั้ง Model ที่ RELATED กับ Fact ได้เลย
  • หรือใส่ Filter ตาราง Fact จะได้ผลลัพธ์เฉพาะที่ RELATED กับ FACT เท่านั้น ดังนั้นลูกค้าที่ไม่ซื้อของจะหายไปเลย

• Context Transition ก็ทำงานกับทุกคอลัมน์ใน Expanded Table ด้วยเช่นกัน

Relationship

• ถ้าจะสร้าง Calculated Column เพื่อเชื่อม Relationship ให้ระวัง Circular Dependency
  • เลี่ยงปัญหาได้ด้วยการใช้ DISTINCT แทน VALUES, ALLNOBLANKROW แทน ALL
  • เลี่ยงการใช้ CALCULATE แบบย่อ (เพราะมี ALL แฝง), SELECTEDVALUE (มี VALUES แฝง)