ส่วนนี้จะอธิบายวิธีการสร้างตัวกรองภายในสูตร Data Analysis Expressions (DAX) คุณสามารถสร้างตัวกรองภายในสูตร เพื่อจํากัดค่าจากแหล่งข้อมูลที่ใช้ในการคํานวณ คุณทําเช่นนี้โดยการระบุตารางเป็นข้อมูลที่ป้อนลงในสูตร แล้วกําหนดนิพจน์ตัวกรอง นิพจน์ตัวกรองที่คุณระบุจะใช้ในการสอบถามข้อมูลและส่งกลับเฉพาะชุดย่อยของข้อมูลต้นฉบับ ตัวกรองจะถูกนําไปใช้แบบไดนามิกในแต่ละครั้งที่คุณอัปเดตผลลัพธ์ของสูตร โดยขึ้นอยู่กับบริบทปัจจุบันของข้อมูลของคุณ
ในบทความนี้
การสร้างตัวกรองในตารางที่ใช้ในสูตร
คุณสามารถใช้ตัวกรองในสูตรที่นําตารางไปใช้เป็นการป้อนข้อมูลได้ แทนที่จะใส่ชื่อตาราง ให้คุณใช้ฟังก์ชัน FILTER เพื่อกําหนดชุดย่อยของแถวจากตารางที่ระบุ เซตย่อยนั้นจะถูกส่งผ่านไปยังฟังก์ชันอื่น สําหรับการดําเนินการต่างๆ เช่น การรวมแบบกําหนดเอง
ตัวอย่างเช่น สมมติว่าคุณมีตารางข้อมูลที่มีข้อมูลการสั่งซื้อเกี่ยวกับผู้จําหน่าย และคุณต้องการคํานวณจํานวนตัวแทนจําหน่ายแต่ละรายที่ขายได้ อย่างไรก็ตาม คุณต้องการแสดงยอดขายสําหรับผู้จําหน่ายที่ขายผลิตภัณฑ์ที่มีมูลค่าสูงกว่าหลายหน่วย สูตรต่อไปนี้ซึ่งยึดตามเวิร์กบุ๊กตัวอย่าง DAX จะแสดงตัวอย่างหนึ่งของวิธีที่คุณสามารถสร้างการคํานวณนี้โดยใช้ตัวกรอง
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[ปริมาณ] > 5 && 'ResellerSales_USD'[ProductStandardCost_USD] > 100), 'ResellerSales_USD'[SalesAmt] )-
ส่วนแรกของสูตรจะระบุหนึ่งใน Power Pivot ฟังก์ชันการรวม ซึ่งใช้ตารางเป็นอาร์กิวเมนต์ SUMX จะคํานวณผลรวมของตาราง
-
ส่วนที่สองของสูตร FILTER(table, expression),จะบอก SUMX ว่าจะใช้ข้อมูลใด SUMX ต้องใช้ตารางหรือนิพจน์ที่ให้ผลลัพธ์ในตาราง ที่นี่ แทนที่จะใช้ข้อมูลทั้งหมดในตาราง ให้คุณใช้ฟังก์ชัน FILTER เพื่อระบุแถวที่จะใช้จากตาราง
นิพจน์ตัวกรองมีสองส่วน: ส่วนแรกจะตั้งชื่อตารางที่ตัวกรองนําไปใช้ ส่วนที่สองกําหนดนิพจน์ที่จะใช้เป็นเงื่อนไขตัวกรอง ในกรณีนี้คุณกําลังกรองผู้จําหน่ายที่ขายสินค้ามากกว่า 5 หน่วยและผลิตภัณฑ์ที่มีราคามากกว่า $100 ตัวดําเนินการ && เป็นตัวดําเนินการ AND เชิงตรรกะ ซึ่งบ่งชี้ว่าทั้งสองส่วนของเงื่อนไขต้องเป็นจริงสําหรับแถวที่จะเป็นสมาชิกของชุดย่อยที่ถูกกรอง
-
ส่วนที่สามของสูตรจะบอกฟังก์ชัน SUMX ว่าค่าใดที่ควรนํามารวม ในกรณีนี้ คุณกําลังใช้เฉพาะยอดขายเท่านั้น
โปรดสังเกตว่าฟังก์ชัน เช่น FILTER ซึ่งส่งกลับตาราง จะไม่ส่งกลับตารางหรือแถวโดยตรง แต่จะฝังอยู่ในฟังก์ชันอื่นเสมอ สําหรับข้อมูลเพิ่มเติมเกี่ยวกับ FILTER และฟังก์ชันอื่นๆ ที่ใช้สําหรับการกรอง รวมถึงตัวอย่างเพิ่มเติม ให้ดูที่ ฟังก์ชันตัวกรอง (DAX)
หมายเหตุ: นิพจน์ตัวกรองจะได้รับผลกระทบจากบริบทที่ใช้ ตัวอย่างเช่น ถ้าคุณใช้ตัวกรองในการวัด และใช้การวัดใน PivotTable หรือ PivotChart ชุดย่อยของข้อมูลที่ส่งกลับอาจได้รับผลกระทบจากตัวกรองหรือตัวแบ่งส่วนข้อมูลที่ผู้ใช้นําไปใช้ใน PivotTable เพิ่มเติม สําหรับข้อมูลเพิ่มเติมเกี่ยวกับบริบท ให้ดูที่ บริบทในสูตร DAX
ตัวกรองที่เอารายการที่ซ้ํากันออก
นอกจากการกรองค่าที่ระบุแล้ว คุณสามารถส่งกลับชุดค่าที่ไม่ซ้ํากันจากตารางหรือคอลัมน์อื่นได้ ซึ่งจะเป็นประโยชน์เมื่อคุณต้องการนับจํานวนค่าที่ไม่ซ้ํากันในคอลัมน์ หรือใช้รายการของค่าที่ไม่ซ้ํากันสําหรับการดําเนินการอื่นๆ DAX มีสองฟังก์ชันสําหรับการส่งกลับค่าที่แตกต่างกัน ได้แก่ ฟังก์ชัน DISTINCT และฟังก์ชัน VALUES
-
ฟังก์ชัน DISTINCT จะตรวจสอบคอลัมน์เดียวที่คุณระบุเป็นอาร์กิวเมนต์ของฟังก์ชัน และส่งกลับคอลัมน์ใหม่ที่มีเฉพาะค่าที่แตกต่างกัน
-
ฟังก์ชัน VALUES ยังส่งกลับรายการของค่าที่ไม่ซ้ํากัน แต่ยังส่งกลับสมาชิกที่ไม่รู้จัก ซึ่งจะเป็นประโยชน์เมื่อคุณใช้ค่าจากสองตารางที่มีความสัมพันธ์รวมกัน และค่าหายไปจากหนึ่งตารางและแสดงในอีกตารางหนึ่ง สําหรับข้อมูลเพิ่มเติมเกี่ยวกับสมาชิกที่ไม่รู้จัก ให้ดู บริบทในสูตร DAX
ฟังก์ชันทั้งสองฟังก์ชันเหล่านี้จะส่งกลับทั้งคอลัมน์ของค่า แต่ฟังก์ชันทั้งสองอย่างจะส่งกลับค่าที่ต่างกัน ดังนั้น คุณจะใช้ฟังก์ชันเพื่อรับรายการของค่าที่ถูกส่งผ่านไปยังฟังก์ชันอื่น ตัวอย่างเช่น คุณสามารถใช้สูตรต่อไปนี้เพื่อรับรายการผลิตภัณฑ์ที่ไม่ซ้ํากันที่ขายโดยผู้จําหน่ายเฉพาะ โดยใช้คีย์ผลิตภัณฑ์ที่ไม่ซ้ํากัน แล้วนับผลิตภัณฑ์ในรายการนั้นโดยใช้ฟังก์ชัน COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
บริบทมีผลต่อตัวกรองอย่างไร
เมื่อคุณเพิ่มสูตร DAX ลงใน PivotTable หรือ PivotChart ผลลัพธ์ของสูตรอาจได้รับผลกระทบจากบริบท ถ้าคุณกําลังทํางานในตาราง Power Pivot บริบทจะเป็นแถวปัจจุบันและค่าของแถวนั้น ถ้าคุณกําลังทํางานใน PivotTable หรือ PivotChart บริบทหมายถึงชุดหรือชุดย่อยของข้อมูลที่กําหนดโดยการดําเนินการ เช่น การแบ่งส่วน หรือการกรอง การออกแบบ PivotTable หรือ PivotChart ยังกําหนดบริบทของตนเองอีกด้วย ตัวอย่างเช่น ถ้าคุณสร้าง PivotTable ที่จัดกลุ่มยอดขายตามภูมิภาคและปี เฉพาะข้อมูลที่นําไปใช้กับภูมิภาคและปีเหล่านั้นเท่านั้นที่จะปรากฏใน PivotTable ดังนั้น การวัดใดๆ ที่คุณเพิ่มลงใน PivotTable จะถูกคํานวณในบริบทของส่วนหัวของคอลัมน์และแถวและตัวกรองใดๆ ในสูตรการวัด
สําหรับข้อมูลเพิ่มเติม ให้ดูที่ บริบทในสูตร DAX
การเอาตัวกรองออก
เมื่อทํางานกับสูตรที่ซับซ้อน คุณอาจต้องการทราบว่าตัวกรองปัจจุบันคืออะไร หรือคุณอาจต้องการปรับเปลี่ยนส่วนตัวกรองของสูตร DAX มีฟังก์ชันหลายฟังก์ชันที่ช่วยให้คุณสามารถเอาตัวกรองออก และควบคุมว่าจะเก็บคอลัมน์ใดไว้เป็นส่วนหนึ่งของบริบทตัวกรองปัจจุบัน ส่วนนี้จะให้ภาพรวมของผลกระทบของฟังก์ชันเหล่านี้ต่อผลลัพธ์ในสูตร
การแทนที่ตัวกรองทั้งหมดด้วยฟังก์ชัน ALL
คุณสามารถใช้ฟังก์ชัน ALL เพื่อแทนที่ตัวกรองใดๆ ที่นําไปใช้ก่อนหน้านี้ และส่งกลับแถวทั้งหมดในตารางไปยังฟังก์ชันที่กําลังดําเนินการรวมหรือการดําเนินการอื่นๆ ถ้าคุณใช้คอลัมน์อย่างน้อยหนึ่งคอลัมน์ แทนที่จะใช้เป็นตาราง เพื่อเป็นอาร์กิวเมนต์ใน ALLฟังก์ชัน ALL จะส่งกลับแถวทั้งหมด โดยไม่สนใจตัวกรองบริบทใดๆ
หมายเหตุ: ถ้าคุณคุ้นเคยกับศัพท์ของฐานข้อมูลเชิงสัมพันธ์ คุณสามารถนึกถึง ALL ว่าเป็นการสร้างการรวมภายนอกด้านซ้ายอย่างเป็นธรรมชาติของตารางทั้งหมด
ตัวอย่างเช่น สมมติว่าคุณมีตาราง ยอดขายและผลิตภัณฑ์ และคุณต้องการสร้างสูตรที่จะคํานวณผลรวมของยอดขายสําหรับผลิตภัณฑ์ปัจจุบันที่หารด้วยยอดขายสําหรับผลิตภัณฑ์ทั้งหมด คุณต้องคํานึงถึงข้อเท็จจริงที่ว่า ถ้าใช้สูตรในการวัด ผู้ใช้ PivotTable อาจใช้ตัวแบ่งส่วนข้อมูลเพื่อกรองผลิตภัณฑ์เฉพาะด้วยชื่อผลิตภัณฑ์บนแถว ดังนั้น เมื่อต้องการรับค่าที่แท้จริงของตัวส่วนโดยไม่คํานึงถึงตัวกรองหรือตัวแบ่งส่วนข้อมูล คุณต้องเพิ่มฟังก์ชัน ALL เพื่อแทนที่ตัวกรองใดๆ สูตรต่อไปนี้เป็นตัวอย่างหนึ่งของวิธีใช้ ALL เพื่อแทนที่ผลกระทบของตัวกรองก่อนหน้า:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
-
ส่วนแรกของสูตร SUM (Sales[Amount]) จะคํานวณตัวเศษ
-
ผลรวมจะคํานึงถึงบริบทปัจจุบัน ซึ่งหมายความว่าถ้าคุณเพิ่มสูตรลงในคอลัมน์จากการคํานวณ บริบทของแถวจะถูกนําไปใช้ และถ้าคุณเพิ่มสูตรลงใน PivotTable เป็นการวัด ตัวกรองใดๆ ที่นําไปใช้ใน PivotTable (บริบทตัวกรอง) จะถูกนําไปใช้
-
ส่วนที่สองของสูตรจะคํานวณตัวหาร ฟังก์ชัน ALL จะแทนที่ตัวกรองใดๆ ที่อาจนําไปใช้กับตาราง Products
สําหรับข้อมูลเพิ่มเติม รวมถึงตัวอย่างโดยละเอียด ให้ดูที่ ฟังก์ชัน ALL
การแทนที่ตัวกรองเฉพาะด้วยฟังก์ชัน ALLEXCEPT
ฟังก์ชัน ALLEXCEPT ยังแทนที่ตัวกรองที่มีอยู่ด้วย แต่คุณสามารถระบุได้ว่าควรเก็บตัวกรองที่มีอยู่บางตัวไว้ คอลัมน์ที่คุณตั้งชื่อเป็นอาร์กิวเมนต์ของฟังก์ชัน ALLEXCEPT จะระบุว่าคอลัมน์ใดที่จะถูกกรองต่อไป ถ้าคุณต้องการแทนที่ตัวกรองจากคอลัมน์ส่วนใหญ่ แต่ไม่ใช่ทั้งหมด ALLEXCEPT จะสะดวกกว่า ALL ฟังก์ชัน ALLEXCEPT จะมีประโยชน์เป็นพิเศษเมื่อคุณสร้าง PivotTable ที่อาจถูกกรองในหลายคอลัมน์ และคุณต้องการควบคุมค่าที่ใช้ในสูตร สําหรับข้อมูลเพิ่มเติม รวมถึงตัวอย่างโดยละเอียดของวิธีใช้ ALLEXCEPT ใน PivotTable ให้ดูที่ ฟังก์ชัน ALLEXCEPT