10- زبان فرمول نویسی و توابع DAX

عبارات تحلیل داده (dax) ممکن است در ابتدا کمی ترسناک به نظر برسد ولی نام این زبان فرمول نویسی نباید شما را فریب دهد. اصول اولیه زبان فرمول نویسی dax و استفاده از توابع dax بسیار ساده هستند و فهم آنها بسیار آسان است. در ابتدا باید گفت که  فرمول نویسی Dax یک زبان برنامه نویسی نیست. Dax یک زبان فرمول نویسی است و به راحتی می توانید از توابع dax استفاده کنید. شما میتوانید با استفاده از Dax محاسبات مورد نظر خود را بر روی ستون های محاسباتی و مقیاس ها(فیلدهای محاسباتی) در پاور پیوت انجام دهید. Dax شامل تعدادی از فرمول های موجود در اکسل به همراه توابعی دیگر که برای کار با داده های ارتباطی و انجام محاسبات پویا مورد استفاده قرار میگیرند، میشود.

فهرست مطالب آموزش های پاور پیوت را در لینک زیر مشاهده کنید:

فیلم و آموزش پاور پیوت در اکسل و پاور بی آی

شناخت فرمول های Dax

فرمول های Dax بسیار شبیه به فرمول های اکسل هستند و با استفاده از توابع Dax به راحتی می توانید توابع dax ساده و پیچیده ای بنویسید. برای ایجاد یک فرمول می بایست ابتدا یک علامت مساوی تایپ کنید، سپس نام تابع را قرار دهید و آرگومان های مورد نیاز را وارد کنید. توابع Dax نیز مانند اکسل مجموعه ای از فرمول ها را برای کار با رشته ها، انجام محاسبات با استفاده از تاریخ و زمان و ایجاد مقادیر مشروط فراهم کرده است.

با اینحال، فرمول نویسی Dax در موارد زیر با فرمول های اکسل متفاوت هستند:

• در صورتی که بخواهید محاسبات خود را به صورت ردیف به ردیف انجام دهید، توابع Dax شامل توابعی میشود که به شما امکان میدهد از مقدار فعلی ردیف یا مقادیر مربوط به آن برای انجام محاسبات مختلف استفاده کنید.
• توابع Dax شامل نوعی تابع میشود که یک جدول را به جای یک مقدار، بعنوان نتیجه برمیگرداند. از اینگونه توابع میتوان برای تولید مقادیر ورودی دیگر توابع استفاده کرد.
• توابع تجزیه و تحلیل زمان در توابع dax به شما امکان میدهد با استفاده از محدوده های تاریخ، محاسبات خود را انجام دهید و نتایج را با بازه های تاریخ موازی مقایسه کنید.

در کجا می بایست از فرمول نویسی dax استفاده شود.

شما میتوانید فرمول نویسی dax را هم در فیلدهای محاسباتی و هم در ستون های محاسباتی مورد استفاده قرار دهید.

ستون های محاسباتی

منظور از ستون محاسباتی ستونی است که به یک جدول پاور پیوت اضافه میشود. به جای paste کردن یا منتقل کردن مقادیر ستون به جدول پاور پیوت، میتوان با استفاده از فرمول های dax مقادیر ستون را تعریف کرد. در صورتی که از جدول پاور پیوت در pivot table یا pivot chart استفاده کنید، ستون محاسباتی شبیه به دیگر ستون های داده قابل استفاده هستند. به طور مثال فرض کنید در جدولی قیمت واحد و تعداد فروش یک واحد را دارید، به راحتی می توانید میزان فروش را از ضرب مقدارهای قیمت واحد در تعداد فروش در ستون جدید بدست آورید. یا با ساختن شاخص ها یا mesure ها پاور پیوت و استفاده در پیوت تیبل در پیوت تیبل به راحتی محاسبات را انجام دهید.

فرمول های مربوط به ستون محاسباتی بیشتر شبیه به فرمول هایی است که در اکسل مورد استفاده قرار میدهید. با اینحال برخلاف اکسل، شما نمیتوانید برای هرکدام از ردیف های یک جدول به صورت جداگانه ای فرمول ایجاد کنید. فرمول های dax به صورت اتوماتیک بر روی کل ستون اعمال میشود.

زمانی که یک ستون شامل یک فرمول dax میشود. مقدار مورد نظر برای هرکدام از ردیف ها محاسبه میشود. به محض اینکه فرمول را ایجاد کنید نتیجه برای کل ستون مورد نظر محاسبه می گردد. مقادیر ستون فقط در صورتی دوباره محاسبه میشوند که یا داده های مربوط به آن ها تازه سازی شود و یا از محاسبه مجدد دستی (manual recalculation) استفاده شود. (که در فیلم های آموشی پاور پیوت تمام این مباحث آموزش داده خواهند شد.)

شما میتوانید ستون های محاسباتی ایجاد کنید که بر اساس مقیاس ها (scale) یا ستون های محاسباتی دیگر باشد. با اینحال به یاد داشته باشید که نباید از یک نام مشابه برای ستون های محاسباتی و مقیاس ها استفاده شود، زیرا این کار ممکن است موجب نتایج گیج کننده ای شود. هنگام اشاره به یک ستون در یک فرمول بهتر است از آدرس کامل آن ستون استفاده شود تا تصادفا منجر به ایجاد خطا نگردد. برای مثال فرض کنید در مرحله قبل با استفاده از فرمول های DAX میزان فروش را محاسبه کردید و حالا می خواهید میزان سود یا میزان مالیات را برای هر ردیف با توجه به ستون فروش کل محاسبه کنید.

پیشنهاد مطالعه: 

برای آشنایی بیشتر با ستون های محاسباتی در اکسل میتوانید مطلب زیر را در مطالعه فرمایید:

زبان فرمول نویسی Dax | مفهوم ستون های محاسباتی در اکسل

مقیاس ها (scale) در پاور پیوت و توابع DAX

یک مقیاس عبارت است از یک فرمول که به صورت خاص برای استفاده در pivot table یا pivot chart  که از داده های پاور پیوت استفاده میکند طراحی شده باشد. مقیاس ها هم میتوانند بر اساس توابع تجمیع استاندار (Sum,sumif,count,…) باشند و هم شما میتوانید با استفاده از فرمول های dax مورد نظر خود مقیاس طراحی کنید. مقیاس در قسمت values یک pivot table استفاده میشود. در صورتی که مایل هستید از نتایج محاسباتی خود در دیگر قسمت های پیوت تیبل استفاده کنید، به جای مقیاس میتوانید از یک ستون محاسباتی استفاده نمائید.

زمانی که یک فرمول برای یک مقیاس تعریف میکنید، تا زمانی که آن را به یک یوت تیبل اضافه نکرده باشید هیچ اتفاقی رخ نخواهد داد. با اضافه کردن مقیاس به pivot table، فرمول برای هرکدام از سلول های موجود در محدوده values مورد محاسبه قرار می گیرد.از آنجایی که نتیجه فرمول مقیاس برای ترکیب یک ردیف و یک عنوان ستون محاسبه میشود، ممکن است نتیجه فرمول در سلول های مختلف با هم متفاوت باشد. .

فرمولی که تعریف کرده اید به همراه جدول منبع داده آن ذخیره میشود. این ذخیره سازی در قسمت field list ابزار pivot table اتفاق می افتد و برای تمام کاربران ورک بوک در دسترس خواهد بود.

ساخت فرمول DAX با استفاده از formula bar

پاور پیوت نیز شبیه به اکسل یک قسمت Formula bar دارد که ساخت و اصلاح فرمول را تسهیل میکند و با استفاده از خاصیت auto complete سرعت شما را افزایش میدهد و از بروز خطا جلوگیری میکند.

برای وارد کردن نام یک جدول، چند کاراکتر از نام جدول را تایپ کنید.  خاصیت Autocomplete یک لیست کشویی نمایش میدهد که نام های مجاز موجود که با حروف تایپ شده هماهنگ باشند، در آن لیست شده است.

برای وارد کردن نام ستون، یک براکت تایپ کنید. سپس نام ستون را از لیست ستون های موجود در جدول انتخاب کنید. برای انتخاب یک ستون از جدولی دیگر، اولین کاراکتر از نام جدول را تایپ کنید، سپس ستون مورد نظر را از لیست کشویی auto complete انتخاب کنید.

نکات مهم پیرامون استفاده از خاصیت auto complete

• شما میتوانید در درون فرمول های موجود و زمانی که از فرمول های تو در تو استفاده می کنید نیز از خاصیت Auto complete استفاده کنید.متنی که قبل از محل درج(insertion point) قرار میگیرد، مشخص کننده مقادیر لیست کشویی auto complete است و تمام متن بعد از محل درج بدون تغییر باقی می ماند.
• نام های تعریف شده ای که برای ثابت ها(constant) در نظر میگیرید، در لیست کشویی auto complete قرار نمی گیرند ولی شما میتوانید درون فرمول های خود از آنها استفاده کنید.
• پاور پیوت پرانتز بسته مربوط به فرمول را قرار نمی دهد و پرانتزها را به صورت اتوماتیک با هم هماهنگ نمی کند. شما باید با دقت ترکیب تمام توابع مورد استفاده را وارد کنید.

استفاده از چندین تابع در یک فرمول

شما میتوانید از فرمول های تو در تو استفاده کنید. به این معنی که از نتایج یک تابع به عنوان آرگومان یک تابع دیگر استفاده کنید. در ستون های محاسباتی میتوان از 64 سطح از توابع استفاده کرد. با اینحال استفاده از فرمول های تو در تو ممکن است رفع خطا و اصلاح فرمول ها را دشوار کند.

بسیاری از توابع Dax منحصرا برای استفاده درون فرمول های دیگر طراحی شده اند. این توابع جدولی را برمیگردانند که نمیتواند بعنوان نتیجه فرمول ذخیره سازی شود بلکه می بایست بعنوان ورودی یک تابع دیگر استفاده شود. برای مثال توابع SUMX, AVERAGEX, MINX همگی در آرگومان اول خود نیاز به یک جدول دارند.

نکته:

برخی از محدودیت های توابع تو در تو در مقیاس ها نیز وجود دارد. این موضوع به این دلیل است که اطمینان حاصل شود که عملکرد، تحت تاثیر محاسبات زیاد انجام شده در بین ستون ها قرار نمی گیرد.

مقایسه توابع dax و توابع اکسل

توابع dax بر اساس توابع اکسل شکل گرفته اند. ولی تفاوت های بسیاری در بین این دو وجود دارد. در این بخش به بررسی شباهت ها و تفاوت های بین این دو نوع توابع میپردازیم.

• بسیاری از توابع dax دارای نام و رفتار مشابهی با توابع اکسل هستند ولی به نحوی تغییر داده شده اند که ورودی های متفاوتی میگیرند و خروجی های متفاوتی را نیز برمیگردانند. عموما شما نمیتوانید از توابع dax در اکسل یا برعکس بدون انجام هیچ تغییری استفاده کنید.
• توابع dax هرگز از آدرس سلول یا محدوده استفاده نمیکنند و به جای آن از آدرس جدول یا ستون در ترکیب خود بهره میبرند.
• توابع زمان و تاریخ dax یک نوع داده datetime برمیگردانند. در مقابل همین نوع توابع در اکسل یک مقدار integer برمیگردانند که تاریخ و زمان را به صورت یک عدد نشان میدهد.
• بسیاری از توابع جدید dax یا یک جدول را بعنوان جواب تابع برمیگردانند یا از یک جدول بعنوان ورودی استفاده میکنند. در مقابل در اکسل هیچ تابعی وجود ندارد که یک جدول را بعنوان پاسخ برگرداند ولی توابعی وجود دارد که میتوانند با آرایه ها کار کنند. توانایی کار با جداول کامل و ستون ها یکی از قابلیت های منحصر به فرد توابع dax است. برای مثال خروجی تابع offset در توابع اکسل یک آرایه از مقادیر است.
• توابع Dax یک تابع lookup جدید معرفی کرده است که بسیار شبیه به توابع array و vector lookup در اکسل می باشد. با اینحال استفاده از توابع dax مستلزم این است که حتما یک ارتباط بین جداول برقرار شود.
• داده ها درون یک ستون همیشه می بایست از یک نوع باشند. در غیر اینصورت، dax نوع تمام داده های ستون را به فرمتی تغییر میدهد که به بهترین شکل تمام مقادیر را دربرمیگیرد.

 

انواع داده در فرمول نویسی dax

شما میتوانید داده های مورد نظر خود را از منابع متعدد و مختلفی وارد پاور پیوت کنید که این داده ها ممکن است دارای فرمت ها و انواع مختلفی باشند. زمانی که داده ها را بارگذاری یا وارد پاور پیوت میکنید، و سپس از این داده ها در محاسبات خود و یا در pivot table استفاده میکنید، داده ها تبدیل به یکی از انواع داده پاور پیوت میشوند.

نوع داده table (جدول) یک نوع جدید از داده در توابع dax است که بعنوان ورودی یا خروجی در بسیاری از توابع جدید مورد استفاده قرار میگیرد. برای مثال تابع filter، یک جدول را بعنوان ورودی میگیرد و یک جدول دیگر با ردیف هایی که از فیلتر مشخص شده عبور کرده اند را برمیگرداند. با ترکیب توابع تجمیع و جدول  میتوانید محاسبات پیچیده ای را بر روی داده هایی که به صورت پویا تعریف شده اند، انجام دهید.

فرمول ها و مدل های ارتباطی

پنجره پاور پیوت محیطی است که شما میتوانید با چندین جدول داده کار کنید و جداول را به صورت مدل های ارتباطی به هم متصل کنید. در درون این مدل داده، جداول با استفاده از برقراری ارتباط (relationship) به هم متصل میشوند که این کار به شما امکان میدهد بین ستون های دیگر جداول ارتباط برقرار کنید و محاسبات جالبی را انجام دهید. برای مثال شما میتوانید با استفاده از یک فرمول Sum مقادیر مرتبط با هم در جداول مختلف را با هم جمع کنید و در یک سلول قرار دهید. یا برای کنترل ردیفهای مرتبط جداول مختلف میتوانید از Filter استفاده کنید.

از آنجایی که شما میتوانید با استفاده از relationship جداول را به هم متصل کنید، pivot table شما نیز میتواند داده های مربوط به ستون های مختلف در جداول مختلف را در برگیرد. (این موضوع به صورت کامل در دوره پیوت تیبل و پاور پیوت آموزش داده شده است. که به زودی در وبسایت kaar2.ir بازگذاری خواهند شد.)

با اینحال از آنجایی که فرمول dax فقط با ستون ها و جداول کامل قادر به کار کردن هستند، می بایست محاسبات خود را به شکلی متفاوت با اکسل طراحی کنید.

• به صورت کلی، یک فرمول dax در یک ستون همیشه به کل مقادیر آن ستون اعمال میشود. (نه به یک ردیف از ستون)
• جداول در پاور پیوت می بایست همیشه تعداد یکسانی ستون در هر ردیف داشته باشند. و تمام ردیف ها در یک ستون می بایست یک نوع داده داشته باشند.
• زمانی که جداول با استفاده از relationship به هم مرتبط شدند، می بایست حتما دو ستون که به عنوان keys استفاده می شود  در اکثر قسمت ها دارای مقادیری هماهنگ باشند. از آنجایی که پاور پیوت یکپارچگی در مراجع داده را بررسی نمیکند، امکان دارد مقادیر غیر هماهنگ در یک ستون کلیدی داشته باشید و یک ارتباط هم ایجاد کنید.  با اینحال ممکن است وجود مقادیر غیر هماهنگ و یا سلول های خالی باعث ایجاد خطاهایی در محاسبات و نتایج فرمول ها و همچنین شکل ظاهری پاور پیوت گردد.
• زمانی که با استفاده از relationship جداول را با هم متصل میکنید، محدوده و زمینه ارزیابی فرمول های خود را بزرگتر میکنید. برای مثال، فرمول های موجود در pivot table ممکن است تحت تاثیر فیلترها، یا عناوین ستون ها و ردیف های پیوت تیبل قرار بگیرند. شما میتوانید فرمول هایی بنویسید که این زمینه را دستکاری کنند. ولی زمینه ممکن است نتایح شما را به نحوی تحت تاثیر قرار دهد که شما پیش بینی نمیکنید.

به روز رسانی نتایج فرمول ها

تازه سازی (refresh) و محاسبه مجدد دو عملیات جداگانه هستند که شما می بایست برای طراحی مدل های داده که شامل فرمول های پیچیده، مقادیر بزرگ داده، یا داده هایی که از منابع خارجی داده بدست آمده اند، میشوند شناخت کافی از آن داشته باشید.

تازه سازی (refresh) داده ها شامل فرایند به روز رسانی داده در ورک بوکی (سند) میشود که داده های جدیدی از یک منبع داده خارجی در آن قرار گرفته است. شما میتوانید داده های خود را به صورت دستی و در بازه هایی که مشخص میکنید تازه سازی کنید و یا اگر ورک بوک خود را در یک سایت sharepoint منتشر کرده اید، میتوانید یک برنامه زمانی مشخص برای تازه سازی اتوماتیک ایجاد کنید.

محاسبه مجدد (Recalculation) فرایند به روز رسانی نتایج یک فرمول به منظور انعکاس تغییرات در خود فرمول و انعکاس آن تغییرات در داده های مربوط به آن است. این فرایند مشابه محاسبه مجدد در فرمول های اکسل است، مانند زمانی که هر بار در یک سلول کلیک می کنید و کلید Enter را می زنید، تمام فرمول های شما مجددا محاسبه می شوند. محاسبه مجدد میتواند به اشکال زیر عملکرد را تحت تاثیر قرار دهد:

• در یک ستون محاسباتی، هر بار که شما فرمول را تغییر میدهید، نتایج فرمول همیشه برای کل ستون محاسبه مجدد می شود.
• در یک مقیاس، نتایج مقیاس تا زمانی که مقیاس در محیط یک پیوت تیبل و یا پیوت چارت قرار نگرفته باشد، محاسبه مجدد نخواهد شد. همچنین زمانی که به صورت دستی پیوت تیبل را تازه سازی میکنید یا در یک عنوان ستون یا ردیف تغییری ایجاد میکنید که این تغییرات باعث تغییر در فیلتر های بر روی داده میشود، فرمول محاسبه مجدد میشود.

 

رفع خطا در فرمول های DAX

اگر در زمان تعریف یک فرمول با خطا مواجه شدید، ممکن است فرمول شما دارای یک خطا در ترکیب (syntactic error)، ماهیت (semantic error)، یا محاسبات (calculation error) باشد.

رفع خطاهای در ترکیب کار آسانی است. ممکن است یک کاما یا پرانتز کم یا زیاد باعث بوجود آمدن چنین خطایی شده باشد.

نوع خطای دیگر زمانی اتفاق می افتد که ترکیب تابع صحیح است ولی مقدار یا آدرس ستونی که درون تابع قرار داده شده است ، به لحاظ ماهیت مناسب نیست. این گونه خطاها ممکن است به دلیل یکی از عوامل زیر ایجاد شده باشد:

• فرمول به یک ستون، جدول، یا تابع اشاره میکند که در واقع وجود ندارد.
• فرمول صحیح به نظر می رسد ولی زمانی که داده ها فراخوانی میشوند، یک عدم تطابق باعث به وجود آمدن خطا میشود.
• یک عدد غیر صحیح یا یک پارامتر نامربوط درون تابع قرار میگیرد.
• فرمول به یک ستون متفاوت اشاره میکند که این ستون دارای یک خطا است و در نتیجه مقادیر آن نامعتبر است.
• فرمول به یک ستون اشاره میکند که مورد پردازش قرار نگرفته است. به این معنی که دارای metadata است ولی داده های واقعی برای استفاده در محاسبات وجود ندارد.

در چهار مورد اول، dax کل ستونی را که داده های نامعتبر در آن قرار دارد را مشخص میکند. در مورد آخر، Dax ستون را به رنگ خاکستری در می آورد تا نشان دهد که ستون در یک حالت پردازش نشده قرار دارد.

نتایج غیرمعمول یا ناصحیح در زمان رتبه بندی یا مرتب کردن مقادیر ستون

زمان مرتب کردن یا رتبه بندی مقادیر ستون که شامل مقدار NAN (غیر عددی و نامشخص) میشود، ممکن است با نتایج غیر صحیح یا غیر منتظره روبرو شوید. برای مثال زمانی که در یک محاسبه 0 را بر 0 تقسیم میکنید یک نتیجه NAN برگردانده میشود.

این موضوع به این دلیل است که موتور فرمول رتبه بندی را بر اساس مقایسه مقادیر عددی انجام میدهد و مقدار های NAN قابل مقایسه با دیگر اعداد درون ستون نیستند. برای اطمینان از اینکه نتایج صحیح برگردانده میشود میتوانید با استفاده از یک فرمول if در صورتی که یک سلول حاوی مقادیر NAN باشد، صفر را جایگزین آن کنید.