ส่วนนี้มีลิงก์ไปยังตัวอย่างที่สาธิตการใช้สูตร DAX ในสถานการณ์ต่อไปนี้
-
การคํานวณที่ซับซ้อน
-
การทํางานกับข้อความและวันที่
-
ค่าตามเงื่อนไขและการทดสอบข้อผิดพลาด
-
การใช้ตัวแสดงเวลา
-
การจัดอันดับและการเปรียบเทียบค่า
ในบทความนี้
การเริ่มต้นใช้งาน
เยี่ยมชม DAX Resource Center Wiki ซึ่งคุณสามารถค้นหาข้อมูลทุกประเภทเกี่ยวกับ DAX รวมถึงบล็อก ตัวอย่าง เอกสาร และวิดีโอที่ให้บริการโดยผู้เชี่ยวชาญระดับแนวหน้าของอุตสาหกรรมและ Microsoft
สถานการณ์สมมติ: การคํานวณที่ซับซ้อน
สูตร DAX สามารถทําการคํานวณที่ซับซ้อนซึ่งเกี่ยวข้องกับการรวมแบบกําหนดเอง การกรอง และการใช้ค่าตามเงื่อนไข ส่วนนี้แสดงตัวอย่างของวิธีการเริ่มต้นการคํานวณแบบกําหนดเอง
สร้างการคํานวณแบบกําหนดเองสําหรับ PivotTable
ฟังก์ชัน CALCULATE และ CALCULATETABLE เป็นฟังก์ชันที่มีประสิทธิภาพและยืดหยุ่น ซึ่งมีประโยชน์สําหรับการกําหนดเขตข้อมูลจากการคํานวณ ฟังก์ชันเหล่านี้ช่วยให้คุณสามารถเปลี่ยนบริบทที่จะทําการคํานวณได้ คุณยังสามารถกําหนดชนิดของการรวมหรือการดําเนินการทางคณิตศาสตร์เองได้ ดูหัวข้อต่อไปนี้สําหรับตัวอย่าง
นําตัวกรองไปใช้กับสูตร
ในตําแหน่งส่วนใหญ่ที่ฟังก์ชัน DAX ใช้ตารางเป็นอาร์กิวเมนต์ โดยปกติแล้ว คุณสามารถส่งผ่านตารางที่ถูกกรองแทนได้ โดยใช้ฟังก์ชัน FILTER แทนชื่อตาราง หรือโดยการระบุนิพจน์ตัวกรองเป็นหนึ่งในอาร์กิวเมนต์ของฟังก์ชัน หัวข้อต่อไปนี้แสดงตัวอย่างของวิธีการสร้างตัวกรองและผลกระทบของตัวกรองต่อผลลัพธ์ของสูตร สําหรับข้อมูลเพิ่มเติม ให้ดูที่ กรองข้อมูลในสูตร DAX
ฟังก์ชัน FILTER ช่วยให้คุณระบุเกณฑ์ตัวกรองโดยใช้นิพจน์ ในขณะที่ฟังก์ชันอื่นๆ ถูกออกแบบมาเพื่อกรองค่าว่างโดยเฉพาะ
ลบตัวกรองที่เลือกเพื่อสร้างอัตราส่วนแบบไดนามิก
ด้วยการสร้างตัวกรองแบบไดนามิกในสูตร คุณสามารถตอบคําถามได้อย่างง่ายดายดังต่อไปนี้:
-
ยอดขายของผลิตภัณฑ์ปัจจุบันมีส่วนสนับสนุนการขายทั้งหมดสําหรับปีอย่างไร
-
แผนกนี้มีส่วนช่วยในการทํากําไรรวมสําหรับทุกปีในการดําเนินงานมากเพียงใดเมื่อเทียบกับแผนกอื่น ๆ
สูตรที่คุณใช้ใน PivotTable อาจได้รับผลกระทบจากบริบท PivotTable แต่คุณสามารถเลือกเปลี่ยนบริบทได้โดยการเพิ่มหรือเอาตัวกรองออก ตัวอย่างในหัวข้อ ทั้งหมด จะแสดงวิธีดําเนินการนี้ให้คุณทราบ เมื่อต้องการค้นหาอัตราส่วนยอดขายสําหรับผู้จําหน่ายที่เฉพาะเจาะจงสําหรับผู้จําหน่ายทั้งหมด ให้คุณสร้างหน่วยวัดที่คํานวณค่าสําหรับบริบทปัจจุบันหารด้วยค่าสําหรับบริบท ALL
หัวข้อ ALLEXCEPT มีตัวอย่างของวิธีการล้างตัวกรองที่เลือกในสูตร ตัวอย่างทั้งสองจะแนะนําคุณเกี่ยวกับการเปลี่ยนแปลงผลลัพธ์โดยขึ้นอยู่กับการออกแบบของ PivotTable
สําหรับตัวอย่างอื่นๆ ของวิธีคํานวณอัตราส่วนและเปอร์เซ็นต์ ให้ดูหัวข้อต่อไปนี้
การใช้ค่าจากการวนรอบภายนอก
นอกจากการใช้ค่าจากบริบทปัจจุบันในการคํานวณแล้ว DAX สามารถใช้ค่าจากลูปก่อนหน้าในการสร้างชุดการคํานวณที่เกี่ยวข้อง หัวข้อต่อไปนี้มีบทสรุปเกี่ยวกับวิธีการสร้างสูตรที่อ้างอิงค่าจากลูปภายนอก ฟังก์ชัน EARLIER สนับสนุนลูปซ้อนกันสูงสุดสองระดับ
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับบริบทของแถวและตารางที่เกี่ยวข้อง และวิธีการใช้แนวคิดนี้ในสูตร ให้ดู บริบทในสูตร DAX
สถานการณ์สมมติ: การทํางานกับข้อความและวันที่
ส่วนนี้มีลิงก์ไปยังหัวข้อการอ้างอิง DAX ที่มีตัวอย่างของสถานการณ์สมมติทั่วไปที่เกี่ยวข้องกับการทํางานกับข้อความ การแยกและเขียนค่าวันที่และเวลา หรือการสร้างค่าตามเงื่อนไข
สร้างคอลัมน์หลักโดยการเรียงต่อกัน
Power Pivot ไม่อนุญาตให้ใช้คีย์ผสม ดังนั้น ถ้าคุณมีคีย์ผสมในแหล่งข้อมูลของคุณ คุณอาจต้องรวมคีย์เหล่านั้นไว้ในคอลัมน์คีย์เดียว หัวข้อต่อไปนี้มีตัวอย่างหนึ่งของวิธีการสร้างคอลัมน์จากการคํานวณโดยยึดตามคีย์ผสม
เขียนวันที่โดยยึดตามส่วนวันที่ที่แยกจากวันที่ที่เป็นข้อความ
Power Pivot ใช้ชนิดข้อมูลวันที่/เวลาของ SQL Server เพื่อทํางานกับวันที่ ดังนั้น ถ้าข้อมูลภายนอกของคุณมีวันที่ที่จัดรูปแบบต่างกัน ตัวอย่างเช่น ถ้าวันที่ของคุณถูกเขียนในรูปแบบวันที่ในภูมิภาคที่โปรแกรมข้อมูล Power Pivot ไม่รู้จัก หรือถ้าข้อมูลของคุณใช้คีย์ตัวแทนจํานวนเต็ม คุณอาจต้องใช้สูตร DAX เพื่อแยกส่วนวันที่ แล้วสร้างส่วนต่างๆ ลงในการแสดงวันที่/เวลาที่ถูกต้อง
ตัวอย่างเช่น ถ้าคุณมีคอลัมน์ของวันที่ที่แสดงเป็นจํานวนเต็ม แล้วนําเข้าเป็นสตริงข้อความ คุณสามารถแปลงสตริงเป็นค่าวันที่/เวลาโดยใช้สูตรต่อไปนี้
=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))
|
ค่า 1 |
ผลลัพธ์ |
|---|---|
|
01032009 |
1/3/2009 |
|
12132008 |
12/13/2008 |
|
06252007 |
6/25/2007 |
หัวข้อต่อไปนี้มีข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชันที่ใช้แยกและเรียบเรียงวันที่
กําหนดรูปแบบวันที่หรือตัวเลขแบบกําหนดเอง
ถ้าข้อมูลของคุณมีวันที่หรือตัวเลขที่ไม่ได้แสดงในรูปแบบข้อความมาตรฐานรูปแบบใดรูปแบบหนึ่งของ Windows คุณสามารถกําหนดรูปแบบแบบกําหนดเองเพื่อให้แน่ใจว่าค่าต่างๆ ได้รับการจัดการอย่างถูกต้อง รูปแบบเหล่านี้จะใช้เมื่อแปลงค่าเป็นสตริง หรือจากสตริง หัวข้อต่อไปนี้ยังแสดงรายการโดยละเอียดของรูปแบบที่กําหนดไว้ล่วงหน้าที่พร้อมใช้งานสําหรับการทํางานกับวันที่และตัวเลข
เปลี่ยนชนิดข้อมูลโดยใช้สูตร
ใน Power Pivot ชนิดข้อมูลของผลลัพธ์จะถูกกําหนดโดยคอลัมน์ต้นฉบับ และคุณไม่สามารถระบุชนิดข้อมูลของผลลัพธ์ได้อย่างชัดเจน เนื่องจากชนิดข้อมูลที่เหมาะสมจะถูกกําหนดโดย Power Pivot อย่างไรก็ตาม คุณสามารถใช้การแปลงชนิดข้อมูลโดยนัยที่ดําเนินการโดย Power Pivot เพื่อจัดการชนิดข้อมูลผลลัพธ์
-
เมื่อต้องการแปลงวันที่หรือสตริงตัวเลขเป็นตัวเลข ให้คูณด้วย 1.0 ตัวอย่างเช่น สูตรต่อไปนี้จะคํานวณวันที่ปัจจุบันลบด้วย 3 วัน แล้วให้ผลลัพธ์เป็นค่าจํานวนเต็มที่สอดคล้องกัน
=(TODAY()-3)*1.0
-
เมื่อต้องการแปลงค่าวันที่ ตัวเลข หรือสกุลเงินเป็นสตริง ให้ต่อค่ากับสตริงว่าง ตัวอย่างเช่น สูตรต่อไปนี้จะส่งกลับวันที่ของวันนี้เป็นสตริง
=""& TODAY()
ฟังก์ชันต่อไปนี้ยังสามารถใช้เพื่อให้แน่ใจว่าชนิดข้อมูลเฉพาะจะถูกส่งกลับ:
แปลงจํานวนจริงเป็นจํานวนเต็ม
-
แปลงจํานวนจริง จํานวนเต็ม หรือวันที่เป็นสตริง
-
แปลงสตริงเป็นตัวเลขจริงหรือวันที่
สถานการณ์สมมติ: ค่าตามเงื่อนไขและการทดสอบข้อผิดพลาด
เช่นเดียวกับ Excel DAX มีฟังก์ชันที่ให้คุณทดสอบค่าในข้อมูลและส่งกลับค่าอื่นโดยยึดตามเงื่อนไข ตัวอย่างเช่น คุณสามารถสร้างคอลัมน์จากการคํานวณที่ป้ายชื่อผู้จําหน่ายเป็น ที่ต้องการ หรือ ค่า โดยขึ้นอยู่กับยอดขายรายปี ฟังก์ชันที่ทดสอบค่ายังมีประโยชน์สําหรับการตรวจสอบช่วงหรือชนิดของค่าเพื่อป้องกันไม่ให้เกิดข้อผิดพลาดที่ไม่คาดคิดกับการคํานวณ
สร้างค่าตามเงื่อนไข
คุณสามารถใช้เงื่อนไข IF ที่ซ้อนกันเพื่อทดสอบค่าและสร้างค่าใหม่ตามเงื่อนไข หัวข้อต่อไปนี้มีตัวอย่างง่ายๆ ของการประมวลผลตามเงื่อนไขและค่าตามเงื่อนไข:
ทดสอบหาข้อผิดพลาดภายในสูตร
ไม่เหมือนกับ Excel คุณไม่สามารถมีค่าที่ถูกต้องในแถวหนึ่งของคอลัมน์จากการคํานวณและค่าที่ไม่ถูกต้องในแถวอื่นได้ นั่นคือถ้ามีข้อผิดพลาดในส่วนใดส่วนหนึ่งของคอลัมน์ Power Pivot ทั้งคอลัมน์จะถูกตั้งค่าสถานะด้วยข้อผิดพลาด ดังนั้นคุณต้องแก้ไขข้อผิดพลาดของสูตรเสมอซึ่งส่งผลให้มีค่าที่ไม่ถูกต้อง
ตัวอย่างเช่น ถ้าคุณสร้างสูตรที่หารด้วยศูนย์ คุณอาจได้รับผลลัพธ์อนันต์ หรือข้อผิดพลาด สูตรบางสูตรจะล้มเหลวถ้าฟังก์ชันพบค่าว่างเมื่อฟังก์ชันต้องการค่าตัวเลข ในขณะที่คุณกําลังพัฒนาตัวแบบข้อมูลของคุณ ทางที่ดีที่สุดคืออนุญาตให้ข้อผิดพลาดปรากฏขึ้นเพื่อให้คุณสามารถคลิกที่ข้อความและแก้ไขปัญหาได้ อย่างไรก็ตาม เมื่อคุณประกาศเวิร์กบุ๊ก คุณควรรวมการจัดการข้อผิดพลาดเพื่อป้องกันไม่ให้ค่าที่ไม่คาดคิดทําให้การคํานวณล้มเหลว
เมื่อต้องการหลีกเลี่ยงไม่ให้ส่งกลับข้อผิดพลาดในคอลัมน์จากการคํานวณ ให้คุณใช้ฟังก์ชันทางตรรกะและข้อมูลร่วมกันเพื่อทดสอบข้อผิดพลาดและส่งกลับค่าที่ถูกต้องเสมอ หัวข้อต่อไปนี้แสดงตัวอย่างง่ายๆ ของวิธีการดําเนินการใน DAX:
สถานการณ์สมมติ: การใช้ตัวแสดงเวลา
ฟังก์ชันตัวแสดงเวลา DAX มีฟังก์ชันที่ช่วยให้คุณเรียกใช้วันที่หรือช่วงวันที่จากข้อมูลของคุณ จากนั้น คุณสามารถใช้วันที่หรือช่วงวันที่เหล่านั้นเพื่อคํานวณค่าในคาบเวลาที่คล้ายกันได้ ฟังก์ชันตัวแสดงเวลายังมีฟังก์ชันที่ทํางานกับช่วงวันที่มาตรฐาน เพื่อให้คุณสามารถเปรียบเทียบค่าระหว่างเดือน ปี หรือไตรมาส คุณยังสามารถสร้างสูตรที่เปรียบเทียบค่าสําหรับวันที่แรกและวันสุดท้ายของช่วงเวลาที่ระบุได้
สําหรับรายการของฟังก์ชันตัวแสดงเวลาทั้งหมด ให้ดูที่ ฟังก์ชันตัวแสดงเวลา (DAX) สําหรับเคล็ดลับเกี่ยวกับวิธีการใช้วันที่และเวลาอย่างมีประสิทธิภาพในการวิเคราะห์ Power Pivot ให้ดูที่ วันที่ใน Power Pivot
คํานวณยอดขายสะสม
หัวข้อต่อไปนี้มีตัวอย่างของวิธีการคํานวณยอดดุลปิดและยอดดุลยกมา ตัวอย่างช่วยให้คุณสร้างยอดดุลสะสมในช่วงเวลาต่างๆ เช่น วัน เดือน ไตรมาส หรือปี
เปรียบเทียบค่าเมื่อเวลาผ่านไป
หัวข้อต่อไปนี้มีตัวอย่างของวิธีการเปรียบเทียบผลรวมในช่วงเวลาต่างๆ ช่วงเวลาเริ่มต้นที่ได้รับการสนับสนุนโดย DAX คือเดือน ไตรมาส และปี
คํานวณค่าในช่วงวันที่แบบกําหนดเอง
ดูหัวข้อต่อไปนี้สําหรับตัวอย่างของวิธีเรียกใช้ช่วงวันที่แบบกําหนดเอง เช่น 15 วันแรกหลังจากเริ่มการส่งเสริมการขาย
ถ้าคุณใช้ฟังก์ชันตัวแสดงเวลาเพื่อเรียกใช้ชุดวันที่แบบกําหนดเอง คุณสามารถใช้ชุดของวันที่นั้นเป็นข้อมูลป้อนเข้าไปยังฟังก์ชันที่ทําการคํานวณ เพื่อสร้างการรวมแบบกําหนดเองในช่วงเวลาต่างๆ ดูหัวข้อต่อไปนี้สําหรับตัวอย่างของวิธีการทําสิ่งนี้:
-
หมายเหตุ: ถ้าคุณไม่จําเป็นต้องระบุช่วงวันที่แบบกําหนดเอง แต่กําลังทํางานกับหน่วยบัญชีมาตรฐาน เช่น เดือน ไตรมาส หรือปี เราขอแนะนําให้คุณทําการคํานวณโดยใช้ฟังก์ชันตัวแสดงเวลาที่ออกแบบสําหรับวัตถุประสงค์นี้ เช่น TOTALQTD, TOTALMTD, TOTALQTD เป็นต้น
สถานการณ์สมมติ: การจัดอันดับและการเปรียบเทียบค่า
เมื่อต้องการแสดงเฉพาะจํานวน N อันดับแรกของรายการในคอลัมน์หรือ PivotTable คุณจะมีตัวเลือกหลายตัวเลือก:
-
คุณสามารถใช้ฟีเจอร์ใน Excel เพื่อสร้างตัวกรองยอดนิยมได้ คุณยังสามารถเลือกจํานวนค่าบนสุดหรือค่าล่างสุดใน PivotTable ได้อีกด้วย ส่วนแรกของส่วนนี้จะอธิบายวิธีการกรองสําหรับรายการ 10 อันดับแรกใน PivotTable สําหรับข้อมูลเพิ่มเติม ให้ดูเอกสารประกอบ Excel
-
คุณสามารถสร้างสูตรที่จัดอันดับค่าแบบไดนามิก แล้วกรองตามค่าการจัดอันดับ หรือใช้ค่าการจัดอันดับเป็นตัวแบ่งส่วนข้อมูล ส่วนที่สองของส่วนนี้จะอธิบายวิธีการสร้างสูตรนี้ แล้วใช้การจัดอันดับนั้นในตัวแบ่งส่วนข้อมูล
มีข้อดีและข้อเสียของแต่ละวิธี
-
ตัวกรองรายการสูงสุดของ Excel ใช้งานง่าย แต่ตัวกรองมีไว้เพื่อวัตถุประสงค์ในการแสดงผลเท่านั้น ถ้าข้อมูลพื้นฐานของ PivotTable มีการเปลี่ยนแปลง คุณต้องรีเฟรช PivotTable ด้วยตนเองเพื่อดูการเปลี่ยนแปลง ถ้าคุณต้องการทํางานกับการจัดอันดับแบบไดนามิก คุณสามารถใช้ DAX เพื่อสร้างสูตรที่เปรียบเทียบค่ากับค่าอื่นๆ ภายในคอลัมน์ได้
-
สูตร DAX มีประสิทธิภาพมากกว่า นอกจากนี้ ด้วยการเพิ่มค่าการจัดอันดับลงในตัวแบ่งส่วนข้อมูล คุณสามารถคลิกที่ตัวแบ่งส่วนข้อมูลเพื่อเปลี่ยนจํานวนค่าสูงสุดที่แสดง อย่างไรก็ตาม การคํานวณมีราคาแพงทางการคํานวณและวิธีนี้อาจไม่เหมาะสําหรับตารางที่มีแถวจํานวนมาก
แสดงเฉพาะสิบรายการแรกใน PivotTable
|
เมื่อต้องการแสดงค่าสูงสุดหรือค่าต่ําสุดใน PivotTable
|
เรียงลําดับรายการแบบไดนามิกโดยใช้สูตร
หัวข้อต่อไปนี้มีตัวอย่างของวิธีการใช้ DAX เพื่อสร้างการจัดอันดับที่เก็บไว้ในคอลัมน์จากการคํานวณ เนื่องจากสูตร DAX จะถูกคํานวณแบบไดนามิก คุณจึงมั่นใจได้ว่าการจัดอันดับถูกต้องแม้ว่าข้อมูลพื้นฐานจะมีการเปลี่ยนแปลงก็ตาม นอกจากนี้ เนื่องจากสูตรถูกใช้ในคอลัมน์จากการคํานวณ คุณสามารถใช้การจัดอันดับในตัวแบ่งส่วนข้อมูล แล้วเลือก 5 อันดับแรก 10 อันดับแรก หรือแม้แต่ค่า 100 อันดับแรก
