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

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

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

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

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

הכרת הקשר

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

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

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

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

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

לראש הדף

הקשר שורה

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

לדוגמה, נניח שאתה יוצר עמודה מחושבת, = [Freight] + [מס],

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

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

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

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

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

הקשרי שורה מרובים

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

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

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

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

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

לקבלת הנחיות מפורטות בנוגע לנוסחה זו, ראה הפונקציה EARLIER.

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

לראש הדף

הקשר שאילתה

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

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

לדוגמה, נניח שתיצור נוסחה פשוטה זו שמסכמת את הערכים בעמודה Profit של table:=SUM('Sales'[Profit]) המכירות .

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

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

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

לראש הדף

הקשר מסנן

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

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

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

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

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

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

לראש הדף

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

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

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

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

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

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

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

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

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

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

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

לראש הדף

שלמות הקשרים

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

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

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

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

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

הכרת המושג 'חבר לא ידוע'

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

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

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

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

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

לראש הדף

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

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×