סעיף זה מספק קישורים לדוגמאות שממחישות את השימוש בנוסחאות DAX בתרחישים הבאים.
- ביצוע חישובים מורכבים
- עבודה עם טקסט ותאריכים
- ערכים מותנים ובדיקת שגיאות
- שימוש בבינת זמן
- דירוג והשוואה של ערכים
במאמר זה
תחילת העבודה
בקר ב - Wiki של מרכז המשאבים של DAX , שבו תוכל למצוא כל מיני סוגים של מידע אודות DAX, כולל בלוגים, דוגמאות, סקירות טכניות וסרטוני וידאו שסופקו על-ידי מומחים מובילים בתעשייה ו- Microsoft.
תרחישים: ביצוע חישובים מורכבים
נוסחאות DAX יכולות לבצע חישובים מורכבים הכוללים צבירות מותאמות אישית, סינון ושימוש בערכים מותנים. סעיף זה מספק דוגמאות לתחילת העבודה עם חישובים מותאמים אישית.
יצירת חישובים מותאמים אישית עבור PivotTable
CALCULATE ו- CALCULATETABLE הן פונקציות חזקות וגמישות שבהן ניתן להשתמש להגדרת שדות מחושבים. פונקציות אלה מאפשרות לך לשנות את ההקשר שבו יבוצע החישוב. באפשרותך גם להתאים אישית את סוג הצבירה או הפעולה המתמטית לביצוע. עיין בנושאים הבאים לקבלת דוגמאות.
החלת מסנן על נוסחה
ברוב המקומות שבהם פונקציית DAX מקבלת טבלה כארגומנט, ניתן בדרך כלל להעביר בטבלה מסוננת במקום זאת, על-ידי שימוש בפונקציה FILTER במקום שם הטבלה, או על-ידי ציון ביטוי מסנן כאחד מהארגומנטים של הפונקציה. הנושאים הבאים מציגים דוגמאות ליצירת מסננים ולאופן שבו מסננים משפיעים על תוצאות הנוסחאות. לקבלת מידע נוסף, ראה סינון נתונים בנוסחאות DAX.
הפונקציה FILTER מאפשרת לך לציין קריטריוני סינון באמצעות ביטוי, בעוד שהפונקציות האחרות תוכננו באופן ספציפי לסינון ערכים ריקים.
הסר מסננים באופן סלקטיבי כדי ליצור יחס דינאמי
על-ידי יצירת מסננים דינאמיים בנוסחאות, תוכל לענות בקלות על שאלות דוגמת:
- מה הייתה התרומה של מכירות המוצר הנוכחי לסך המכירות בשנה?
- עד כמה תרמה חטיבה זו לסך הרווחים בכל שנות הפעילות, בהשוואה לחטיבות אחרות?
נוסחאות שבהן אתה משתמש ב- PivotTable עשויות להיות מושפעות מההקשר של PivotTable, אך באפשרותך לשנות את ההקשר באופן סלקטיבי על-ידי הוספה או הסרה של מסננים. הדוגמה בנושא ALL מראה לך כיצד לעשות זאת. כדי למצוא את היחס בין המכירות של משווק ספציפי לעומת המכירות של כל המשווקים, צור מדיד שמחשב את הערך עבור ההקשר הנוכחי חלקי הערך עבור ההקשר ALL.
הנושא ALLEXCEPT מספק דוגמה לאופן הניקוי הסלקטיבי של מסננים בנוסחה. שתי הדוגמאות מסבירות כיצד התוצאות משתנות, בהתאם לעיצוב של ה- PivotTable.
לקבלת דוגמאות אחרות לחישוב יחסים ואחוזים, עיין בנושאים הבאים:
שימוש בערך מלולאה חיצונית
בנוסף לשימוש בערכים מההקשר הנוכחי בחישובים, DAX יכול להשתמש בערך מלולאה קודמת ביצירת סידרה של חישובים קשורים. הנושא הבא מספק הדרכה המסבירה כיצד לבנות נוסחה המפנה לערך מלולאה חיצונית. הפונקציה EARLIER תומכת בשתי רמות של לולאות מקוננות לכל היותר.
כדי ללמוד עוד אודות הקשר שורה וטבלאות קשורות, וכיצד להשתמש במושג זה בנוסחאות, ראה הקשר בנוסחאות DAX.
תרחישים: עבודה עם טקסט ותאריכים
סעיף זה מספק קישורים לנושאי DAX לעיון המכילים דוגמאות לתרחישים נפוצים הכוללים עבודה עם טקסט, חילוץ וחיבור של ערכי תאריך ושעה, או יצירת ערכים בהתבסס על תנאי.
יצירת עמודת מפתח על-ידי שרשור
Power Pivot אינו מאפשר שימוש במפתחות מורכבים; לכן, אם מקור הנתונים שלך כולל מפתחות מורכבים, ייתכן שיהיה עליך לשלב אותם לעמודת מפתח יחידה. הנושא הבא מספק דוגמה אחת לאופן היצירה של עמודה מחושבת בהתבסס על מפתח מורכב.
Compose תאריך בהתבסס על חלקי תאריך שחולצו מתאריך טקסט
Power Pivot משתמש בסוג הנתונים 'תאריך/שעה' של SQL Server כדי לעבוד עם תאריכים; לכן, אם הנתונים החיצוניים שלך מכילים תאריכים המעוצבים באופן שונה - לדוגמה, אם התאריכים שלך נכתבו בתבנית תאריך אזורית שאינה מזוהה על-ידי מנוע הנתונים של Power Pivot, או אם הנתונים שלך משתמשים במפתחות חלופיים של מספרים שלמים - ייתכן שיהיה עליך להשתמש בנוסחת DAX כדי לחלץ את חלקי התאריך ולאחר מכן לחבר את החלקים לתאריך חוקי/ ייצוג זמן.
לדוגמה, אם יש לך עמודה של תאריכים שיוצגו כמספר שלם ולאחר מכן יובאו כמחרוזת טקסט, באפשרותך להמיר את המחרוזת לערך תאריך/שעה באמצעות הנוסחה הבאה:
=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))
| Value1 | Result |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
הנושאים הבאים מספקים מידע נוסף אודות הפונקציות המשמשות לחילוץ וחיבור תאריכים.
הגדרת תאריך מותאם אישית או תבנית מספר מותאמת אישית
אם הנתונים שלך מכילים תאריכים או מספרים שאינם מיוצגים באחת מתבניות הטקסט הרגילות של 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
תרחיש: ערכים מותנים ובדיקת שגיאות
בדומה ל- Excel, DAX כולל פונקציות המאפשרות לך לבדוק ערכים בנתונים ולהחזיר ערך שונה בהתבסס על תנאי. לדוגמה, באפשרותך ליצור עמודה מחושבת שמסמנת משווקים כמועדפים או כערך , בהתאם לכמות המכירות השנתית. פונקציות שבודקות ערכים עוזרות גם לבדיקת הטווח או הסוג של הערכים, כדי למנוע שגיאות נתונים בלתי צפויות עלולות לפגוע בחישובים.
יצירת ערך בהתבסס על תנאי
באפשרותך להשתמש בתנאי IF מקוננים כדי לבדוק ערכים וליצור ערכים חדשים באופן מותנה. הנושאים הבאים מכילים כמה דוגמאות פשוטות של עיבוד מותנה וערכים מותנים:
בדיקת שגיאות בנוסחה
בניגוד ל- Excel, לא ניתן לכלול ערכים חוקיים בשורה אחת של עמודה מחושבת וערכים לא חוקיים בשורה אחרת. כלומר, אם יש שגיאה בחלק כלשהו של עמודת Power Pivot, העמודה כולה מסומנת בדגל עם שגיאה, לכן עליך לתקן תמיד שגיאות בנוסחה שהובאו לידי ביטוי בערכים לא חוקיים.
לדוגמה, אם אתה יוצר נוסחה שמחלקת באפס, ייתכן שתקבל את התוצאה האינסופית או שגיאה. נוסחאות מסוימות ייכשלו גם אם הפונקציה נתקלת בערך ריק כאשר היא מצפה לערך מספרי. בעת פיתוח מודל הנתונים שלך, מומלץ לאפשר את הופעת השגיאות כדי שתוכל ללחוץ על ההודעה ולפתור את הבעיה. עם זאת, בעת פרסום חוברות עבודה, עליך לשלב טיפול בשגיאות כדי למנוע מערכים בלתי צפויים לגרום לכשל בחישובים.
כדי להימנע מהחזרת שגיאות בעמודה מחושבת, השתמש בשילוב של פונקציות לוגיות ופונקציות מידע כדי לבדוק אם קיימות שגיאות ולהחזיר תמיד ערכים חוקיים. הנושאים הבאים מספקים כמה דוגמאות פשוטות לאופן שבו ניתן לעשות זאת ב- DAX:
תרחישים: שימוש בבינת זמן
פונקציות בינת הזמן של DAX כוללות פונקציות שעוזרות לך לאחזר תאריכים או טווחי תאריכים מהנתונים שלך. לאחר מכן, תוכל להשתמש בתאריכים או בטווחי תאריכים אלה כדי לחשב ערכים בתקופות דומות. פונקציות בינת הזמן כוללות גם פונקציות הפועלות עם מרווחי תאריכים רגילים, כדי לאפשר לך להשוות ערכים על-פני חודשים, שנים או רבעונים. באפשרותך גם ליצור נוסחה המשווה בין ערכים עבור התאריך הראשון והתאריך האחרון של תקופה שצוינה.
לקבלת רשימה של כל פונקציות בינת הזמן, ראה פונקציות בינת זמן (DAX). לקבלת עצות לשימוש יעיל בתאריכים ובשעות בניתוח Power Pivot, ראה תאריכים ב- Power Pivot.
חישוב מכירות מצטברות
הנושאים הבאים מכילים דוגמאות לחישוב יתרות סגירה ופתיחה. הדוגמאות מאפשרות לך ליצור יתרות מצטברות במרווחי זמן שונים, כגון ימים, חודשים, רבעונים או שנים.
- הפונקציה CLOSINGBALANCEMONTH, הפונקציה CLOSINGBALANCEQUARTER, הפונקציה CLOSINGBALANCEYEAR
- הפונקציה OPENINGBALANCEMONTH, הפונקציה OPENINGBALANCEQUARTER, הפונקציה OPENINGBALANCEYEAR
השוואה בין ערכים לאורך זמן
הנושאים הבאים מכילים דוגמאות לאופן שבו ניתן להשוות סכומים בין תקופות זמן שונות. תקופות הזמן המוגדרות כברירת מחדל הנתמכות על-ידי DAX הן חודשים, רבעונים ושנים.
- הפונקציה PREVIOUSMONTH, PREVIOUSQUARTER, הפונקציה PREVIOUSYEAR
- הפונקציה TOTALMTD, הפונקציה TOTALQTD, הפונקציה TOTALYTD
- הפונקציה PARALLELPERIOD
חישוב ערך בטווח תאריכים מותאם אישית
עיין בנושאים הבאים לקבלת דוגמאות לאחזור טווחי תאריכים מותאמים אישית, כגון 15 הימים הראשונים לאחר התחלה של קידום מכירות.
אם אתה משתמש בפונקציות בינת זמן כדי לאחזר ערכת תאריכים מותאמת אישית, באפשרותך להשתמש בערכת תאריכים זו כקלט לפונקציה שמבצעת חישובים, כדי ליצור צבירות מותאמות אישית על-פני תקופות זמן. עיין בנושא הבא לקבלת דוגמה כיצד לעשות זאת:
-
הערה
אם אינך צריך לציין טווח תאריכים מותאם אישית, אלא עובד עם יחידות חשבונאות סטנדרטיות כגון חודשים, רבעונים או שנים, מומלץ לבצע חישובים באמצעות פונקציות בינת הזמן שנועדו למטרה זו, כגון TOTALQTD, TOTALMTD, TOTALQTD וכן הלאה.
תרחישים: דירוג והשוואה של ערכים
כדי להציג רק את n הפריטים העליונים בעמודה או ב- PivotTable, עומדות בפניך מספר אפשרויות:
- באפשרותך להשתמש בתכונות ב- Excel כדי ליצור מסנן עליון. באפשרותך גם לבחור מספר ערכים עליונים או תחתונים ב- PivotTable. החלק הראשון של סעיף זה מתאר כיצד לסנן את 10 הפריטים המובילים ב- PivotTable. לקבלת מידע נוסף, עיין בתיעוד של Excel.
- באפשרותך ליצור נוסחה המדרגת ערכים באופן דינאמי ולאחר מכן לסנן לפי ערכי הדירוג, או להשתמש בערך הדירוג ככלי פריסה. החלק השני של סעיף זה מתאר כיצד ליצור נוסחה זו ולאחר מכן להשתמש בדירוג זה בכלי פריסה.
ישנם יתרונות וחסרונות לכל שיטה.
- קל להשתמש במסנן העליון של Excel, אך המסנן מיועד למטרות תצוגה בלבד. אם הנתונים המשמשים כבסיס ל- PivotTable משתנים, עליך לרענן את ה- PivotTable באופן ידני כדי לראות את השינויים. אם עליך לעבוד עם דירוגים באופן דינאמי, באפשרותך להשתמש ב- DAX כדי ליצור נוסחה המשווה ערכים לערכים אחרים בעמודה.
- נוסחת DAX חזקה יותר; יתר על כן, על-ידי הוספת ערך הדירוג לכלי פריסה, באפשרותך פשוט ללחוץ על כלי הפריסה כדי לשנות את מספר הערכים העליונים המוצגים. עם זאת, החישובים יקרים מבחינה חישובית וייתכן ששיטה זו לא תתאים לטבלאות בעלות שורות רבות.
הצגת עשרת הפריטים העליונים בלבד ב- PivotTable
כדי להציג את הערכים העליונים או התחתונים ב- PivotTable
|
|---|
סידור פריטים באופן דינאמי באמצעות נוסחה
הנושא הבא מכיל דוגמה לשימוש ב- DAX כדי ליצור דירוג המאוחסן בעמודה מחושבת. מאחר שנוסחאות DAX מחושבות באופן דינאמי, תמיד תוכל להיות בטוח שהדירוג נכון גם אם הנתונים המשמשים כבסיס השתנו. בנוסף, מכיוון שהנוסחה נמצאת בשימוש בעמודה מחושבת, באפשרותך להשתמש בדירוג בכלי פריסה ולאחר מכן לבחור את 5 הערכים העליונים, 10 הערכים העליונים או אפילו 100 הערכים העליונים.