نوشتهٔ امیر تیمور پور، دانشجوی کد 20 دورهٔ هوش تجاری
Power BI از دو زبان اصلی برای دستکاری و تحلیل داده‌ها استفاده می‌کند: DAX (عبارات تحلیل داده) و M (زبان فرمول Power Query). هر کدام در مرحله‌ای از گردش کار Power BI کاربرد دارند. DAX پس از بارگذاری داده‌ها برای ایجاد محاسبات در مدل داده (مانند Measures و ستون‌های محاسبه‌شده) به کار می‌رود، در حالی که M در Power Query Editor برای پاک‌سازی، تبدیل و شکل‌دهی داده‌ها قبل از ورود به مدل مورد استفاده قرار می‌گیرد. در این راهنمای ویژه‌ی کاربران سطح متوسط Power BI، خواهیم دید چگونه می‌توان از هر دو زبان به‌طور مؤثر بهره برد—از پاک‌سازی با M تا ساخت مصورسازی‌های بینش‌زا با DAX—همراه با مثال‌های گام‌به‌گام، بهترین شیوه‌ها و سناریوهای واقعی.
درک تفاوت DAX و M در Power BI
DAX (عبارات تحلیل داده) زبان فرمول‌نویسی برای محاسبات در مدل داده‌ای Power BI است. این زبان بهینه‌شده برای تجمیع و تحلیل داده‌های جدولی است و نحو آن تا حدی شبیه فرمول‌های اکسل است. هرگاه بخواهید مجموع فروش، مقادیر تجمعی تا تاریخ، درصدها یا سایر محاسبات تحلیلی را بر اساس انتخاب کاربر انجام دهید، DAX انتخاب اصلی شما خواهد بود. فرمول‌های DAX می‌توانند Measures (محاسبات پویا که به تغییرات فیلتر واکنش نشان می‌دهند) یا ستون‌های محاسبه‌شده (محاسبات ایستا که در زمان بارگذاری داده‌ها به ازای هر سطر ارزیابی می‌شوند) ایجاد کنند.
M (زبان فرمول Power Query) در Power Query Editor برای واردکردن، پاک‌سازی و تبدیل داده‌های خام کاربرد دارد. هر مرحله‌ای که در Power Query اعمال می‌کنید—مانند حذف ستون‌ها، فیلتر کردن سطرها، تجزیه یا ادغام متن و…—به یک تبدیل M تبدیل می‌شود. M زبان تابعی قدرتمندی است که می‌تواند داده‌ها را از منابع مختلف شکل دهد: ترکیب جداول، Pivot/Unpivot داده، تجزیه متن و موارد دیگر—همه قبل از بارگذاری در مدل Power BI. اساساً M برای آماده‌سازی داده (ETL) و DAX برای تحلیل داده آماده‌شده کاربرد دارد.
نکته کلیدی: DAX پس از بارگذاری داده‌ها در مدل (در گزارش یا نماهای داده) اجرا می‌شود و نتایج آن بر اساس زمینه‌ی فیلتر (slicers، فیلترهای بصری و…) به‌روز می‌شود. M در Power Query شکل داده‌ای را که برای تحلیل آماده می‌شود تعیین می‌کند و در کاربرد معمول، داده‌های منبع را تغییر نمی‌دهد. بهترین عمل این است که پاک‌سازی و محاسبات پایه را هرچه زودتر—ترجیحاً در منبع یا Power Query—انجام دهید و از DAX تنها برای محاسباتی استفاده کنید که نیاز به زمینه‌ی پویا دارند. این رویکرد منجر به عملکرد بهتر و مدلی ساده‌تر می‌شود؛ در حالت Import، تبدیل‌های M هنگام تازه‌سازی داده اجرا می‌شوند. در DirectQuery، زمان اجرای پرس‌وجو و Query Folding باید جداگانه بررسی شود.
استفاده از DAX برای مصورسازی‌های قدرتمند
پس از تمیز و بارگذاری داده‌ها، DAX وارد عمل می‌شود تا Measures و محاسباتی ایجاد کنید که مصورسازی‌هایتان را تغذیه می‌کنند. DAX می‌تواند بینش‌هایی مانند رشد سال به سال، مجموع‌های تجمعی، درصدهای بخش، رتبه‌بندی‌ها و غیره استخراج کند که سپس در نمودارها، جداول و نقشه‌ها نمایش داده می‌شوند.
Measure در مقابل ستون محاسبه‌شده
- Measure: محاسبه‌ای پویا که در زمینه‌ی فیلتر گزارش یا بصری ارزیابی می‌شود. برای تجمیع‌ها (SUM, COUNT, AVERAGE) و محاسباتی که باید بر اساس انتخاب کاربر تغییر کنند، ایده‌آل است. مثال:
Total Sales = SUM(Sales[SalesAmount])این Measure در یک نمودار ستونی، مقادیر مجموع فروش را بسته به فیلترهای فعال روی محورها یا Slicerها برای هر بخش (مثلاً برای هر سال یا منطقه) محاسبه می‌کند.
- ستون محاسبه‌شده: مقدارش به‌ازای هر سطر هنگام بارگذاری داده‌ها ثابت می‌شود. برای دسته‌بندی‌های ایستا یا الحاق متنی کاربرد دارد؛ مثلاً دسته‌بندی «High/Low» بر اساس مقدار فروش. اغلب ترجیح داده می‌شود این نوع محاسبات در Power Query (M) انجام شود تا مدل ساده بماند.
مثال گام‌به‌گام
- جدول Sales با ستون‌های Product, Category, Date, SalesAmount را در نظر بگیرید.
- در Power BI Desktop یک Measure جدید ایجاد کنید:
Total Sales = SUM(Sales[SalesAmount])- Measure دومی بسازید برای میانگین:
Average Sale = AVERAGE(Sales[SalesAmount])- یک نمودار ستونی در گزارش وارد کنید، فیلد Category را به محور X و Total Sales و Average Sale را به مقادیر (Values) بکشید. خواهید دید که دو میله برای هر دسته‌بندی رسم می‌شود و با تغییر فیلتر (مثلاً انتخاب سال خاص) مقادیر به‌روزرسانی می‌شوند.
می‌توانید Measures بیشتری مانند درصد از کل بسازید با استفاده از توابع CALCULATE و ALL برای حذف فیلتر، یا مقایسه با سال قبل، و آن‌ها را در Tooltip یا لایه‌های ترکیبی نمودار اضافه کنید.
توصیه برای مصورسازی نقشه‌ای
- داده‌های جغرافیایی را در Power Query تمیز و استاندارد کنید (تفکیک آدرس به شهر و استان، تطبیق نام کشورها با ISO و…).
- ستون‌های مربوط به طول و عرض جغرافیایی یا نام شهر/استان را «Data Category» بزنید.
- برای مقادیر عددی Measureهایی مانند Total Sales یا Rank بسازید و آن‌ها را به Size یا Color map بدهید.
- اگر نیاز به دسته‌بندی دارید (مثلاً گروه‌بندی کشورها در قاره‌ها)، بهتر است این ستون‌ها را در Power Query یا به‌عنوان ستون محاسبه‌ای در M بسازید.
استفاده از M در Power Query برای پاک‌سازی داده‌ها
قبل از هرگونه فرمول DAX، داشتن داده‌های تمیز و شکل‌یافته اهمیت دارد و این همان‌جایی است که Power Query (M) می‌درخشد. در Power Query Editor با رابط گرافیکی می‌توانید تبدیل‌های زیر را انجام دهید که همگی به صورت کد M ثبت می‌شوند:
- حذف/فیلتر سطرها (مثلاً فیلتر مقادیر Null یا فیلتر شرطی)
- حذف/تغییر نام ستون‌ها برای سبک‌سازی مدل
- ادغام/تقسیم ستون‌ها (Split by Delimiter) برای تفکیک آدرس یا ادغام متن‌ها
- تغییر نوع داده (Date, Text, Number)
- ستون‌های محاسبه‌ای سفارشی با فرمول M (مثلاً
if [Revenue] > 10000 then "High" else "Low") - Group & Aggregate برای خلاصه‌سازی در صورت لزوم
- Merge (Join) و Append جداول
- Pivot/Unpivot برای شکل‌دهی دوباره داده‌ها
مثال عملی: فرض کنید ستونی به نام FullAddress دارید و می‌خواهید «شهر» و «استان» را استخراج کنید.
- در Power Query Editor ستون FullAddress را انتخاب کنید.
- در ریبون، Home > Split Column > By Delimiter (Delimiter = “,”) را انتخاب کنید.
- سه ستون جدید ایجاد می‌شود—که آن‌ها را «Street»، «City» و «State» نام‌گذاری کنید.
- اگر نیاز دارید فضای اضافی را حذف کنید، از Transform > Trim استفاده کنید.
- ستون «Street» را در صورت عدم نیاز حذف کنید.
- Close & Apply را بزنید تا تبدیل‌ها اعمال و داده‌ها بارگذاری شوند.
اکنون در مدل Power BI یک ستون City و یک ستون State دارید که آماده مصورسازی نقشه یا جداول است.
M در مقابل ستون محاسبه‌شدهٔ DAX
بسیاری از وظایف—مانند تقسیم ستون—قابل انجام هم در M و هم با ستون محاسبه‌شده در DAX هستند. اغلب بهتر است در M انجام شود، چون:
- یک‌بار هنگام Refresh اجرا می‌شود
- امکان Query Folding(ارسال تبدیل‌ها به منبع مانند SQL) را دارد
- همه تبدیل‌ها در یک‌جا ویرایش‌پذیر است (Advanced Editor)
برای محاسبه‌ای که باید به انتخاب کاربر و زمینهٔ فیلتر واکنش نشان دهد، از Measure استفاده کنید. ستون محاسبه‌شدهٔ DAX برای محاسبات سطری و استفاده از روابط مدل مناسب است و با انتخاب Slicer دوباره محاسبه نمی‌شود.
ترکیب DAX و M برای مصورسازی بهتر
برای نتایج بهتر، معمولاً از M و DAX با هم استفاده می‌کنید:
- جدول تاریخ (Date Table): می‌توانید با M یک جدول تاریخ بسازید و با Measures DAX عملیات زمان‌محور (YTD, YOY, …) را انجام دهید.
- تحلیل جغرافیایی: با M نام کشورها را استاندارد کنید و با DAX Measures برای رتبه یا تراکم ایجاد کنید. در نقشه‌ها، لایه‌های تمیز شده با M و مقادیر تجمیع‌شده با DAX نمایش داده می‌شوند.
- بهینه‌سازی عملکرد: اگر به داده‌ی خام روزانه نیازی ندارید، در M داده را به ماه خلاصه کنید تا حجم مدل کاهش یابد. اگر جزئیات موردنیاز است، جزئیات را نگه دارید و مقادیر را با DAX تجمیع کنید.
در همه جداول، نمودارها و نقشه‌ها—ابتدا از Power Query (M) برای شکل‌دهی داده استفاده کنید، سپس با Measures DAX بینش‌های تعاملی و پویا بسازید.
نتیجه‌گیری
هر دو زبان DAX و M در Power BI ضروری هستند و در مراحل مختلف گردش کار وظایف متفاوتی دارند:
- M در Power Query: پاک‌سازی و شکل‌دهی داده‌ها پیش از بارگذاری (ETL)
- DAX در مدل: Measureهای پویا در زمینهٔ فیلتر و ستون‌های محاسبه‌شده برای محاسبات سطری
با انجام تبدیل‌های داده «تا حد امکان زود» (در منبع یا Power Query) و نگه‌داشتن محاسبات پویا برای DAX، می‌توانید گزارش‌هایی بسازید که دقیق، سریع و قابل‌نگهداری باشند. داده‌های تمیز همراه با Measures قدرتمند، پایه‌ای برای مصورسازی‌های دقیق و تعاملی فراهم می‌کند—از نمودارهای ستونی ساده تا نقشه‌های پیشرفته و تحلیل‌های شبکه‌ای. به‌خاطر داشته باشید که از قدرت هر زبان در جای مناسب استفاده کنید: M برای آماده‌سازی، DAX برای روایت داده.