Data Analysis Expressions (DAX) נשמע קצת מאיים בהתחלה, אבל אל תיתן לשם להטעות אותך. קל מאוד להבין את היסודות של DAX. דבר ראשון - DAX היא לא שפת תכנות. DAX היא שפת נוסחאות. באפשרותך להשתמש ב- DAX כדי להגדיר חישובים מותאמים אישית עבור עמודות מחושבות ועבור מידות (הידועות גם כשדות מחושבים). DAX כולל כמה מהפונקציות המשמשות בנוסחאות של Excel ופונקציות נוספות המיועדות לעבוד עם נתונים יחסיים ולבצע צבירה דינאמית.
הכרת נוסחאות DAX
נוסחאות DAX דומות מאוד לנוסחאות של Excel. כדי ליצור תיקיה אישית, עליך להקליד סימן שוויון ולאחריו שם או ביטוי של פונקציה, ואת כל הערכים או הארגומנטים הדרושים. בדומה ל- Excel, DAX מספק מגוון פונקציות שבהן ניתן להשתמש כדי לעבוד עם מחרוזות, לבצע חישובים באמצעות תאריכים ושעות או ליצור ערכים מותנים.
עם זאת, נוסחאות DAX שונות זו מזו בהיבטים החשובים הבאים:
- אם ברצונך להתאים אישית חישובים על בסיס שורה אחר שורה, DAX כולל פונקציות המאפשרות לך להשתמש בערך השורה הנוכחי או בערך קשור כדי לבצע חישובים המשתנים לפי הקשר.
- DAX כולל סוג פונקציה המחזירה טבלה כתוצאה שלה, במקום ערך יחיד. ניתן להשתמש בפונקציות אלה כדי לספק קלט לפונקציות אחרות.
- פונקציות בינת זמןב- DAX מאפשרות חישובים באמצעות טווחי תאריכים, ומשווים את התוצאות בין תקופות מקבילות.
היכן להשתמש בנוסחאות DAX
ניתן ליצור נוסחאות ב- Power Pivot בעמודות מחושבות או בשדות מחושבים.
עמודות מחושבות
עמודה מחושבת היא עמודה שאתה מוסיף לטבלת Power Pivot קיימת. במקום להדביק או לייבא ערכים בעמודה, צור נוסחת DAX המגדירה את ערכי העמודה. אם תכלול את טבלת Power Pivot ב- PivotTable (או ב- PivotChart), ניתן להשתמש בעמודה המחושבת כמו בכל עמודת נתונים אחרת.
הנוסחאות בעמודות מחושבות דומות מאוד לנוסחאות שאתה יוצר ב- Excel. עם זאת, שלא כמו ב- Excel, אין באפשרותך ליצור נוסחה שונה עבור שורות שונות בטבלה; במקום זאת, נוסחת DAX מוחלת באופן אוטומטי על העמודה כולה.
כאשר עמודה מכילה נוסחה, הערך מחושב עבור כל שורה. התוצאות מחושבות עבור העמודה ברגע שאתה יוצר את הנוסחה. ערכי עמודה מחושבים מחדש רק אם מתבצע רענון של הנתונים המשמשים כבסיס או אם נעשה שימוש בחישוב ידני מחדש.
באפשרותך ליצור עמודות מחושבות המבוססות על מידות ועמודות מחושבות אחרות. עם זאת, הימנע משימוש באותו שם עבור עמודה מחושבת ועבור מדיד, מכיוון שהדבר עלול לגרום לתוצאות מבלבלות. בעת הפניה לעמודה, מומלץ להשתמש בהפניה מלאה לעמודה, כדי להימנע מהפעלת מידה בטעות.
לקבלת מידע מפורט יותר, ראה עמודות מחושבות ב- Power Pivot.
מדידים
מדיד הוא נוסחה שנוצרה במיוחד לשימוש ב- PivotTable (או PivotChart) שמשתמש בנתונים של Power Pivot. המדידים יכולים להתבסס על פונקציות צבירה סטנדרטיות, כגון COUNT או SUM, או להגדיר נוסחה משלך באמצעות DAX. מידה נמצאת בשימוש באזור 'ערכים ' של PivotTable. אם ברצונך למקם תוצאות מחושבות באזור אחר של PivotTable, השתמש במקום זאת בעמודה מחושבת.
כאשר אתה מגדיר נוסחה עבור מידה מפורשת, לא קורה דבר עד שתוסיף את המידה ל- PivotTable. כאשר אתה מוסיף את המדיד, הנוסחה מוערכת עבור כל תא באזור 'ערכים ' של ה- PivotTable. מאחר שנוצרת תוצאה עבור כל שילוב של כותרות שורה ועמודה, התוצאה עבור המדיד עשויה להיות שונה בכל תא.
ההגדרה של המידה שאתה יוצר נשמרת עם טבלת נתוני המקור שלה. הוא מופיע ברשימה 'שדות PivotTable' וזמין לכל המשתמשים של חוברת העבודה.
לקבלת מידע מפורט יותר, ראה מדידים ב- Power Pivot.
יצירת נוסחאות באמצעות שורת הנוסחאות
Power Pivot, בדומה ל- Excel, מספק שורת נוסחאות כדי להקל עליך ליצור ולערוך נוסחאות, וכן פונקציונליות של השלמה אוטומטית, כדי למזער את שגיאות ההקלדה והתחביר.
כדי להזין שם של טבלה התחל להקליד את שם הטבלה. השלמה אוטומטית של נוסחה מספקת רשימה נפתחת המכילה שמות חוקיים המתחילים באותיות אלה.
כדי להזין שם של עמודה הקלד סוגריים מרובעים ולאחר מכן בחר את העמודה מרשימת העמודות בטבלה הנוכחית. עבור עמודה מטבלה אחרת, התחל להקליד את האותיות הראשונות של שם הטבלה ולאחר מכן בחר את העמודה מהרשימה הנפתחת של ההשלמה האוטומטית.
לקבלת פרטים נוספים והדרכה לגבי בניית נוסחאות, ראה יצירת נוסחאות עבור חישובים ב- Power Pivot.
עצות לשימוש בהשלמה אוטומטית
באפשרותך להשתמש בהשלמה אוטומטית של נוסחה באמצע נוסחה קיימת עם פונקציות מקוננות. הטקסט שמופיע מיד לפני נקודת הכניסה משמש להצגת ערכים ברשימה הנפתחת, וכל הטקסט שאחרי נקודת הכניסה נותר ללא שינוי.
שמות מוגדרים שאתה יוצר עבור קבועים אינם מוצגים ברשימה הנפתחת של ההשלמה האוטומטית, אך עדיין תוכל להקליד אותם.
Power Pivot אינו מוסיף את תו הסוגריים הסוגר של פונקציות או מתאים סוגריים באופן אוטומטי. עליך לוודא שכל אחת מהפונקציות נכונה מבחינה תחבירית, אחרת לא ניתן לשמור את הנוסחה או להשתמש בה.
שימוש בפונקציות מרובות בנוסחה
באפשרותך לקנן פונקציות, כלומר להשתמש בתוצאות מפונקציה אחת כארגומנט של פונקציה אחרת. באפשרותך לקנן עד 64 רמות של פונקציות בעמודות מחושבות. עם זאת, קינון עלול להקשות על יצירה או פתרון של נוסחאות.
פונקציות DAX רבות מיועדות לשימוש אך ורק כפונקציות מקוננות. פונקציות אלה מחזירות טבלה, שלא ניתן לשמור ישירות כתוצאה מכך; יש לספק אותו כקלט לפונקציית טבלה. לדוגמה, הפונקציות SUMX, AVERAGEX ו- MINX דורשות טבלה כארגומנט הראשון.
הערה
קיימות מגבלות מסוימות על קינון פונקציות בתוך מדידים, כדי להבטיח שהביצועים לא יושפעו מהחישובים הרבים הנדרשים על-ידי יחסי תלות בין עמודות.
השוואה בין פונקציות DAX ופונקציות Excel
ספריית הפונקציות DAX מבוססת על ספריית הפונקציות של Excel, אך לספריות יש הבדלים רבים. סעיף זה מסכם את ההבדלים ואת נקודות הדמיון בין פונקציות Excel ופונקציות DAX.
- לפונקציות DAX רבות יש שם זהה ואופן פעולה כללי זהה לשל פונקציות Excel, אך הן שונו כך שיקבלו סוגים שונים של קלט, ובמקרים מסוימים, ייתכן שיחזירו סוג נתונים שונה. באופן כללי, לא ניתן להשתמש בפונקציות DAX בנוסחה של Excel או להשתמש בנוסחאות של Excel ב- Power Pivot ללא שינוי כלשהו.
- פונקציות DAX לעולם אינן משתמשות בהפניה לתא או בטווח כהפניה, אלא במקום זאת פונקציות DAX מקבלות עמודה או טבלה כהפניה.
- פונקציות התאריך והשעה של DAX מחזירות סוג נתונים של datetime. לעומת זאת, פונקציות התאריך והשעה של Excel מחזירות מספר שלם המייצג תאריך כמספר סידורי.
- רבות מפונקציות DAX החדשות מחזירות טבלת ערכים או מבצעות חישובים המבוססים על טבלת ערכים כקלט. לעומת זאת, ב- Excel אין פונקציות שמחזירות טבלה, אך פונקציות מסוימות יכולות לפעול עם מערכים. היכולת להפנות בקלות לטבלאות ולעמודות שלמות היא תכונה חדשה ב- Power Pivot.
- DAX מספק פונקציות בדיקת מידע חדשות הדומות לפונקציות מערך ופונקציות בדיקת מידע וקטורית ב- Excel. עם זאת, פונקציות DAX דורשות יצירת קשר גומלין בין הטבלאות.
- הנתונים בעמודה צפויים להיות תמיד מאותו סוג נתונים. אם הנתונים אינם מאותו סוג, DAX משנה את העמודה כולה לסוג הנתונים המתאים ביותר לכל הערכים.
סוגי נתונים של DAX
באפשרותך לייבא נתונים למודל נתונים של Power Pivot ממקורות נתונים רבים ושונים שעשויים לתמוך בסוגי נתונים שונים. בעת הייבוא או הטענה של הנתונים ולאחר מכן שימוש בנתונים בחישובים או בטבלאות PivotTable, הנתונים מומרים לאחד מסוגי הנתונים של Power Pivot. לקבלת רשימה של סוגי הנתונים, ראה 'סוגי נתונים במודלי נתונים'.
סוג הנתונים של הטבלה הוא סוג נתונים חדש ב- DAX המשמש כקלט או כפלט לפונקציות חדשות רבות. לדוגמה, הפונקציה FILTER מקבלת טבלה כקלט ומפיקה פלט של טבלה אחרת המכילה רק את השורות העומדות בתנאי הסינון. על-ידי שילוב פונקציות טבלה עם פונקציות צבירה, באפשרותך לבצע חישובים מורכבים על-פני ערכות נתונים המוגדרות באופן דינאמי. לקבלת מידע נוסף, ראה צבירות ב- Power Pivot.
נוסחאות והמודל היחסי
חלון Power Pivot הוא אזור שבו ניתן לעבוד עם טבלאות נתונים מרובות ולחבר את הטבלאות במודל יחסי. במודל נתונים זה, טבלאות מחוברות זו לזו באמצעות קשרי גומלין, המאפשרים לך ליצור מתאמים עם עמודות בטבלאות אחרות וליצור חישובים מעניינים יותר. לדוגמה, באפשרותך ליצור נוסחאות שמסכמות ערכים עבור טבלה קשורה ולאחר מכן לשמור ערך זה בתא בודד. לחלופין, כדי לשלוט בשורות מהטבלה הקשורה, באפשרותך להחיל מסננים על טבלאות ועמודות. לקבלת מידע נוסף, ראה 'קשרי גומלין בין טבלאות במודל נתונים'.
מאחר שניתן לקשר טבלאות באמצעות קשרי גומלין, טבלאות PivotTable יכולות גם לכלול נתונים מעמודות מרובות מטבלאות שונות.
עם זאת, מאחר שנוסחאות יכולות לפעול עם טבלאות ועמודות שלמות, עליך לעצב חישובים באופן שונה מאשר ב- Excel.
- באופן כללי, נוסחת DAX בעמודה חלה תמיד על כל קבוצת הערכים בעמודה (לעולם לא על מספר שורות או תאים בודדים).
- טבלאות ב- Power Pivot חייבות לכלול תמיד את אותו מספר עמודות בכל שורה, וכל השורות בעמודה חייבות להכיל את אותו סוג נתונים.
- כאשר טבלאות מחוברות באמצעות קשר גומלין, מצופה ממך לוודא שלשתי העמודות המשמשות כמפתחות יש ערכים תואמים, לרוב. מאחר ש- Power Pivot אינו אוכף שלמות הקשרים, ניתן לכלול ערכים לא תואמים בעמודת מפתח ועדיין ליצור קשר גומלין. עם זאת, נוכחותם של ערכים ריקים או לא תואמים עשויה להשפיע על תוצאות הנוסחאות ועל המראה של טבלאות PivotTable. לקבלת מידע נוסף, ראה בדיקות מידע בנוסחאות של Power Pivot.
- בעת קישור טבלאות באמצעות קשרי גומלין, אתה מגדיל את הטווח או את ההקשר שבו הנוסחאות שלך מוערכות. לדוגמה, נוסחאות ב- PivotTable עשויות להיות מושפעות ממסננים או מכותרות עמודה ושורה ב- PivotTable. באפשרותך לכתוב נוסחאות תוך טיפול בהקשר, אך ההקשר יכול גם לגרום לתוצאות להשתנות בדרכים שייתכן שלא ציפית. לקבלת מידע נוסף, ראה הקשר בנוסחאות DAX.
עדכון תוצאות של נוסחאות
רענון נתונים וחישוב מחדש של נתונים הן שתי פעולות נפרדות אך קשורות שעליך להבין בעת עיצוב מודל נתונים המכיל נוסחאות מורכבות, כמויות גדולות של נתונים או נתונים המתקבלים ממקורות נתונים חיצוניים.
רענון נתונים הוא תהליך של עדכון הנתונים בחוברת העבודה שלך בנתונים חדשים ממקור נתונים חיצוני. באפשרותך לרענן נתונים באופן ידני במרווחים שאתה מציין. לחלופין, אם פרסמת את חוברת העבודה באתר SharePoint, באפשרותך לתזמן רענון אוטומטי ממקורות חיצוניים.
חישוב מחדש הוא תהליך של עדכון התוצאות של נוסחאות כדי לשקף שינויים בנוסחאות עצמן וכדי לשקף שינויים אלה בנתונים המשמשים כבסיס. חישוב מחדש יכול להשפיע על הביצועים בדרכים הבאות:
- עבור עמודה מחושבת, יש תמיד לחשב מחדש את תוצאת הנוסחה עבור העמודה כולה, בכל פעם שאתה משנה את הנוסחה.
- עבור מדידה, תוצאות נוסחה אינן מחושבות עד למיקום המידה בהקשר של PivotTable או של PivotChart. הנוסחה גם תחושב מחדש בעת שינוי כל כותרת שורה או עמודה שמשפיעה על מסננים בנתונים או כאשר תרענן את ה- PivotTable באופן ידני.
פתרון בעיות בנוסחאות
שגיאות בעת כתיבת נוסחאות
אם אתה מקבל שגיאה בעת הגדרת נוסחה, ייתכן שהנוסחה מכילה שגיאה תחבירית, שגיאה סמנטית או שגיאת חישוב.
שגיאות תחביר הן הפתרון הקל ביותר. הם כוללים בדרך כלל סוגריים או פסיק חסרים. לקבלת עזרה בתחביר של פונקציות בודדות, עיין בחומר עזר בנושא פונקציות DAX.
השגיאה מהסוג האחר מתרחש כאשר התחביר נכון, אך הערך או העמודה שאליהם מתבצעת הפניה אינם הגיוניים בהקשר של הנוסחה. שגיאות סמנטיות וחישוביות כאלה עלולות לנבוע מאחת מהבעיות הבאות:
- הנוסחה מפנה לעמודה, טבלה או פונקציה שאינן קיימות.
- הנוסחה נראית נכונה, אך כאשר מנוע הנתונים מביא את הנתונים הוא מוצא אי-התאמה מסוג ומעלה שגיאה.
- הנוסחה מעבירה לפונקציה מספר שגוי או סוג שגוי של פרמטרים.
- הנוסחה מפנה לעמודה אחרת המכילה שגיאה, ולכן הערכים שלה אינם חוקיים.
- הנוסחה מפנה לעמודה שלא עובדה, כלומר היא כוללת מטה-נתונים, אך אין לה נתונים ממשיים לשימוש בחישובים.
בארבעת המקרים הראשונים, DAX מסמן בדגל את כל העמודה המכילה את הנוסחה הלא חוקית. במקרה האחרון, DAX מסמן באפור את העמודה כדי לציין שהעמודה נמצאת במצב לא מעובד.
תוצאות שגויות או חריגות בעת דירוג או סידור של ערכי עמודה
בעת דירוג או סידור של עמודה המכילה ערך NaN (לא מספר), אתה עשוי לקבל תוצאות שגויות או בלתי צפויות. לדוגמה, כאשר חישוב מחלק את 0 ב- 0, מוחזרת תוצאת NaN.
הסיבה לכך היא שמנגנון הנוסחאות מבצע סידור ודירוג על-ידי השוואת הערכים המספריים; עם זאת, לא ניתן להשוות את NaN למספרים אחרים בעמודה.
כדי להבטיח תוצאות נכונות, באפשרותך להשתמש במשפטים מותנים באמצעות הפונקציה IF כדי לבדוק אם קיימים ערכי NaN ולהחזיר ערך 0 מספרי.
תאימות למודלים טבלאיים של Analysis Services ולמצב DirectQuery
באופן כללי, נוסחאות DAX שאתה בונה ב- Power Pivot תואמות לחלוטין למודלים טבלאיים של Analysis Services. עם זאת, אם אתה מעביר את מודל Power Pivot למופע של Analysis Services ולאחר מכן פורס את המודל במצב DirectQuery, קיימות כמה מגבלות.
- נוסחאות DAX מסוימות עשויות להחזיר תוצאות שונות אם תפרוס את המודל במצב DirectQuery.
- נוסחאות מסוימות עלולות לגרום לשגיאות אימות בעת פריסת המודל במצב DirectQuery, מאחר שהנוסחה מכילה פונקציית DAX שאינה נתמכת מול מקור נתונים יחסי.
לקבלת מידע נוסף, ראה תיעוד של מידול טבלאי של Analysis Services ב- SQL Server 2012 BooksOnline.