แนวทางการใช้ DAX Studio ในการตรวจสอบและเรียนรู้ DAX

ปัญหาหนึ่งที่คนที่ใช้ Power BI มาแล้วซักพักจะต้องเจอก็คือการเรียนรู้เรื่องสูตร DAX ซึ่งเป็นภาษาที่มีความสามารถในการสร้างผลลัพธ์ที่ซับซ้อนได้เก่งมากๆ แต่ก็ค่อนข้างยากต่อการเรียนรู้และต่อยอด เนื่องด้วยประเด็นต่างๆ เหล่านี้

เหตุผลที่ทำให้ DAX ยาก

เหตุผล 1 : การเขียน DAX ขึ้นอยู่กับบริบท และ Data Model

DAX นั้นไม่เหมือนภาษาอื่นๆ ตรงที่การทำงานของมันขึ้นกับสิ่งแวดล้อมหรือบริบทที่เรียกว่า Evaluation Context อย่างเข้มข้น (ทั้ง Filter Context และ Row Context) ดังนั้นมันจึงมีความเกี่ยวข้องกับ Data Model อย่างเข้มข้นด้วย

สมมติว่าเราไปถามสูตร DAX คนอื่น ก็จะเป็นการยากที่ผู้รู้หรือแม้แต่ ChatGPT จะตอบให้เราได้ถูกต้องแบบง่ายๆ เพราะว่าเราต้องอธิบายตัวข้อมูลและ Data Model ของเราพอสมควร ผู้รู้ถึงจะตอบคำถามของเราได้

ไม่เหมือนสูตร Excel ที่รู้แค่ที่อยู่ของข้อมูลว่าเก็บอยู่ใน Range ไหนหรือ Table ไหนคอลัมน์ไหนก็เพียงพอแล้ว

Solution ที่แนะนำ

ณ ตอนนี้ ถ้าจะถามคำถามเรื่อง Power BI กับคนอื่น ควรจะให้ข้อมูลที่สำคัญให้ครบ เช่น

• อยากทำอะไรได้ ทำไปทำไม
• หน้าตาค่าของผลลัพธ์ที่อยากได้ วิธีคำนวณสิ่งที่อยากได้ ถ้าทำ Manual เอง
• หน้าตาของข้อมูลที่เกี่ยวข้อง
• หน้าตาของ Data Model
• จะเอาสูตรไปใช้ในไหน ใน Measure หรือ Column หรือ Table
• ถ้าเป็น Measure จะเอาไปใช้ใน Visual อะไร มี Filter อะไรทำงานอยู่ หรือจะเกิดการ Filter อะไรขึ้นได้บ้าง

เห็นมั้ยครับว่ามันยาก เพราะว่า “การที่จะได้คำตอบที่สมบูรณ์ คำถามก็ต้องสมบูรณ์ด้วย”

แต่ในอนาคตอันใกล้นี้ เราจะมีแนวทางใหม่ที่ง่ายขึ้นเยอะ นั่นคือ การสอบถามสูตร DAX ที่น่าจะสะดวกสุด คือ “ถาม AI ที่อยู่ในโปรแกรมเลย” เช่น

• ใน Excel จะมี 365 Copilot ที่อยู่ใน Excel (กำลังมา)
• ใน Power BI จะมี Quick Measure Suggestion (ตอนนี้มีแล้ว แต่ว่าไม่ค่อยฉลาด ทำได้แค่ Measure พื้นๆ)
• และ Power BI Copilot (กำลังมา ไม่แน่ใจว่าจะฉลาดแค่ไหน)

ซึ่งสะดวกที่มันมองเห็น Data เราอยู่แล้วจึงไม่ต้องอธิบายมาก แต่เราอย่าเพิ่งเชื่อมัน 100% ทันที เพราะเราต้องเอาสิ่งที่มันตอบมาทดสอบด้วย และ เราเองก็ต้องเข้าใจสูตรที่ AI จะ Generate ออกมาให้ด้วยเช่นกัน เพราะ สุดท้ายเราเองก็ยังต้องเป็นคนรับผิดชอบในงานนั้นอยู่ดี

นั่นคือ แม้ในอนาคต AI จะเขียนสูตรหรือสร้างรายงานให้เราได้ เราก็ต้องทำเองเป็นอยู่ดี (แค่อาจไม่ต้องทำเองแต่แรก)

เหตุผล 2 : การเขียน DAX นั้นตรวจสอบการทำงานยาก

DAX นั้นเป็นสูตรที่สามารถเขียนแบบซับซ้อนมากๆ ได้ แต่ยิ่งเขียนซับซ้อนมากเท่าไหร่ ก็จะยิ่งตรวจสอบผลลัพธ์ได้ยากขึ้นด้วยเช่นกัน

แม้ว่าเราจะสามารถประกาศตัวแปรด้วยการใช้ VAR และ RETURN มาช่วยแล้ว แต่ผลลัพธ์ในบางขั้นตอนนั้นก็ไม่สามารถเอาออกมาได้ตรงๆ เช่น

สมมติว่าเราเขียน DAX Measure แต่ถ้าผลลัพธ์ใน VAR ขั้นตอนกลางๆ เป็นตาราง ก็เอาออกมาดูตรงๆ ใน Measure ไม่ได้อีก

Solution ที่แนะนำ

วิธีที่ Work ที่สุดในการตรวจสอบการทำงานของสูตร DAX ก็คือ การใช้เครื่องมือเสริมที่เรียกว่า DAX Studio นั่นเอง

นอกจากที่มันช่วย Export ข้อมูลเยอะๆ (เกินล้าน) ออกจาก Data Model ได้แล้ว มันยังช่วยเขียนหรือตรวจสอบสูตร DAX ได้ด้วย

ซึ่งนี่คือเหตุผลหลักที่ผมเขียนบทความนี้ขึ้นมา นั่นก็คือเพื่อ อธิบายแนวทางการใช้ DAX Studio ในการช่วยเขียนและตรวจสอบสูตร DAX นั่นเอง

วิธีการเรียกใช้ DAX Studio

หลังจากที่ Install DAX Studio เรียบร้อยแล้ว ให้เราเปิดไฟล์ PBIX ของเราตามปกติ แล้วไปที่ External Tool แล้วจะมี Icon DAX Studio ใน Ribbon ก็กดเข้าไปได้เลย มันจะเชื่อมกับ Data Model ของไฟล์เราโดยอัตโนมัติ และมองเห็นข้อมูลและ Measure ทั้งหมด

สิ่งที่เราจะต้องทำใน DAX Studio คือ สร้าง DAX Query ขึ้นมา เพื่อให้ได้คำตอบหรือผลลัพธ์ที่เราอยากรู้ ซึ่งมีคู่มือการใช้งานการสร้าง Query หลักๆ ที่นี่ แต่ถ้าขรเดี๋ยวผมจะแนะนำให้เอง

Syntax หลักของ Query ใน DAX Studio

เวลาใช้ DAX Studio โครงสร้างของ Query หลักๆ คือ แบบนี้

EVALUATE <table expression>

ซึ่ง table expression แปลว่าสูตรอะไรก็ได้ที่ “ให้ผลลัพธ์เป็นตาราง” นั่นคือใส่ชื่อตารางเข้าไปตรงๆ เลยก็ยังได้ เช่น

EVALUATE ProductMaster

เราจะได้ผลลัพธ์เรียงตาม Data ใน Data Model

หรือเราจะใส่สูตรอะไรก็ได้ ที่ให้ผลเป็นตาราง ซึ่งก็คือพวก Table Function ทั้งหลายนั่นเอง เช่น DISTINCT, ALL, FILTER, CALCULATETABLE, SUMMARIZE, CROSSJOIN, ADDCOLUMNS และอีกมากมาย

เช่น

EVALUATE ALL(ProductMaster[Brand],ProductMaster[Color])

ใช้ Query Builder ก็ได้

แต่ถ้าเรายังเขียนสูตรไม่เก่ง หรือ ขี้เกียจเขียน เราก็ไม่จำเป็นต้องเขียน Query เอง เพราะมันมี Query Builder ให้ใช้ โดยที่มันจะ Gen Query ให้เราได้

เพียงแค่ ราสามารถลากเข้าไปได้ว่าตาราง Query เรา มีผลลัพธ์เป็นคอลัมน์อะไรบ้าง มีการ Filter ด้วยอะไร และเรียงตามอะไร??

/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
    ProductMaster[Category],
    ProductMaster[Class],
    "Total Revenue", [Total Revenue]
)
ORDER BY 
    ProductMaster[Category] ASC,
    ProductMaster[Class] ASC
/* END QUERY BUILDER */

วิธีแสดงค่า Measure ที่เป็น scalar ออกมา

ถ้าเราลองใส่ Measure เข้าไปใน Query Builder เฉยๆ มันจะ Gen ให้ออกมาแบบนี้ ซึ่งเป็นหนึ่งในวิธีได้ผลลัพธ์ออกมาเป็นตารางได้ แม้จะแสดงค่าใน Measure ก็ตาม

/* START QUERY BUILDER */
EVALUATE
CALCULATETABLE(
    ROW(
    "Total Revenue", [Total Revenue],
    "Total Qty", [Total Qty]
    )
)
/* END QUERY BUILDER */

แต่ถ้าเราต้องการแสดง Measure ตัวเดียว วิธีที่ Simple ที่สุดคือใช้ { } ซึ่งเป็น Table Constructor มาช่วย เช่น

EVALUATE {[MeasureName]}

เช่น

EVALUATE {[Total Revenue]}

ดู History ย้อนหลัง

นอกจากนี้เรายังกดดู History ย้อนหลังของ Query ที่เราทำทั้งหมดได้ ไม่ว่าจะพิมพ์เองหรือใช้ Query Builder ก็ตาม โดย Double Click ที่ Query ที่ต้องการเพื่อเอากลับมาใช้ใหม่ได้

แล้วมันใช้ตรวจสอบสูตร DAX ยังไง?

สมมติผมเขียนสูตร DAX ขึ้นมาแล้วผมไม่แน่ใจว่ามันทำงานถูกต้องหรือไม่ ผมสามารถใช้ DAX Studio มาช่วยตรวจสอบได้

ยกตัวอย่างเช่น

ผมต้องการจะคำนวณว่า มีกี่ Brand ที่สามารถขายของได้เกิน 10000 ชิ้นทุกปี แล้ว brand เหล่านั้นสร้างยอดขายได้ทั้งหมดกี่บาท??

โดยแนวคิดคือ ผมจะ

• List รายชื่อ Brand ที่ขายได้เกิน 10000 ชิ้นในแต่ละปีออกมาก่อน เรียกว่า BigBrand
• List รายชื่อ Brand ที่ขายได้ไม่เกิน 10000 ชิ้นในแต่ละปี เรียกว่า NotBigBrand
• เอารายชื่อ BigBrand ตั้งแล้วหักที่มีใน NotBigBrand ออก เราก็จะได้เหลือ Brand ที่ผ่านเกณฑ์เท่านั้น

สมมติว่าแรกสุดผมเขียนสูตร Measure ไปแบบนี้ (ซึ่งผิด)

Total Brand buy Gt10000 every year = 
VAR BrandandYear=SUMMARIZE(OrderDetail,dDate[Year],ProductMaster[Brand])
    
VAR BigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]>10000))

VAR NotBigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]<=10000))

VAR BigBrandEveryYear=EXCEPT(BigBrand,NotBigBrand)
VAR CalRev=CALCULATE([Total Revenue],BigBrandEveryYear)
RETURN COUNTROWS(BigBrandEveryYear)
//RETURN CalRev

ที่ผิดเพราะมันกลับให้ผลลัพธ์รายชื่อ Brand ในแต่ละปีไม่เท่ากัน (ตามหลักต้องนับได้จำนวนเท่ากัน)

ที่นี้ผมต้องการเอาไปทดสอบใน DAX Studio ผมสามารถทำได้ดังนี้ โดยผมอยากตรวจสอบที่ปี 2022 ด้วยว่าทำไมมันถึงได้ 10

นอกจากนี้ ก็ลองเอา Measure ที่เราทำมาใช้ใน DAX Studio ดูซึ่งมันใช้ KEEPFILTERS มาช่วยจัดการเรื่อง Filter ให้เลย สะดวกดี

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

ที่นี้มันด่าเรา เนื่องจากว่าเราดันให้ผลลัพธ์ของ Measure เป็นตาราง มันเลยทำไม่ได้

ผมเลยต้องพลิกแพลงเล็กน้อย คือ ให้ Copy สูตรใน Measure ออกมา แล้วเอามาทับในส่วนสูตร ROW ที่มัน Gen ให้ (เพราะสิ่งที่เรา copy มาคือตาราง)

/* START QUERY BUILDER */
EVALUATE
CALCULATETABLE(

VAR BrandandYear=SUMMARIZE(OrderDetail,dDate[Year],ProductMaster[Brand])
    
VAR BigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]>10000))

VAR NotBigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]<=10000))

VAR BigBrandEveryYear=EXCEPT(BigBrand,NotBigBrand)
VAR CalRev=CALCULATE([Total Revenue],BigBrandEveryYear)
//RETURN COUNTROWS(BigBrandEveryYear)
//RETURN CalRev
RETURN BigBrandEveryYear
    
    ,
    KEEPFILTERS( TREATAS( {2022}, dDate[Year] ))
)
/* END QUERY BUILDER */

คราวนี้มันจะ Run ออกมาดูได้แล้ว และยังมีผลการ Filter ปี 2022 อยู่ด้วย

ทีนี้ผมก็สามารถลองเอา BigBrand และ NotBigBrand ออกมาได้เช่นกัน ซึ่งใน NotBigBrand พบว่ามี Brand เดียวที่ไม่ผ่านเกณฑ์ คือ Northwind Trader

ซึ่งเราลองสร้าง Query อีกอันมาเช็คดูจริงๆ ว่าในแต่ละปี แต่ละ brand ขายได้เท่าไหร่

เราจะพบว่ามีหลาย Brand ที่ไม่ควรผ่าน แต่มันดันเจอแค่ Northwind Trader อันเดียว น่าจะเป็นเพราะมันเป็น Brand เดียวที่ไม่ผ่านในปี 2022

ทีนี้ผมเลยลองเอาส่วนของ BrandandYear ออกมาดูดังนี้

/* START QUERY BUILDER */
EVALUATE
CALCULATETABLE(

VAR BrandandYear=SUMMARIZE(OrderDetail,dDate[Year],ProductMaster[Brand])
    
VAR BigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]>10000))

VAR NotBigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]<=10000))

VAR BigBrandEveryYear=EXCEPT(BigBrand,NotBigBrand)
VAR CalRev=CALCULATE([Total Revenue],BigBrandEveryYear)
//RETURN COUNTROWS(BigBrandEveryYear)
//RETURN CalRev
RETURN BrandandYear
    
    ,
    KEEPFILTERS( TREATAS( {2022}, dDate[Year] ))
)
/* END QUERY BUILDER */

พบว่า Filter ปี 2022 ส่งผลเข้าไปใน BrandandYear ทำให้เห็นปีไม่ครบนั่นเอง ทั้งนี้เพราะเราใช้ SUMMARIZE ซึ่งจะทำงานภายใต้ Filter Context แบบไม่ได้มีการปลด Filter ออก

ดังนั้นเราจะเปลี่ยนมาเป็นการใช้ CROSSJOIN + ALL เพื่อให้เห็นทุกปีเสมอ ดังนี้

โดยเปลี่ยนจาก

VAR BrandandYear=SUMMARIZE(OrderDetail,dDate[Year],ProductMaster[Brand])

เป็น

VAR BrandandYear=CROSSJOIN(ALL(dDate[Year]),ALL(ProductMaster[Brand]))

/* START QUERY BUILDER */
EVALUATE
CALCULATETABLE(

VAR BrandandYear=CROSSJOIN(ALL(dDate[Year]),ALL(ProductMaster[Brand]))
    
VAR BigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]>10000))

VAR NotBigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]<=10000))

VAR BigBrandEveryYear=EXCEPT(BigBrand,NotBigBrand)
VAR CalRev=CALCULATE([Total Revenue],BigBrandEveryYear)
//RETURN COUNTROWS(BigBrandEveryYear)
//RETURN CalRev
RETURN BrandandYear
    
    ,
    KEEPFILTERS( TREATAS( {2022}, dDate[Year] ))
)
/* END QUERY BUILDER */

สรุปได้แบบนี้ ซึ่งจะเห็นว่าแม้จะมี Filter ปี 2022 ไว้จาก KEEPFILTERS ก็ตาม แต่ว่าเราก็สามารถสร้างตารางที่เห็นทุกปีได้แล้ว

ซึ่งพอเอาไปใช้ใน Filter แล้วเราใส่ Measure เข้าไปว่า [Total Qty] มันก็จะเกิด Context Transition ขึึ้นทำให้ได้ยอดของ Brand นั้นๆ ในปีนั้นๆ จนครบได้ ผล BigBrandEveryYear ที่ถูกต้องจึงออกมาแบบนี้

และนี่คือ 6 Brand ที่ทำยอด Total Qty ได้มากกว่า 10000 ชิ้นทุกปี

และถ้าเราเอา BigBrandEveryYear นี้ไปเป็น Filter ของ CALCULATE ก็จะหาได้ว่า Brand เหล่านี้สร้าง Total Revenue ได้เท่าไหร่

Total Revenue by Brand buy Gt10000 every year = 
VAR BrandandYear=CROSSJOIN(ALL(dDate[Year]),ALL(ProductMaster[Brand]))
    
VAR BigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]>10000))

VAR NotBigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]<=10000))

VAR BigBrandEveryYear=EXCEPT(BigBrand,NotBigBrand)
VAR CalRev=CALCULATE([Total Revenue],BigBrandEveryYear)
//RETURN COUNTROWS(BigBrandEveryYear)
RETURN CalRev

รวมถึงถ้าเอาไปใช้ใน CONCATENATEX ก็เอารายชื่อ Brand ออกมาเป็นข้อความเดียวได้

Brand List buy Gt10000 every year = 
VAR BrandandYear=CROSSJOIN(ALL(dDate[Year]),ALL(ProductMaster[Brand]))
    
VAR BigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]>10000))

VAR NotBigBrand=CALCULATETABLE(DISTINCT(ProductMaster[Brand]),
FILTER(BrandandYear,[Total Qty]<=10000))

VAR BigBrandEveryYear=EXCEPT(BigBrand,NotBigBrand)
VAR BrandList=CONCATENATEX(BigBrandEveryYear,ProductMaster[Brand],UNICHAR(10),[Total Revenue],DESC)
//RETURN COUNTROWS(BigBrandEveryYear)
RETURN BrandList

เอาไปใช้ในรายงาน ได้แบบนี้

สรุป

นี่คือสิ่งที่ DAX Studio ช่วยเราได้ในกรณีที่ต้องเขียนสูตร DAX ที่เริ่มมีความซับซ้อนครับ เรายังไม่รู้ว่าในอนาคต AI จะเก่งพอที่จะเขียน DAX ได้อย่างถูกต้องเลยหรือไม่ ดังนั้นถ้าให้มันช่วยเขียนเราก็ต้องเข้าใจสิ่งที่มันเขียนด้วยอยู่ดี ซึ่ง DAX Studio ก็จะเป็นตัวที่ช่วยได้ในระดับนึงเลย

สุดท้ายนี้หวังว่าเพื่อนๆ จะพอเข้าใจการทำงานของ DAX Studio มากขึ้น และสามารถนำมาช่วยปรับปรุงการเขียนสูตร DAX ของเราได้นะครับ