סעיף זה מתאר כיצד ליצור מסננים בתוך נוסחאות Data Analysis Expressions (DAX). באפשרותך ליצור מסננים בתוך נוסחאות, כדי להגביל את הערכים מתוך נתוני המקור המשמשים בחישובים. לשם כך עליך להגדיר טבלה כקלט לנוסחה ולאחר מכן להגדיר ביטוי מסנן. ביטוי המסנן שאתה מספק משמש לביצוע שאילתה על הנתונים ולהחזרת קבוצת משנה בלבד של נתוני המקור. המסנן מוחל באופן דינאמי בכל פעם שאתה מעדכן את תוצאות הנוסחה, בהתאם להקשר הנוכחי של הנתונים שלך.
במאמר זה
יצירת מסנן בטבלה הנמצאת בשימוש בנוסחה
באפשרותך להחיל מסננים בנוסחאות המשתמשות בטבלה כקלט. במקום להזין שם טבלה, השתמש בפונקציה FILTER כדי להגדיר קבוצת משנה של שורות מהטבלה שצוינה. לאחר מכן, תת-קבוצה זו מועברת לפונקציה אחרת, עבור פעולות כגון צבירות מותאמות אישית.
לדוגמה, נניח שיש לך טבלת נתונים המכילה פרטי הזמנות לגבי משווקים, וברצונך לחשב כמה כל משווק מכר. עם זאת, אתה רוצה להציג את סכום המכירות רק עבור אותם משווקים שמכרו יחידות מרובות של המוצרים בעלי הערך הגבוה יותר שלך. הנוסחה הבאה, המבוססת על חוברת העבודה לדוגמה של DAX, מציגה דוגמה אחת לאופן שבו באפשרותך ליצור חישוב זה באמצעות מסנן:
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
החלק הראשון של הנוסחה מציין את אחת מפונקציות הצבירה של Power Pivot, שמקבלת טבלה כארגומנט. הפונקציה SUMX מחשבת סכום בטבלה.
החלק השני של הנוסחה
FILTER(table, expression),מסבירSUMXבאילו נתונים להשתמש.SUMXדורש טבלה או ביטוי שתוצאתם היא טבלה. כאן, במקום להשתמש בכל הנתונים בטבלה, השתמש בפונקציהFILTERכדי לציין באילו מהשורות מהטבלה ייעשה שימוש.
ביטוי המסנן כולל שני חלקים: החלק הראשון נותן שם לטבלה שעליה חל המסנן. החלק השני מגדיר ביטוי שישמש כתנאי המסנן. במקרה זה, אתה מסנן לפי משווקים שמכרו יותר מ- 5 יחידות ומוצרים שעולים יותר מ- 100 ש"ח. האופרטור && הוא אופרטור AND לוגי, המציין ששני חלקי התנאי חייבים להיות True כדי שהשורה תשתייך לערכת המשנה המסוננת.החלק השלישי של הנוסחה מורה
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 יצירת הצירוף החיצוני השמאלי הטבעי של כל הטבלאות.
לדוגמה, נניח שיש לך את הטבלאות Sales ו- Products, וברצונך ליצור נוסחה שתחשב את סכום המכירות של המוצר הנוכחי חלקי המכירות של כל המוצרים. עליך לקחת בחשבון את העובדה שאם הנוסחה נמצאת בשימוש במידה, המשתמש ב- 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 .