תרחישים של DAX ב- Power Pivot

יצירת חישובים מותאמים אישית עבור PivotTable

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

• לחשב

• חישוב טבלת

החלת מסנן על נוסחה

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

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

• מסנן

• 1000000000

• ריקון ראשון

• ריקון אחרון

הסר מסננים באופן סלקטיבי כדי ליצור יחס דינאמי

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

• מה היתה תרומת המכירות של המוצר הנוכחי לסך המכירות בשנה?

• כמה תרמה החטיבה הזאת לרווחים הכוללים עבור כל שנות ההפעלה, בהשוואה למחלקות אחרות?

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

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

• כל

• מעיין ת'ם

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

• ISBLANK

• ISERROR

שימוש בערך בלולאה הוצאה

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

• הפונקציה EARLIER

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

יצירת עמודת מפתח באמצעות שרשור

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

• הפונקציה CONCATENATE

חיבור תאריך בהתבסס על חלקי תאריך שחולצו מתאריך טקסט

Power Pivot משתמש בסוג נתונים של תאריך/שעה של SQL Server כדי לעבוד עם תאריכים; לכן, אם הנתונים החיצוניים שלך מכילים תאריכים המעוצבים באופן שונה - לדוגמה, אם התאריכים שלך נכתבו בתבנית תאריך אזורית שאינו מזוהה על-ידי מנוע הנתונים של Power Pivot, או אם הנתונים שלך משתמשים במפתחות חלופיים של מספרים שלמים -- ייתכן שתצטרך להשתמש בנוסחת DAX כדי לחלץ את חלקי התאריך ולאחר מכן לחבר את החלקים לייצוג חוקי של תאריך/שעה.

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

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

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

• הפונקציה DATEVALUE

• הפונקציה TIMEVALUE

• הפונקציה DATE

הגדרת תבנית תאריך או מספר מותאמת אישית

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

• תבניות מספריות מוגדרות מראש עבור הפונקציה FORMAT

• תבניות מספריות מותאמות אישית עבור הפונקציה FORMAT

• תבניות תאריך ושעה מוגדרות מראש עבור הפונקציה FORMAT

• תבניות תאריך ושעה מותאמות אישית עבור הפונקציה FORMAT

שינוי סוגי נתונים באמצעות נוסחה

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

• כדי להמיר תאריך או מחרוזת מספר למספר, הכפל ב- 1.0. לדוגמה, הנוסחה הבאה מחשבת את התאריך הנוכחי פחות 3 ימים ולאחר מכן מחשבת את ערך המספר השלם התואם.

  =(TODAY()-3)*1.0

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

  =""& TODAY()

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

המרת מספרים ממשיים למספרים שלמים

• הפונקציה ROUND

• הפונקציה CEILING

• הפונקציה FLOOR

  המרת מספרים ממשיים, מספרים שלמים או תאריכים במחרוזות

• הפונקציה FIXED

• הפונקציה FORMAT

  המרת מחרוזות למספרים או לתאריכים ממשיים

• הפונקציה VALUE

• הפונקציה DATEVALUE

• הפונקציה TIMEVALUE

יצירת ערך בהתבסס על תנאי

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

• אם

• הפונקציה OR

• הפונקציה CONCATENATE

בדיקה אם קיימים שגיאות בתוך נוסחה

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

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

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

• הפונקציה ISBLANK

• הפונקציה IFERROR

חישוב מכירות מצטברות

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

• הפונקציה CLOSINGBALANCEMONTH, הפונקציה CLOSINGBALANCEQUARTER, הפונקציה CLOSINGBALANCEYEAR

• הפונקציה OPENINGBALANCEMONTH, הפונקציה OPENINGBALANCEQUARTER, הפונקציה OPENINGBALANCEYEAR

השוואת ערכים לאורך זמן

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

• הפונקציה PREVIOUSMONTH, PREVIOUSQUARTER, הפונקציה PREVIOUSYEAR

• הפונקציה TOTALMTD, הפונקציה TOTALQTD, הפונקציה TOTALYTD

• הפונקציה PARALLELPERIOD

חישוב ערך בטווח תאריכים מותאם אישית

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

• הפונקציה DATESINPERIOD

• הפונקציה DATESBETWEEN

• הפונקציה DATEADD

• הפונקציה FIRSTDATE

• הפונקציה LASTDATE

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

• הפונקציה PARALLELPERIOD

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

הצגת עשרת הפריטים המובילים בלבד ב- PivotTable

הזמנת פריטים באופן דינאמי באמצעות נוסחה

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

• הפונקציה EARLIER.