از مفاهیم پایه در زبان DAX درک تفاوت سنجه‌ها و ستون‌های ایجاد شده با استفاده از این زبان و کاربرد یکی از موارد در موقعیت مناسب است. در این مقاله موضوعات بیان شده به اختصار توضیح داده می‌شوند.
ایجاد ستون در زبان DAX
در هنگام ایجاد یک مدل داده در محیط هایی همچون PowerBI Desktop، امکان توسعه‌ی یک جدول دلخواه با یک ستون جدید فراهم است که محتوای این ستون بر اساس عبارت DAX و مقادیر سطری جدول اولیه بدست می‌آید. رابط کاربری برای ایجاد ستون جدید با DAX در محیط‌های مختلف متفاوت است.
برای ایجاد ستون جدید برای یک جدول در اکسل، به اولین سرستون خالی بعد از آخرین سرستون رفته و یک اسم به دلخواه وارد می‌شود (مثلا Add column). بعد از نوشتن نام و با زدن Enter نشانگر به سلول اولین سطر خالی برای وارد کردن داده می‌رود. حال به قسمت نوار فرمول رفته و با زدن “=” فرمول مورد نظر وارد می‌شود. دقت شود که در اکسل نیازی نیست قبل از “=” اسمی برای ستون جدید آورده شود و همچنین می‌توان اسم ستون را بعد از ایجاد آن تغییر داد. (شکل 1: ایجاد ستون Add column در اکسل)
در نرم‌افزار PowerBI Desktop با رفتن به محیط Report view و مسیر Modeling > Calculation > New column ، امکان ایجاد ستون جدید بر اساس فرمول DAX فراهم می‌شود. (شکل 2: مسیر ایجاد ستون در Power BI)
در جدول نهایی دو نوع ستون وجود دارد. ستون‌هایی که جز داده‌های اولیه بوده یا ستون هایی که در محیط های دیگری مثل Power Ouery ساخته شده‌اند و ستون‌هایی که با استفاده از DAX ایجاد شده‌اند. می‌توان از ستون‌های ایجاد شده با DAX، به مانند دیگر ستون‌ها، در گزارش‌گیری و ایجاد رابطه‌ی بین جدولی استفاده کرد. از نکات مهم در ایجاد ستون با DAX این است که مقدار در هر سطر بر اساس فرمول و مقادیر موجود جدول اولیه در همان سطر بدست می‌آید.
ستون‌های ایجاد شده با DAX در زمان پردازش پایگاه داده محاسبه می‌شوند و سپس در مدل ذخیره می‌شوند (هنگام ایجاد ستون با DAX از RAM استفاده می‌شود). این مهم در هنگام ایجاد ستون‌های پیچیده با DAX یک مزیت محسوب می‌شود. برای راحتی و به رسم قرارداد، از فرمت زیر برای نوشتن توابع ایجاد ستون در یک مقاله یا کتاب استفاده می‌شود:
TableName[ColumnName] = <DAX expression for calculated column>
فرمول قبل، مطابق آنچه یک فرمول در نرم‌افزارها ایجاد شود نیست و صرفا یک طرح اولیه برای ایجاد ستون با DAX در نرم‌افزارهای گوناگون است. با این وجود این فرمول دید خوبی از نحوه‌ی نوشتن فرمول ایجاد ستون میدهد. به عنوان مثال، فرض کنید عبارت زیر نحوه‌ی ایجاد یک ستون را نشان میدهد:
Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalProductCost]
در محیط اکسل، با رفتن به سرستون خالی از جدول Sales فرمول بیان شده به شکل ذیل وارد می‌شود:
= Sales[SalesAmount] - Sales[TotalProductCost]
در محیط Power BI با رفتن به جدول Sales و قسمت New column، فرمول زیر وارد می‌شود:
GrossMargin = Sales[SalesAmount] - Sales[TotalProductCost]
ایجاد سنجه یا همان Measure در زبان DAX
هنگامی که نیازی به محاسبه‌ی تمام سطرهای یک ستون نیست و تنها می‌بایست مقدار هم‌فزون سطرهای یک جدول محاسبه شوند، از سنجه (Measure) استفاده می‌شود. پیشتر در مورد نحوه‌ی ایجاد ستون GrossMargin در جدول Sales که در آن مقادیر هر سطر GrossMargin محاسبه شده نشان داده شده است. حال فرض کنید هدف محاسبه‌ی مقدار GrossMargin به عنوان درصدی از Sales Amount است. ستون دلخواه را می‌توان با استفاده از فرمول زیر محاسبه نمود:
Sales[GrossMarginPct] = DIVIDE ( Sales[GrossMargin], Sales[SalesAmount] )
با استفاده از فرمول divide ستون GrossMarginPct طبق شکل زیر ایجاد می‌شود: (شکل 3: نتایج حاصل شده GrossMarginPct به ازای هر سطر)
با استفاده از ستون ایجاد شده نمی‌توان مقدار هم‌فزون یک مقدار (بر حسب درصد) را محاسبه کرد و به جای آن می‌بایست مقدار هم‌فزونی که از تقسیم مجموع سطرهای ستون GrossMargin بر مجموع سطرهای ستون SalesAmount بدست می‌آید را محاسبه کرد. به عبارت دیگر، در اینجا نیاز به محاسبه‌ی نسبت مجموع است و نه جمع نسبت. برای محاسبه‌ی این نسبت جمع از سنجه استفاده می‌شود. به مانند ایجاد ستون و برای راحتی، از فرمت زیر هنگام نوشتن توابع ایجاد سنجه در یک مقاله یا کتاب استفاده می‌شود:
TableName[MeasureName] := <DAX expression for measure>
همانطور که در نحوه‌ی نوشتن فرمول در قسمت قبل مشخص است، برای محاسبه‌ی سنجه می‌بایست اسم سنجه و جدولی که یک سنجه‌ی آن محاسبه می‌شود و همچنین عبارت DAX مشخص باشد. ترکیب نوشتن این فرمول در نرم‌افزارهای مختلف متفاوت است. به عنوان مثال، نحوه‌ی نوشتن صحیح سنجه برای محاسبه‌ی GrossMarginPct به شکل زیر است:
Sales[Gross Margin %] := DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )
در اکسل با رفتن به مسیر View > Workbook Views > Page Layout و نوشتن فرمول زیر در یکی از سلول‌های خالی، سنجه‌ی Gross Margin % ایجاد می‌شود:
Gross Margin % := DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )
در محیط Power BI نیز با رفتن روی جدول Sales و راست کلیک کردن و انتخاب new measure و نوشتن فرمول زیر، سنجه‌ی Gross Margin % ساخته می‌شود:
Gross Margin % = DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )
Power BI هنگام محاسبات عملگر “:=” را به “=” تبدیل میکند. در مقاله ها و کتاب‌ها برای محاسبه‌ی سنجه‌ها از این عملگر استفاده می‌شود و این باعث می‌شود ستون ایجاد شده و سنجه راحت‌تر شناسایی شوند.
سنجه‌ها و ستون‌های ایجاد شده با DAX در محتوای برآورد با یکدیگر تفاوت دارند. سنجه با استفاده از محتوای سلولی که در یک گزارش یا کوئری DAX برآورد شده محاسبه می‌گردد، در حالی که ستون در سطح سطر و داخل جدولی که به آن تعلق دارد محاسبه می‌شود. هنگامی که فرمول SUM(Sales[SalesAmount]) برای محاسبه‌ی یک سنجه نوشته می‌شود، منظور مجموع تمام سلول‌هایی است که تحت این سلول هم‌فزون شده‌اند، در حالی که هنگام نوشتن Sales[SalesAmount] برای ایجاد یک ستون، منظور مقدار ستون SalesAmount در سطر فعلی است.
یکی از پیش‌نیازهای زبان DAX این است که measure می‌بایست داخل جدول تعریف شود. با این حال، می‌توان measure را از یک جدول به جدول دیگری برد بدون آنکه کاربرد آن دستخوش تغییر شود (measure متعلق به جدول نمی‌باشد).
انتخاب بین ستون و سنجه در Power BI
یک تفاوت بزرگ بین سنجه و ستون وجود دارد. مقادیر ستون در طول بروزرسانی داده محاسبه می‌شود و وابسته به کنش و واکنش کاربر در گزارش نمی‌باشد. سنجه وابسته به فیلتری است که در گزارش استفاده شده است. برای استفاده به موقع از بین سنجه یا ستون، در ابتدا می‌بایست هدف کاربر مشخص باشد. برای موارد زیر باید از ستون استفاده شود:
-
- هنگام جایگذاری نتایج محاسبه شده در slicer یا مشاهده‌ی نتایج در سطرها یا ستون‌ها داخل یک جدول pivot، یا در محورهای یک نمودار، یا استفاده‌ی نتایج به عنوان فیلتری در یک کوئری DAX
-
- تعریف یک عبارت DAX که به طور قطعی وابسته به سطر فعلی است. به عنوان مثال، مقدار Price * Quantity روی میانگین یا روی جمع دو ستون عمل نمی‌کند
-
- هنگام دسته‌بندی متن یا اعداد. به عنوان مثال، یک محدوده‌ی مقادیر برای یک سنجه (دامنه‌ی سن مشتریان)، مثلا 18-0، 25-18 و به همین ترتیب.
با اینحال، در هنگام نمایش مقادیر محاسبه‌ی نتیجه که انتخاب‌های کاربر را منعکس میکند و در محیط مقادیر یک جدول pivot یا یک قطعه از نمودار نشان داده می‌شود، می‌بایست از سنجه استفاده شود. برای مثال:
-
- زمانی که هدف محاسبه‌ی درصد سود روی یک محدوده‌ی خاص از داده است.
-
- زمانی که هدف محاسبه‌ی نسبت‌های شاخصی معلوم یک محصول در مقایسه با تمام محصولات است و فیلتر روی سال و منطقه نیز در نظر گرفته شود.
برخی محاسبات را می‌توان هم با ستون‌ها و هم با سنجه‌ها بیان کرد، اگرچه نیاز است تا فرمول‌های DAX متفاوتی نوشت. برای مثال می‌توان ستون ایجادی GrossMargin را به شکل زیر نوشت:
Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalProductCost]
برای تغییر این متن بر روی دکمه ویرایش کلیک کنید. لورم ایپسوم متن ساختگی با تولید سادگی نامفهوم از صنعت چاپ و با استفاده از طراحان گرافیک است.
[GrossMargin] := SUM ( Sales[SalesAmount] ) – SUM ( Sales[TotalProductCost] )
با توجه به آنکه در زمان محاسبه‌ی کوئری از فضای حافظه استفاده نمی‌شود بهتر است از سنجه استفاده شود. این موضوع در زمان کار با داده‌های بزرگ اهمیت پیدا میکند. در برخی موارد، در محاسبه‌ی سنجه‌ها از ستون‌های ایجاد شده استفاده می‌شود و این ستون‌ها به عنوان واسطه در نظر گرفته می‌شوند. فرمول‌های زیر مثالی از مورد بیان شده است:
Sales[SalesAmount] = Sales[Quantity] * Sales[Unit Price]
Sales[Sum of SalesAmount] := SUM ( Sales[SalesAmount] )
یا می‌توان از یک سنجه استفاده کرد که عبارت مشابه برای محاسبه‌ی سطر به سطر ستون محاسبه شده داخل تکرار جدول را محاسبه میکند:
Sales[Sum of SalesAmount] := SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
این شیوه را تقریبا برای تمام سنجه‌ها می‌توان به کار برد. برای مثال، در فرمول‌های ایجاد سنجه و ستون‌ها آورده شده است:
Sales[SalesAmount] = Sales[Quantity] * Sales[Unit Price]
Sales[TotalProductCost] = Sales[Quantity] * Sales[Unit Cost]
Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalProductCost]
Sales[Gross Margin %] := DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )
با این حال، سنجه‌ی قبل را می‌توان به صورت زیر نیز ایجاد کرد:
Sales[Gross Margin %] :=
DIVIDE (
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
- SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] ),
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
)
در پلتفرم‌های تحلیلی و نرم‌افزارهایی همچون اکسل و Power BI به منظور کاهش در نوشتار و شفاف کردن مراحل نوشتار فرمول می‌توان از متغیرها استفاده کرد. فرمول زیر محاسبه‌ی سنجه‌ی Gross Margin % را با استفاده از متغیرها نشان میدهد:
Sales[Gross Margin %] :=
VAR SalesAmount = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
VAR TotalProductCost = SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
VAR GrossMargin = SalesAmount - TotalProductCost
RETURN DIVIDE ( GrossMargin, SalesAmount )
دانلود فایلهای مربوط به این مقاله هوش تجاری
با تکمیل فرم زیر، فایل (های) همراه مقاله در اختیار شما قرار خواهد گرفت: