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

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

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

• הפונקציה CALCULATE

• CALCULATETABLE

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

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

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

• סינון

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

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

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

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

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

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

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

• הכל

• ALLEXCEPT

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

• 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 מקוננים כדי מחשב ערכים חדשים וליצור ערכים חדשים באופן מותן. הנושאים הבאים מכילים כמה דוגמאות פשוטות של עיבוד מותן וערכים מותנים:

• 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.