ส่วนนี้จะอธิบายวิธีการสร้างตัวกรองภายในสูตร 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 บริบทอาจส่งผลกระทบต่อผลลัพธ์ของสูตรได้ ถ้าคุณกําลังทํางานในตาราง PowerPivot บริบทคือแถวปัจจุบันและค่าของแถวนั้น ถ้าคุณกําลังทํางานใน 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