סעיף זה מתאר כיצד ליצור מסננים בתוך נוסחאות 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 מחזירה גם רשימה של ערכים ייחודיים, אך גם מחזירה את החבר Unknown. פעולה זו שימושית כאשר אתה משתמש בערכים משתי טבלאות המצורפות על-ידי קשר גומלין, וערך חסר בטבלה אחת ומציג בטבלה השניה. לקבלת מידע נוסף אודות החבר 'לא ידוע', ראה הקשר בנוסחאות 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.
