สูตรขั้นสูง เช่น IF และ VLOOKUP อาจยุ่งยากใน Excel เมื่อทำงานกับชุดข้อมูลขนาดใหญ่ ในทางกลับกัน Power BI มี Data Analysis Expressions (DAX) ซึ่งสามารถปรับปรุงและลดความซับซ้อนในการคำนวณเหล่านี้ได้ สูตร DAX ช่วยให้คุณสร้างคอลัมน์จากการคำนวณ หน่วยวัด และตารางที่มีประสิทธิภาพและดูแลรักษาง่ายกว่าสูตร Excel แบบดั้งเดิม
ในบทช่วยสอนนี้ เราจะสำรวจสูตร Power BI DAX 5 สูตรที่ทำให้การคำนวณ Excel ที่ซับซ้อนง่ายขึ้น หากคุณเป็นผู้ใช้ Excel ระดับสูง การเรียนรู้สูตร DAX จะช่วยเพิ่มทักษะ Power BI ของคุณ และทำให้การวิเคราะห์ข้อมูลของคุณแข็งแกร่งและมีประสิทธิภาพมากขึ้น
1. คำนวณ:การแก้ไขบริบทตัวกรอง
ใน Excel การคำนวณค่าตามเงื่อนไขเฉพาะมักจะต้องใช้คำสั่ง IF ที่ซ้อนกัน ฟังก์ชัน CALCULATE ของ DAX ช่วยให้คุณสามารถปรับเปลี่ยนบริบทตัวกรองได้อย่างมีประสิทธิภาพและอ่านง่ายยิ่งขึ้น เป็นหนึ่งในฟังก์ชัน DAX ที่ทรงพลังที่สุด ซึ่งเปิดใช้งานการรวมที่ซับซ้อนและการกรองแบบไดนามิก
เทียบเท่ากับ Excel:คำสั่ง IF ที่ซ้อนกัน
ใน Excel คุณอาจมีสิ่งนี้:
=IF(A2 > 100, "High", IF(A2 > 50, "Medium", "Low"))
กำลัง BI DAX:
Filter Sales Category = CALCULATE ( IF (SUM(Sales[SalesAmount]) > 50000, "High", "Low"), Products[Category] = "Book" )
ในกรณีนี้ CALCULATE จะเปลี่ยนบริบทตัวกรองให้รวมเฉพาะข้อมูลสำหรับ "หนังสือ" ก่อนที่จะประเมินว่ายอดขายรวมเกิน 50,000 หรือไม่
CALCULATE ปรับเปลี่ยนบริบทตัวกรองปัจจุบัน โดยจะบอก Power BI ให้ละเว้นตัวกรองที่มีอยู่และใช้เงื่อนไขที่ระบุแทน คุณยังสามารถซ้อนเงื่อนไขหลายรายการได้ตามต้องการ
2. ที่เกี่ยวข้อง:การเข้าถึงตารางที่เกี่ยวข้อง
Excel ใช้ฟังก์ชันการค้นหาเพื่อดึงค่าที่เกี่ยวข้องจากตารางอื่น ใน Power BI ฟังก์ชันที่เกี่ยวข้องทำให้กระบวนการนี้ตรงไปตรงมาและมีประสิทธิภาพมากขึ้น
เทียบเท่ากับ Excel:VLOOKUP
ใน Excel สูตรเช่นนี้:
=VLOOKUP(A2, SalesData, 2, FALSE)
ดึงค่าจากคอลัมน์ที่สองของตาราง SalesData โดยที่คอลัมน์แรกตรงกับค่าใน A2
พาวเวอร์ BI DAX:เกี่ยวข้อง
ฟังก์ชันที่เกี่ยวข้องใน DAX ดึงค่าจากตารางที่เกี่ยวข้อง หากต้องการใช้ RELATED ต้องมีความสัมพันธ์ระหว่างสองตารางในแบบจำลองข้อมูล Power BI
- คอลัมน์จากการคำนวณในตารางยอดขาย
Product Category = RELATED(Products[Category])
ที่นี่ ฟังก์ชัน RELATED จะดึงประเภทผลิตภัณฑ์จากตารางผลิตภัณฑ์ตามความสัมพันธ์ที่มีอยู่ ซึ่งช่วยลดความจำเป็นในการใช้สูตรการค้นหาที่ซับซ้อนและลดข้อผิดพลาดโดยใช้ประโยชน์จากแบบจำลองข้อมูล
3. SWITCH(TRUE(), …):การแทนที่ที่สะอาดกว่าสำหรับคำสั่ง IF ที่ซ้อนกัน
ฟังก์ชัน IF ของ Excel กลายเป็นเรื่องยากที่จะจัดการด้วยหลายเงื่อนไข แต่ฟังก์ชัน SWITCH ของ DAX จะปรับปรุงลอจิกแบบมีเงื่อนไขให้มีประสิทธิภาพยิ่งขึ้น มีประโยชน์อย่างยิ่งเมื่อต้องจัดการกับเงื่อนไขต่างๆ โดยไม่มีคำสั่ง IF ที่ซ้อนกันแบบลึก
เทียบเท่ากับ Excel:คำสั่ง IF ที่ซ้อนกัน
ใน Excel คุณอาจใช้สิ่งที่ต้องการ:
=IF(A2>100000,"High",IF(A2>50000,"Medium",IF(A2>10000,"Low","Tiny")))
กำลัง BI DAX:
Sales Tier = SWITCH( TRUE(), [Total Sales] > 200000, "High Performer", [Total Sales] > 150000, "Strong", [Total Sales] > 100000, "Moderate", "Entry Level" )
Total Sales = SUM(Sales[Amount])
กลุ่มลูกค้า: คอลัมน์จากการคำนวณ
Customer Segment Logic = SWITCH( TRUE(), CALCULATE([Total Sales]) > 75000 && RELATED(Regions[Country]) = "United States", "US VIP", CALCULATE([Total Sales]) > 50000 && RELATED(Regions[Country]) = "United Kingdom", "UK Premium", CALCULATE([Total Sales]) > 30000 && RELATED(Regions[Country]) = "Canada", "Canada Premium", Customers[CustomerType] = "Premium", "Premium Customer", "Standard" )
วิธีการนี้สามารถอ่านได้ง่ายกว่าคำสั่ง IF ที่ซ้อนกันแบบลึกอย่างมาก เหมาะอย่างยิ่งสำหรับตรรกะการจำแนกประเภท เช่น การแบ่งส่วน แถบ หรือการจัดหมวดหมู่ KPI
4. SUMX:วนซ้ำตารางเพื่อคำนวณผลรวม
เมื่อคุณต้องการคำนวณทีละแถวแล้วรวมผลลัพธ์ SUMX จะเป็นฟังก์ชันที่เหมาะสม โดยวนซ้ำตาราง ประเมินนิพจน์สำหรับแต่ละแถว จากนั้นจึงรวมผลลัพธ์
เทียบเท่ากับ Excel:SUMPRODUCT
ใน Excel คุณอาจใช้:
=SUMPRODUCT(A2:A10, B2:B10)
กำลัง BI DAX:
Total Revenue = SUMX( Sales, Sales[Quantity] * Sales[UnitPrice] )
ฟังก์ชัน SUMX วนซ้ำแต่ละแถวของตาราง Sales คูณปริมาณด้วย UnitPrice จากนั้นจึงรวมผลลัพธ์
5. คำนวณ + ระบบอัจฉริยะเวลา:กำจัดตรรกะวันที่แบบแมนนวล
ใน Excel การคำนวณตามเวลามักจะขึ้นอยู่กับรูปแบบ SUMIFS, OFFSET หรือ INDEX/MATCH ที่ซับซ้อน DAX มีฟังก์ชันข่าวกรองเวลาในตัวที่ทำให้งานเหล่านี้ง่ายขึ้น
กำลัง BI DAX:
Sales YoY % Growth =
VAR CurrentSales = SUM(Sales[SalesAmount])
VAR PreviousSales =
CALCULATE(
SUM(Sales[SalesAmount]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)
RETURN
DIVIDE(CurrentSales - PreviousSales, PreviousSales, 0)
การวัดเวลาอัจฉริยะในตัวที่เรียบง่าย:
YTD Sales = TOTALYTD( SUM(Sales[SalesAmount]), 'Calendar'[Date] )
Sales vs Last Year =
CALCULATE(
SUM(Sales[SalesAmount]),
PARALLELPERIOD('Calendar'[Date], -1, YEAR)
)
ฟังก์ชันเหล่านี้ทำงานได้อย่างราบรื่นกับตัวกรองวันที่ในรายงานของคุณ รวมถึงตัวแบ่งส่วนข้อมูลสำหรับเดือน ไตรมาส หรือปีบัญชี ไม่จำเป็นต้องมีคอลัมน์ตัวช่วยหรือการปรับเปลี่ยนด้วยตนเอง
สูตร DAX ในรายงาน:
เคล็ดลับ:การใช้ DIVIDE เพื่อจัดการกับข้อผิดพลาด
ใน Excel การหารด้วยศูนย์มักส่งผลให้เกิดข้อผิดพลาด DAX มอบโซลูชันที่มีประสิทธิภาพยิ่งขึ้นผ่านฟังก์ชัน DIVIDE ซึ่งจัดการการหารด้วยศูนย์ได้อย่างสวยงาม
ฟังก์ชัน DIVIDE ช่วยให้คุณสามารถระบุผลลัพธ์ทางเลือกเมื่อมีการหารด้วยศูนย์:
Profit Margin = DIVIDE(Sales[Profit], Sales[Total Revenue], 0)
ฟังก์ชันนี้จะคืนค่า 0 เมื่อตัวส่วนเป็นศูนย์ เพื่อหลีกเลี่ยงข้อผิดพลาดโดยไม่ต้องใช้ตรรกะเพิ่มเติม
เคล็ดลับการเริ่มต้นอย่างรวดเร็วสำหรับผู้ใช้ Excel
- สร้างความสัมพันธ์ก่อนเสมอ: นี่คือจุดที่ RELATED และ CALCULATE ได้รับพลัง
- สร้างหน่วยวัด ไม่ใช่คอลัมน์: โดยทั่วไปแล้ว มาตรการต่างๆ จะรวดเร็วกว่าและยืดหยุ่นกว่า
- ใช้ตัวแปร (VAR): ปรับปรุงความสามารถในการอ่านและการบำรุงรักษา
- ทดสอบด้วยภาพเปล่า: ตรวจสอบหน่วยวัดโดยใช้การ์ดหรือตารางพร้อมตัวแบ่งส่วนข้อมูล
- เคล็ดลับด้านประสิทธิภาพ: เก็บตัวกรองให้แคบที่สุดเท่าที่จะเป็นไปได้ ตัวกรองคอลัมน์โดยตรงมักจะเร็วกว่าการสแกนตารางทั้งหมด
บทสรุป
ฟังก์ชัน Power BI DAX ทั้งห้าสูตร ได้แก่ คำนวณ ที่เกี่ยวข้อง สลับ SUMX และข่าวกรองเวลา มอบวิธีที่สะอาดกว่าและมีประสิทธิภาพมากขึ้นในการจัดการการคำนวณที่อาจต้องใช้สูตร Excel ที่ซับซ้อน เช่น คำสั่ง IF ที่ซ้อนกันหรือ VLOOKUP ด้วยการรวมเทคนิคเหล่านี้เข้ากับขั้นตอนการทำงานของคุณ คุณสามารถทำให้โมเดลข้อมูลของคุณง่ายขึ้น ปรับปรุงประสิทธิภาพ และสร้างรายงานที่ปรับขนาดได้มากขึ้น
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!