אם אתם עובדים עם Power BI או עם Power Pivot באקסל או עם Analysis Services, סביר להניח שפגשתם את DAX (Data Analysis eXpressions).

במבט ראשון, השפה נראית פשוטה ודומה לנוסחאות באקסל. אתם כותבים SUM וזה עובד. אבל מהר מאוד, ככל שנכנסים לעומק, מתחילים להיתקל בשגיאות מסתוריות ותוצאות שלא מסתדרות עם ההיגיון.

ב Power BI:

באקסל:

 

המסע הזה שעובר מתסכול לשליטה אינו עוסק בשינון של עוד פונקציות. הוא עוסק בהבנת כללי המנוע הנסתרים: איך SUM היא למעשה איטרטור סודי, מדוע פעולת כפל פשוטה נכשלת כמדד (measure) אך עובדת בעמודה, וכיצד CALCULATE פועלת כשליטה הכל-יכולה של ההקשר. מאמר זה יחשוף חמישה עקרונות יסוד כאלה, שהבנתם תפתח בפניכם רמה חדשה של שליטה ב-DAX.

——————————————————————————–

1. ישנם שני "הקשרים" שונים, וההבחנה ביניהם קריטית

המושג "הקשרי הערכה" (Evaluation Contexts) הוא עמוד התווך של DAX. כמעט כל שגיאה או תוצאה בלתי צפויה נובעת מאי הבנה של הנושא הזה. ישנם שני סוגי הקשרים מרכזיים:

• הקשר סינון (Filter Context): זוהי "הסביבה" של כל הפילטרים הפעילים על מודל הנתונים שלכם ברגע נתון, הנוצרת בדרך כלל על ידי בחירות המשתמש בפילטרים (slicers), השורות והעמודות במטריצה, או פילטרים שהוחלו על ויזואל. בקצרה, הקשר הסינון מסנן טבלאות.
• הקשר שורה (Row Context): הקשר זה קיים כאשר DAX מבצע איטרציה על טבלה, שורה אחר שורה, ומבצע חישוב עבור כל שורה בנפרד.

הדוגמה הקלאסית להבדל היא הביטוי הבא: Orders[Quantity] * Orders[Price].

אם תשימו את הנוסחה הזו בתוך עמודה מחושבת (Calculated Column) בטבלת Orders, היא תעבוד מצוין. הסיבה לכך היא שעמודה מחושבת פועלת תמיד תחת הקשר שורה טבעי – היא עוברת שורה-שורה ומחשבת את התוצאה.

אבל אם תנסו להשתמש באותו ביטוי בדיוק כדי ליצור מדד (Measure), תקבלו שגיאה. הודעת השגיאה הרשמית, "cannot determine a single value for column…", היא קריפטית לשמצה. הודעת שגיאה כנה יותר הייתה פשוט אומרת: "אין לי הקשר שורה. אתה מבקש ממני להכפיל מחיר בכמות, אבל אני מסתכל על כל העמודה בבת אחת." הפנמה של רעיון זה היא המפתח לפתרון רוב הבעיות הנפוצות ב-DAX.

2. הפונקציה SUM היא בעצם "קיצור דרך" ל-SUMX

רובנו מתחילים את דרכנו ב-DAX עם פונקציית האגרגציה SUM. היא פשוטה ומוכרת. אבל האם ידעתם ש-SUM היא למעשה "Syntax Sugar" (קיצור תחבירי) לפונקציית האיטרטור SUMX?

כאשר אתם כותבים את הקוד הבא:

SUM ( Sales[Quantity] )

מנוע DAX מתרגם אותו באופן פנימי לביטוי הזה:

SUMX ( Sales, Sales[Quantity] )

התרגום הזה הוא קריטי, מכיוון ש-SUMX, כפונקציית איטרטור, מציגה הקשר שורה במקום שבו הוא לא היה קיים קודם לכן. זהו המנגנון המאפשר לאגרגציה לפעול על שורות בודדות לפני סיכום התוצאה – קישור ישיר בחזרה לשני "העולמות" של DAX שדנו בהם זה עתה.

3. CALCULATE היא הפונקציה החזקה והחשובה ביותר ב-DAX

אם יש פונקציה אחת שאתם חייבים להכיר לעומק כדי לשלוט ב-DAX, זוהי CALCULATE. היא מתוארת כמפורש בתור "הפונקציה החשובה ביותר ב-DAX".

תפקידה המרכזי של CALCULATE הוא לשנות את הקשר הסינון (Filter Context) לפני שהיא מחשבת ביטוי כלשהו. היא יכולה להוסיף, להסיר או לשנות פילטרים קיימים. חשוב להבין, זוהי הפונקציה היחידה ב-DAX בעלת הכוח לבצע "מעבר הקשרים" זה. כל פונקציה אחרת פועלת בתוך ההקשר שניתן לה; CALCULATE היא היחידה שיכולה לבנות הקשר חדש לחלוטין.

לדוגמה, הביטוי הבא מחשב את סכום המכירות הכולל תוך התעלמות מכל פילטר שהוחל על צבע המוצר:

SalesAllColors :=
CALCULATE ( [Sales Amount], ALL ( Product[Color] ) )

CALCULATE לוקחת את המדד [Sales Amount], מסירה את הפילטר מעמודת הצבע באמצעות ALL, ורק אז מחשבת את התוצאה.

4. DAX מוסיף "שורה ריקה" נסתרת לטבלאות כדי להבטיח שלמות נתונים

זהו אחד המנגנונים הפחות מוכרים של DAX. במודל נתונים עם קשרי גומלין, מנוע DAX עשוי להוסיף "שורה ריקה" (blank row) נסתרת לטבלה שנמצאת בצד ה"אחד" של הקשר, לדוגמה, בטבלת המוצרים (Products) בקשר גומלין עם טבלת המכירות (Sales).

למה זה קורה? כדי להבטיח שלמות ייחוסית (referential integrity). אם בטבלת המכירות (צד ה"רבים") יש מוצר שלא קיים בטבלת המוצרים (צד ה"אחד"), השורה הריקה הזו משמשת כמציין מקום כדי למנוע שגיאות.

העובדה הזו הופכת להיות חשובה כאשר משתמשים בפונקציות כמו VALUES, המחזירה את הערכים הייחודיים הנראים בהקשר הסינון הנוכחי. זה יכול לגרום לספירות הייחודיות שלכם, כמו COUNTROWS ( VALUES ( ... ) ), להיות שגויות באחד – באג עדין ומתסכל שכמעט בלתי אפשרי למצוא אם לא מודעים לקיומה של שורה נסתרת זו.

5. שימוש ב-VAR וב-DIVIDE יכול לשפר משמעותית את הקוד שלכם

מעבר להבנת התיאוריה, ישנן שתי טכניקות פשוטות שיכולות להפוך את הקוד שלכם לנקי, קריא ויעיל יותר באופן מיידי.

• השתמשו ב-DIVIDE במקום ב-IF: כאשר אתם צריכים לחלק שני מספרים ורוצים להימנע משגיאת חלוקה באפס, הדרך האינטואיטיבית היא להשתמש ב-IF. אבל יש דרך טובה יותר. הפונקציה DIVIDE לא רק נקייה יותר לקריאה, אלא גם "מהירה יותר" מאשר שימוש ב-IF.
• הגדירו משתנים עם VAR: שימוש במשתנים הוא "מאוד שימושי כדי להימנע מחזרה על תת-ביטויים בקוד". כאשר יש לכם חישוב מורכב שחוזר על עצמו מספר פעמים בנוסחה, הגדרתו כמשתנה משפרת את הקריאות, מקלה על התחזוקה, ויכולה לשפר ביצועים על ידי כך שהיא מבטיחה שהחישוב יתבצע פעם אחת בלבד.

——————————————————————————–

סיכום: לחשוב כמו מנוע ה-DAX

שליטה אמיתית ב-DAX מגיעה לא רק מהיכרות עם רשימת הפונקציות, אלא מהבנה עמוקה של העקרונות הפנימיים שמפעילים אותן. כפי שראינו, מאחורי פונקציות פשוטות כמו SUM מסתתרת לוגיקה של איטרציה, והתנהגות של כל מדד תלויה באופן קריטי בהקשר הסינון והשורה שבה הוא מחושב.

בפעם הבאה שאתם נתקלים בבעיה או כותבים מדד חדש, נסו לחשוב על העקרונות הללו. איזו מהתובנות הללו גרמה לכם להסתכל אחרת על מדד שעבדתם עליו לאחרונה, ואיך תיישמו אותה בפעם הבאה?

שלומי פוסטלניק הוא מייקרוסופט MVP, מומחה אקסל, אשר מעביר הדרכות פנים ארגוניות, בחברות וארגונים ובונה פתרונות בהתאמה אישית, על בסיס תוכנת אקסל, ו Power BI.
להדרכות או פתרונות אקסל  או Power BI צור קשר למייל shlomi@uniquetech.co.il

או שלח לי הודעה מ >> כאן <<