הקשר בנוסחאות DAX

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

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

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

הבנת ההקשר

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

קיימים סוגים שונים של הקשרים: הקשר שורה, הקשר שאילתה והקשר מסנן.

ניתן להתייחס להקשר שורה כאל "השורה הנוכחית". אם יצרת עמודה מחושבת, הקשר השורה מורכב מהערכים בכל שורה בודדת ומערכים בעמודות הקשורות לשורה הנוכחית. קיימות גם כמה פונקציות (EARLIER ו- EARLIEST) המקבלות ערך מהשורה הנוכחית ולאחר מכן משתמשות בערך זה בעת ביצוע פעולה בטבלה שלמה.

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

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

לראש הדף

הקשר שורה

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

לדוגמה, נניח שאתה יוצר עמודה מחושבת, =[Freight] + [Tax], המחברת שתי עמודות מאותה טבלה. נוסחה זו פועלת כמו נוסחאות בטבלת Excel, אשר מפנות באופן אוטומטי לערכים מאותה שורה. שים לב שטבלאות שונות מטווחים: אין באפשרותך להפנות לערך מהשורה שלפני השורה הנוכחית באמצעות סימון טווח, ואין באפשרותך להפנות לערך בודד שרירותי כלשהו בטבלה או בתא. תמיד עליך לעבוד עם טבלאות ועמודות.

הקשר שורה עוקב באופן אוטומטי אחר קשרי הגומלין בין הטבלאות כדי לקבוע אילו שורות בטבלאות קשורות משויכות לשורה הנוכחית.

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

= [Freight] + RELATED('Region'[TaxRate])

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

הקשר שורות מרובות

בנוסף, DAX כולל פונקציות שמבצעות איטראציה לחישובים בטבלה. פונקציות אלה יכולות לכלול שורות נוכחיות מרובות והקשרי שורה נוכחית מרובות. במונחי תיכנות, באפשרותך ליצור נוסחאות החוזרות על פני לולאה פנימית וחיצונית.

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

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

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

=MAXX(FILTER(Sales,[ProdKey]=EARLY([ProdKey])),Sales[OrderQty])

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

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

לראש הדף

הקשר שאילתה

הקשר שאילתה מתייחס לקבוצת המשנה של הנתונים שמאוחזרים באופן משתמע עבור נוסחה. בעת שחרור שדה מידה או ערך אחר בתא ב- PivotTable, מנגנון Power Pivot בודק את כותרות השורות והעמודות, כלי הפריסה ומסנני הדוחות כדי לקבוע את ההקשר. לאחר מכן, Power Pivot מבצע את החישובים הדרושים כדי לאכלס כל תא ב- PivotTable. ערכת הנתונים המאוחזרת היא הקשר השאילתה עבור כל תא.

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

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

=SUM('Sales'[Profit])

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

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

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

לראש הדף

הקשר מסנן

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

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

לקבלת מידע נוסף אודות אופן יצירת מסננים בתוך נוסחאות, עיין בפונקציות הסינון.

לקבלת דוגמה לאופן שבו ניתן לנקות מסננים כדי ליצור סכומים כוללים, ראה את הפונקציה ALL.

לקבלת דוגמאות לאופן הניקוי וההחלה של מסננים באופן סלקטיבי בתוך נוסחאות, עיין בפונקציה ALLEXCEPT .

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

לראש הדף

קביעת הקשר בנוסחאות

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

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

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

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

דוגמאות של הקשר בנוסחאות

• הפונקציה RELATED מרחיבה את ההקשר של השורה הנוכחית כדי לכלול ערכים בעמודה קשורה. הדבר מאפשר לך לבצע בדיקות מידע. הדוגמה בנושא זה ממחישה את האינטראקציה בין סינון להקשר שורה.
• הפונקציה FILTER מאפשרת לך לציין את השורות שיש לכלול בהקשר הנוכחי. הדוגמאות בנושא זה גם ממחישות כיצד להטביע מסננים בתוך פונקציות אחרות שמבצעות צבירות.
• הפונקציה ALL מגדירה הקשר בתוך נוסחה. באפשרותך להשתמש בו כדי לעקוף מסננים המוחלים כתוצאה מהקשר שאילתה.
• הפונקציה ALLEXCEPT מאפשרת לך להסיר את כל המסננים למעט אחד שאתה מציין. שני הנושאים כוללים דוגמאות שמדריכות אותך בבניית נוסחאות והבנת הקשרים מורכבים.
• הפונקציות EARLIER ו- EARLY מאפשרות לך לעבור בלולאה בין טבלאות על-ידי ביצוע חישובים, תוך הפניה לערך מלולאה פנימית. אם אתה מכיר את המושג רקורסיה ועם לולאות פנימיות וחיצוניות, תעריך את העוצמה שהפונקציות EARLIER ו- EARLY מספקות. אם אינך מכיר מושגים אלה, עליך לבצע את השלבים בדוגמה בקפידה כדי לראות כיצד נעשה שימוש בהקשרים הפנימיים והחיצוניים בחישובים.

לראש הדף

שלמות הקשרים

סעיף זה דן בכמה מושגים מתקדמים הקשורים לערכים חסרים בטבלאות Power Pivot המחוברות באמצעות קשרי גומלין. מקטע זה עשוי להיות שימושי עבורך אם יש לך חוברות עבודה עם טבלאות מרובות ונוסחאות מורכבות וברצונך לקבל עזרה בהבנת התוצאות.

אם אינך בקי במושגים של נתונים יחסיים, מומלץ לקרוא תחילה את נושא המבוא ' מבט כולל על קשרי גומלין'.

שלמות הקשרים וקשרי גומלין של Power Pivot

Power Pivot אינו דורש אכיפה של שלמות הקשרים בין שתי טבלאות כדי להגדיר קשר גומלין חוקי. במקום זאת, נוצרת שורה ריקה בקצה ה"יחיד" של כל קשר גומלין של יחיד לרבים, המשמשת לטיפול בכל השורות שאינן תואמות מהטבלה הקשורה. הוא פועל ביעילות כמו צירוף חיצוני של SQL.

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

להבין את החבר הלא ידוע

המושג של חבר לא ידוע מוכר לך בוודאי אם עבדת עם מערכות מסדי נתונים רב-ממדיות, כגון SQL Server Analysis Services. אם המונח חדש לך, הדוגמה הבאה מסבירה מהו האיבר הלא ידוע וכיצד הוא משפיע על חישובים.

נניח שאתה יוצר חישוב המסכם מכירות חודשיות עבור כל חנות, אך בעמודה בטבלה Sales חסר ערך עבור שם החנות. בהתחשב בעובדה שהטבלאות עבור Store ו - Sales מחוברות באמצעות שם החנות, מה היית מצפה שיקרה בנוסחה? כיצד על ה- PivotTable לקבץ או להציג את נתוני המכירות שאינם קשורים לחנות קיימת?

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

טיפול בערכים ריקים לעומת השורה הריקה

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

לראש הדף