ส่วนนี้จะมีลิงก์ไปยังตัวอย่างที่สาธิตการใช้สูตร DAX ในสถานการณ์ต่อไปนี้
-
การคํานวณที่ซับซ้อน
-
การใช้งานได้กับข้อความและวันที่
-
ค่าตามเงื่อนไขและการทดสอบข้อผิดพลาด
-
การใช้ตัวแสดงเวลา
-
การจัดอันดับและการเปรียบเทียบค่า
ในบทความนี้
เริ่มต้นใช้งาน
เยี่ยมชม DAX Resource Center Wiki ที่คุณสามารถค้นหาข้อมูลทุกประเภทเกี่ยวกับ DAX รวมถึงบล็อก ตัวอย่าง รายงาน และวิดีโอที่จัดหาให้โดยผู้เชี่ยวชาญชั้นนาในอุตสาหกรรมและ Microsoft
สถานการณ์สมมติ: การคํานวณที่ซับซ้อน
สูตร DAX สามารถคํานวณที่ซับซ้อนที่เกี่ยวข้องกับการรวม การกรอง และการใช้ค่าตามเงื่อนไขแบบเองได้ ส่วนนี้จะมีตัวอย่างของวิธีการเริ่มต้นใช้งานการคํานวณแบบปรับแต่งเอง
สร้างการคํานวณแบบปรับแต่งเองของ PivotTable
CALCULATE และ CALCULATETABLE คือฟังก์ชันที่มีประสิทธิภาพและยืดหยุ่น ซึ่งมีประโยชน์ในการคํานวณเขตข้อมูล ฟังก์ชันเหล่านี้ช่วยให้คุณเปลี่ยนบริบทที่จะคํานวณได้ คุณยังสามารถปรับแต่งชนิดของการรวมหรือการดําเนินการทางคณิตศาสตร์ที่จะดําเนินการได้ ดูหัวข้อต่อไปนี้ตัวอย่างเช่น
การใช้ตัวกรองกับสูตร
ในที่ส่วนใหญ่ที่ฟังก์ชัน DAX จะใช้ตารางเป็นอาร์กิวเมนต์ คุณสามารถส่งผ่านตารางที่กรองแล้วแทนได้ โดยการใช้ฟังก์ชัน FILTER แทนชื่อตาราง หรือโดยการระบุนิพจน์ตัวกรองให้เป็นหนึ่งในอาร์กิวเมนต์ของฟังก์ชัน หัวข้อต่อไปนี้แสดงตัวอย่างวิธีการสร้างตัวกรองและผลกระทบที่ตัวกรองมีผลต่อผลลัพธ์ของสูตร ดูข้อมูลเพิ่มเติมที่กรองข้อมูลในสูตร DAX
ฟังก์ชัน FILTER ช่วยให้คุณระบุเกณฑ์การกรองโดยใช้นิพจน์ ในขณะที่ฟังก์ชันอื่นๆ ได้รับการออกแบบมาโดยเฉพาะเพื่อกรองค่าว่างออก
เอาตัวกรองที่เลือกออกเพื่อสร้างอัตราส่วนแบบไดนามิก
เมื่อสร้างตัวกรองแบบไดนามิกในสูตร คุณจะสามารถตอบคําถามต่อไปนี้ได้อย่างง่ายดาย:
-
การมีส่วนร่วมในยอดขายรวมของผลิตภัณฑ์ปัจจุบันของปีมีอะไรบ้าง
-
ผลกําไรรวมของทุกปีดําเนินการมีการหารนี้มากน้อยเท่าใดเมื่อเทียบกับผลกําไรรวมของทุกปีจากการหารอื่นๆ
สูตรที่คุณใช้ใน PivotTable อาจได้รับผลกระทบจากบริบท PivotTable แต่คุณสามารถเลือกเปลี่ยนบริบทได้โดยการเพิ่มหรือเอาตัวกรองออก ตัวอย่างในหัวข้อ ทั้งหมด จะแสดงวิธีการ To find the ratio of sales for a specific reseller over the sales for all resellers, you create a measure that calculates the value for the current context divided by the value for the ALL context.
หัวข้อ 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 คุณสามารถกําหนดรูปแบบแบบกําหนดเองเพื่อให้แน่ใจว่าค่าได้รับการจัดการอย่างถูกต้อง รูปแบบเหล่านี้จะถูกใช้เมื่อแปลงค่าเป็นสตริง หรือจากสตริง หัวข้อต่อไปนี้ยังมีรายการแบบละเอียดของรูปแบบที่กําหนดไว้ซึ่งพร้อมใช้งานในการการใช้งานได้กับวันที่และตัวเลข
เปลี่ยนชนิดข้อมูลโดยใช้สูตร
In Power Pivot, the data type of the output is determined by the source columns, and you cannot explicitly specify the data type of the result, because the optimal data type is determined by Power Pivot. อย่างไรก็ตาม คุณสามารถใช้การแปลงชนิดข้อมูลโดยนัยที่ Power Pivot จัดการชนิดข้อมูลผลลัพธ์
-
เมื่อต้องการแปลงวันที่หรือสตริงตัวเลขเป็นตัวเลข ให้คูณด้วย 1.0 ตัวอย่างเช่น สูตรต่อไปนี้จะคํานวณวันที่ปัจจุบันลบ 3 วัน แล้วแสดงผลค่าจํานวนเต็มที่สอดคล้องกัน
=(TODAY()-3)*1.0
-
เมื่อต้องการแปลงค่าวันที่ ตัวเลข หรือค่าสกุลเงินเป็นสตริง ให้เรียงค่าต่อกันด้วยสตริงว่าง ตัวอย่างเช่น สูตรต่อไปนี้จะส่งกลับวันที่ของวันนี้เป็นสตริง
=""& TODAY()
นอกจากนี้ ยังสามารถใช้ฟังก์ชันต่อไปนี้เพื่อให้แน่ใจว่าชนิดข้อมูลเฉพาะจะถูกส่งกลับ:
แปลงจํานวนจริงเป็นจํานวนเต็ม
-
แปลงจํานวนจริง จํานวนเต็ม หรือวันที่เป็นสตริง
-
แปลงสตริงเป็นตัวเลขหรือวันที่จริง
สถานการณ์: ค่าตามเงื่อนไขและการทดสอบข้อผิดพลาด
เช่นเดียวกับExcelของคุณ DAX มีฟังก์ชันที่ช่วยให้คุณทดสอบค่าในข้อมูลและส่งกลับค่าอื่นโดยยึดตามเงื่อนไข ตัวอย่างเช่น คุณสามารถสร้างคอลัมน์ที่คํานวณที่ป้ายชื่อผู้จัดซื้อเป็นที่ต้องการหรือ ค่าโดยขึ้นอยู่กับยอดขายรายปี ฟังก์ชันที่ทดสอบค่ายังมีประโยชน์ในการตรวจสอบช่วงหรือชนิดของค่า เพื่อป้องกันไม่ให้เกิดข้อผิดพลาดของข้อมูลที่ไม่คาดคิดจากการตัดการคํานวณ
สร้างค่าโดยยึดตามเงื่อนไข
คุณสามารถใช้เงื่อนไข IF ที่ซ้อนกันเพื่อทดสอบค่าและสร้างค่าใหม่ตามเงื่อนไข หัวข้อต่อไปนี้มีตัวอย่างง่ายๆ ของการประมวลผลตามเงื่อนไขและค่าตามเงื่อนไข
ทดสอบหาข้อผิดพลาดภายในสูตร
ไม่Excelกับค่าที่คุณระบุ คุณไม่สามารถมีค่าที่ถูกต้องในแถวหนึ่งของคอลัมน์ที่คํานวณและค่าที่ไม่ถูกต้องในแถวอื่นได้ นั่นคือ ถ้ามีข้อผิดพลาดในส่วนใดส่วนหนึ่งของคอลัมน์ Power Pivot คอลัมน์ทั้งหมดจะถูกตั้งค่าสถานะด้วยข้อผิดพลาด เพื่อที่คุณจะต้องแก้ไขข้อผิดพลาดของสูตรเสมอเพื่อให้ได้ผลลัพธ์เป็นค่าที่ไม่ถูกต้อง
ตัวอย่างเช่น ถ้าคุณสร้างสูตรที่หารด้วยศูนย์ คุณอาจได้รับผลลัพธ์อนันต์ หรือข้อผิดพลาด สูตรบางสูตรจะล้มเหลวด้วยถ้าฟังก์ชันพบค่าว่างเมื่อฟังก์ชันต้องการค่าตัวเลข ขณะที่คุณพัฒนาตัวแบบข้อมูล คุณควรอนุญาตให้ข้อผิดพลาดปรากฏขึ้นเพื่อให้คุณสามารถคลิกที่ข้อความและแก้ไขปัญหาได้ อย่างไรก็ตาม เมื่อคุณประกาศเวิร์กบุ๊ก คุณควรรวมการจัดการข้อผิดพลาดเพื่อป้องกันไม่ให้ค่าที่ไม่คาดคิดทําให้การคํานวณล้มเหลว
เมื่อต้องการหลีกเลี่ยงการส่งกลับข้อผิดพลาดในคอลัมน์ที่คํานวณ ให้คุณใช้ฟังก์ชันทางตรรกะและข้อมูลร่วมกันเพื่อทดสอบข้อผิดพลาด และส่งกลับค่าที่ถูกต้องเสมอ หัวข้อต่อไปนี้มีตัวอย่างง่ายๆ บางตัวอย่างของวิธีการดังกล่าวใน DAX:
สถานการณ์: การใช้ตัวแสดงเวลา
ฟังก์ชันตัวแสดงเวลา DAX มีฟังก์ชันเพื่อช่วยให้คุณเรียกใช้วันที่หรือช่วงวันที่จากข้อมูลของคุณ จากนั้น คุณสามารถใช้วันที่หรือช่วงวันที่เหล่านั้นเพื่อคํานวณค่าในงวดที่คล้ายกันได้ นอกจากนี้ ฟังก์ชันตัวแสดงเวลายังมีฟังก์ชันที่พร้อมด้วยช่วงวันที่มาตรฐานด้วย เพื่อให้คุณสามารถเปรียบเทียบค่าต่างๆ ระหว่างเดือน ปี หรือไตรมาสต่างๆ ได้ คุณยังสามารถสร้างสูตรที่เปรียบเทียบค่าต่างๆ ของวันที่แรกและวันสุดท้ายของช่วงเวลาที่ระบุได้
For a list of all time intelligence functions, see Time Intelligence Functions (DAX). For tips on how to use dates and times effectively in a Power Pivot analysis, see Dates in Power Pivot.
คํานวณยอดขายสะสม
หัวข้อต่อไปนี้มีตัวอย่างของวิธีการคํานวณยอดดุลที่ปิดและยอดดุลยกมา ตัวอย่างช่วยให้คุณสร้างยอดคงเหลือในบัญชีในช่วงเวลาต่างๆ เช่น วัน เดือน ไตรมาส หรือปี
เปรียบเทียบค่าเมื่อเวลาผ่านไป
หัวข้อต่อไปนี้มีตัวอย่างของวิธีการเปรียบเทียบผลรวมระหว่างช่วงเวลาที่แตกต่างกัน ช่วงเวลาเริ่มต้นที่สนับสนุนโดย DAX คือ เดือน ไตรมาส และปี
คํานวณค่าเหนือช่วงวันที่แบบปรับแต่งเอง
ดูหัวข้อต่อไปนี้ของตัวอย่างวิธีเรียกใช้ช่วงวันที่แบบระบุเอง เช่น 15 วันแรกหลังจากเริ่มต้นโปรโมชันการขาย
ถ้าคุณใช้ฟังก์ชันตัวแสดงเวลาเพื่อเรียกใช้ชุดของวันที่แบบระบุเอง คุณสามารถใช้ชุดของวันที่เป็นข้อมูลป้อนเข้าไปยังฟังก์ชันที่คํานวณได้ เพื่อสร้างการรวมแบบเองข้ามช่วงเวลา ดูหัวข้อต่อไปนี้เพื่อดูตัวอย่างวิธีการ:
-
หมายเหตุ: ถ้าคุณไม่ต้องการระบุช่วงวันที่แบบระบุเอง แต่ต้องใช้งานหน่วยทางบัญชีมาตรฐาน เช่น เดือน ไตรมาส หรือปี เราขอแนะนนะให้คุณคํานวณโดยใช้ฟังก์ชันตัวแสดงเวลาที่ออกแบบมาเพื่อวัตถุประสงค์นี้ เช่น TOTALQTD, TOTALMTD, TOTALQTD เป็นต้น
สถานการณ์: การจัดอันดับและการเปรียบเทียบค่า
เมื่อต้องการแสดงเฉพาะจํานวนรายการ n ด้านบนสุดในคอลัมน์หรือ PivotTable คุณมีตัวเลือกหลายตัวเลือก ดังนี้
-
คุณสามารถใช้ฟีเจอร์ใน Excel 2010 เพื่อสร้างตัวกรองบนสุด คุณยังสามารถเลือกจํานวนค่าด้านบนหรือด้านล่างใน PivotTable ได้ ส่วนแรกของส่วนนี้จะอธิบายวิธีการกรองรายการ 10 อันดับแรกใน PivotTable ดูข้อมูลเพิ่มเติมในคู่มือExcelเอกสาร
-
คุณสามารถสร้างสูตรที่จัดอันดับค่าแบบไดนามิก แล้วกรองตามค่าการจัดอันดับ หรือใช้ค่าการจัดอันดับในฐานะตัวแบ่งส่วนข้อมูล ส่วนที่สองของส่วนนี้จะอธิบายวิธีการสร้างสูตรนี้ แล้วใช้การจัดอันดับนั้นใน ตัวแบ่งส่วนข้อมูล
มีข้อดีและข้อเสียของวิธีการแต่ละวิธี
-
ตัวกรองExcelด้านบนใช้งานง่าย แต่ตัวกรองใช้เพื่อวัตถุประสงค์ในการแสดงเท่านั้น ถ้าข้อมูลอยู่ภายใต้การเปลี่ยนแปลง PivotTable คุณต้องรีเฟรช PivotTable ด้วยตนเองเพื่อดูการเปลี่ยนแปลง ถ้าคุณต้องการใช้การจัดอันดับแบบไดนามิก คุณสามารถใช้ DAX เพื่อสร้างสูตรที่เปรียบเทียบค่ากับค่าอื่นๆ ภายในคอลัมน์ได้
-
สูตร DAX มีประสิทธิภาพมากกว่า นอกจากนี้ ด้วยการเพิ่มค่าการจัดอันดับให้กับตัวแบ่งส่วนข้อมูล คุณสามารถคลิกตัวแบ่งส่วนข้อมูลเพื่อเปลี่ยนจํานวนค่าสูงสุดที่แสดง อย่างไรก็ตาม การคํานวณจะมีค่าใช้จ่ายในการคํานวณและวิธีนี้อาจไม่เหมาะกับตารางที่มีแถวหลายแถว
แสดงเฉพาะสิบรายการบนสุดใน PivotTable
|
เมื่อต้องการแสดงค่าด้านบนหรือด้านล่างใน PivotTable
|
การสั่งซื้อรายการแบบไดนามิกโดยใช้สูตร
หัวข้อต่อไปนี้มีตัวอย่างของวิธีการใช้ DAX เพื่อสร้างการจัดอันดับที่เก็บอยู่ในคอลัมน์ที่คํานวณ เนื่องจากสูตรของ DAX จะได้รับการคํานวณแบบไดนามิก คุณจึงมั่นใจได้ว่าการจัดอันดับนั้นถูกต้องเสมอ แม้ว่าข้อมูลหลักนั้นจะถูกเปลี่ยนแปลงไป นอกจากนี้ เนื่องจากสูตรถูกใช้ในคอลัมน์ที่คํานวณ คุณสามารถใช้การจัดอันดับใน ตัวแบ่งส่วนข้อมูล แล้วเลือก 5 อันดับแรก 10 อันดับแรก หรือ 100 ค่าสูงสุด
