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