נעים להכיר: משפחת פונקציות X
| תקציר המאמר: בעוד שפונקציית SUM מסכמת נתונים בעמודה ספציפית בלבד, פונקציית SUMX ב-Power BI יכולה לבצע חישובים ברמת השורה (לדוגמה – מחיר כפול כמות), ולאחר מכן לסכם את התוצאות. |
במאמר "עמודה מחושבת או מדד (Measure)?", ראינו כיצד לחשב סך מכירות כולל. עשינו זאת בשני שלבים:
- יצרנו עמודה מחושבת (Total) שהכפילה את המחיר בכמות עבור כל שורה.
- יצרנו מדד (TotalSum) שסכם את עמודת העזר החדשה באמצעות הפונקציה SUM.
ראינו שהשיטה הזו עובדת, אבל יש לה מחיר:
העמודה המחושבת נשמרת פיזית במודל, תופסת מקום יקר בזיכרון (RAM) ומגדילה את גודל הקובץ.
במאמר הזה נלמד כיצד להשיג את אותה התוצאה בדיוק, אך ללא עמודת העזר, ובכך לשמור על מודל נתונים רזה ויעיל יותר.
הכלי המרכזי שיאפשר לנו לעשות זאת הוא לא פונקציה בודדת, אלא משפחה שלמה של פונקציות איטרציה: X-Functions.
ולמי שלא מכיר את המונח – איטרציה היא תהליך חישוב שעובר שורה אחרי שורה בטבלה, ומבצע פעולה מוגדרת, בכל שורה בנפרד.
למה אנחנו צריכים את משפחת פונקציות X?
ב-DAX, רוב פונקציות האגרגציה הפשוטות (כמו SUM, AVERAGE, MAX) נועדו לעבוד על עמודה קיימת אחת.
הפונקציה SUM(Sales[Price]) תסכום את עמודת המחיר, אבל לא תדע לבצע חישוב שמשלב שתי עמודות, כמו Sales[Price] * Sales[Quantity].
במאמר הזה פתרנו את הבעיה באמצעות יצירת עמודת עזר.
עכשיו נלמד איך להשיג את אותה התוצאה, בלי לנפח את המודל עם עמודות עזר שנועדו רק לחישוב ביניים
כאן נכנסות לפעולה פונקציות ה-X
פונקציות אלו (כמו SUMX, AVERAGEX, MAXX, MINX וכו') הן איטרטורים (Iterators), שנועדו במיוחד כדי:
- לקבל טבלה שלמה כארגומנט.
- לעבור על הטבלה הזו שורה אחר שורה.
- לבצע חישוב עבור כל שורה בנפרד (כמו מחיר * כמות).
- לאסוף את כל התוצאות לעמודה וירטואלית זמנית בזיכרון.
- לבצע אגרגציה (סכום, ממוצע, מקסימום וכד') על התוצאות שאספו.
למעשה, פונקציות X מאפשרות לנו לבצע חישוב שורה-אחרי-שורה בתוך מדד, מבלי שניצור עמודה מחושבת פיזית.
התחביר של פונקציות X
התחביר של פונקציות אלו פשוט וזהה ברובו.
ניקח לדוגמה את הפונקציה SUMX:
Msr = SUMX(table,expression)
הארגומנט הראשון (table) הוא תמיד הטבלה שעליה נרצה לעבור, שורה אחרי שורה. בדוגמה שלנו, זו תהיה טבלת Sales.
הארגומנט השני (expression) הוא הביטוי או החישוב שנרצה לבצע עבור כל שורה בודדת באותה טבלה.
הביטוי הזה מחושב בהקשר שורה (Row Context).
שאר הפונקציות במשפחה (כמו AVERAGEX, MAXX וכו') פועלות על פי אותו תחביר בדיוק.
SUM מול SUMX
בואו נחזור אל טבלת המכירות שלנו:
השיטה הישנה: עמודת עזר + מדד שמשתמש בפונקציה SUM:
כדי לקבל את סך המכירות, יצרנו עמודת עזר:
Total = Sales[Quantity] * Sales[Price]
ולאחר מכן יצרנו מדד שסוכם אותה:
TotalSum = SUM(Sales[Total])
זוהי דרך פעולה שמוכרת לנו היטב מעולם האקסל. שם אנו מוסיפים עמודת עזר, ורק לאחר מכן מסכמים אותה (למשל ב-Pivot Table או בפונקציית SUM).
השיטה החדשה: מדד SUMX בלבד:
באמצעות SUMX, אנחנו מדלגים על עמודת העזר וכותבים מדד אחד שעושה הכל:
Total Sales (SUMX) = SUMX(Sales, Sales[Price] * Sales[Quantity])
דרך הפעולה הזו דומה מאוד לפונקציית האקסל SUMPRODUCT, שיודעת להכפיל שתי עמודות זו בזו ולסכום את התוצאה בפעולה אחת.
בואו ננתח את התחביר:
הארגומנט הראשון הוא Sales, שהיא הטבלה שעליה אנחנו רוצים לעבור.
הארגומנט השני הוא הביטוי Sales[Price] * Sales[Quantity]
הפונקציה SUMX תבצע את ההכפלה הזו עבור כל שורה בטבלת Sales בנפרד.
מאחורי הקלעים, SUMX מייצרת "עמודה וירטואלית" זמנית בזיכרון שנראית כך:
לאחר שהיא מסיימת את החישוב עבור כל השורות, היא מחשבת SUM על עמודת החישוב הווירטואלית (200+200+150+300) ומחזירה את התוצאה הסופית: 850,
והיתרון הגדול הוא שהתוצאה הזו תשתנה בהתאם להקשר הסינון
לא רק SUMX: הכירו את כל המשפחה
אותו עיקרון בדיוק חל על שאר חברות המשפחה. ההבדל היחיד הוא פעולת האגרגציה הסופית שהן מבצעות על העמודה הווירטואלית.
AVERAGEX: מחשבת ממוצע של התוצאות.
MAXX: מחזירה את הערך המקסימלי מהתוצאות.
MINX: מחזירה את הערך המינימלי מהתוצאות.
לדוגמה, אם נרצה לדעת מהי שורת המכירה הממוצעת (כלומר, הממוצע של 200, 200, 150 ו-300), נכתוב מדד:
AvgSale = AVERAGEX(Sales, Sales[Price] * Sales[Quantity])
התוצאה תהיה 212.5 (850 / 4).
אם נרצה לדעת מה הייתה העסקה הבודדת הגדולה ביותר בטבלה:
LrgSale = MAXX(Sales,Sales[Price] * Sales[Quantity])
התוצאה תהיה 300 (העסקה של ה-Wallet).
אז מתי נשתמש במה?
- נשתמש בעמודה מחושבת רק במקרים הספציפיים שבהם אנחנו זקוקים לתוצאת החישוב כמאפיין קבוע של השורה,
בעיקר לטובת סינון, קיבוץ או מיון (למשל, להשתמש בתוצאה כציר בגרף או בכלי פריסה-Slicer). - כברירת מחדל, עבור רוב חישובי האגרגציה (סכום, ממוצע) שדורשים חישוב מקדים ברמת השורה (כמו מחיר * כמות),
העדיפו תמיד להשתמש במדד עם פונקציית X. זה שומר על המודל שלכם קטן ומהיר. - שקלו את הביצועים: עמודה מחושבת צורכת RAM (אחסון) ו-CPU בזמן רענון הנתונים, אך היא מהירה מאוד לשליפה בדוח.
מדד עם פונקציית X חוסך ב-RAM, אבל צורך CPU בזמן אמת, בכל פעם שהמשתמש מרענן תצוגה. ב-90% מהמקרים, המדד עדיף,
אבל במודלים ענקיים עם מיליארדי שורות, חישוב SUMX מורכב עלול להאט את הדוחות.
במקרים נדירים אלו, ייתכן שדווקא חישוב מקדים בעמודה מחושבת ייתן חווית משתמש מהירה יותר.
טיפ למתקדמים
במאמר הזה השתמשנו ב-SUMX על טבלה פיזית (Sales). אבל הכוח האמיתי של האיטרטורים מתגלה כשהם פועלים על טבלאות וירטואליות.
טבלאות כאלה יכולות להיווצר לדוגמה מפונקציית טבלה כמו FILTER, אך ההסבר חורג מתחולתו של מאמר זה
שאלות ותשובות על ההבדלים שבין SUM ל-SUMX ב-DAX
שאלה:
מה ההבדל העיקרי בין פונקציית SUM לפונקציית SUMX?
תשובה:
פונקציית SUM שייכת למשפחת פונקציות הצבירה (Aggregate Functions) ומסכמת ערכים בעמודה אחת בלבד.
לעומתה, SUMX היא פונקציית איטרציה (Iterator) העוברת שורה-שורה בטבלה, מבצעת חישוב לכל שורה בנפרד (Row Context), ולבסוף מסכמת את התוצאות.
שאלה:
מתי חובה להשתמש ב-SUMX כדי לקבל תוצאה נכונה?
תשובה:
חובה להשתמש ב-SUMX כאשר נדרש חישוב המערב מספר עמודות באותה שורה (למשל: כמות * מחיר) לפני הסיכום הכולל.
שימוש ב-SUM במקרה זה יסכם קודם את כל הכמויות ואת כל המחירים ורק אז יכפיל ביניהם, מה שיוביל לתוצאה שגויה מתמטית.
שאלה:
אילו פרמטרים מקבלת פונקציית SUMX?
תשובה:
הפונקציה מקבלת שני פרמטרים: הראשון הוא הטבלה (Table) עליה רוצים לבצע את האיטרציה, והשני הוא הביטוי (Expression) – הנוסחה שתחושב עבור כל שורה בטבלה זו.