ایجاد اعداد ترتیبی (1st, 2nd, 3rd) در Power BI
اعداد ترتیبی چیستند و چرا در Power BI کاربرد دارند؟
در دنیای تحلیل داده، گاهی لازم است جایگاه یک عنصر را نسبت به بقیه نمایش دهیم. در این موارد از اعداد ترتیبی استفاده می‌کنیم؛ یعنی اعدادی مثل اول (1st)، دوم (2nd)، سوم (3rd) و به همین ترتیب.
اما یک نکته مهم این است که در زبان DAX یا Power Query — که دو ابزار اصلی در توسعه گزارش‌های Power BI هستند — تابع داخلی و آماده‌ای برای تولید این نوع اعداد وجود ندارد. بنابراین اگر بخواهیم مثلاً در جدول تاریخ (Date Table) یا گزارش‌های رتبه‌بندی، این نوع نمایش را داشته باشیم، باید خودمان آن را پیاده‌سازی کنیم.
در این مقاله از مجموعه‌ی آموزش Power BI، قصد دارم به‌صورت کامل و کاربردی به شما نشان دهم که چگونه می‌توان با استفاده از DAX و Power Query، اعداد ترتیبی تولید کرد و آن‌ها را به داشبوردهای هوش تجاری خود اضافه نمود.
فهرست مطالب
1. شرایط لازم برای ایجاد اعداد ترتیبی در Power BI
برای تولید اعداد ترتیبی (مثل 1st، 2nd، 3rd) در تحلیل داده، باید به چند قانون ساده توجه کنیم. این قوانین مشخص می‌کنند که چه پسوندی به کدام عدد اضافه شود.
قوانین کلی ساخت اعداد ترتیبی:
اعدادی که به 11، 12 یا 13 ختم می‌شوند همیشه با “th” نمایش داده می‌شوند.
مانند:11th,12th,13th,111th,113thسایر اعداد بر اساس رقم پایانی‌شان به شکل زیر پسوند می‌گیرند:
اعدادی که به 1 ختم می‌شوند، با “st” نمایش داده می‌شوند.
مانند:1st,21st,101stاعدادی که به 2 ختم می‌شوند، با “nd” نمایش داده می‌شوند.
مانند:2nd,22nd,102ndاعدادی که به 3 ختم می‌شوند، با “rd” نمایش داده می‌شوند.
مانند:3rd,53rd,953rdسایر اعداد با “th” پایان می‌گیرند.
مانند:5th,14th,25th
با دانستن این قواعد، در ادامه مقاله با چند روش مختلف برای ساخت این اعداد ترتیبی در هر دو زبان DAX و Power Query (M) آشنا می‌شوید. بعضی از این روش‌ها خوانایی بالاتری دارند و برخی دیگر کدنویسی کوتاه‌تر و ساده‌تری ارائه می‌دهند.
2. ساخت اعداد ترتیبی در DAX
در این بخش، می‌خواهیم ببینیم چگونه می‌توان با استفاده از زبان DAX — یکی از ابزارهای اصلی در آموزش Power BI — اعداد ترتیبی تولید کرد. ابتدا مثالی کاربردی را بررسی می‌کنیم و سپس نسخه‌ی ساده‌تر کد را ارائه می‌دهیم.
2.1. روش استفاده از SWITCH
فرض کنید گزارشی دارید که اطلاعات مربوط به درآمد تجمعی هر روز تا به امروز (Month-to-Date) را نمایش می‌دهد. می‌خواهید ستونی اضافه کنید که بازه زمانی محاسبه را نشان دهد، به این صورت:
«درآمد از 1st تا 25th آوریل 2022»
برای این‌کار باید عدد روز را به فرمت ترتیبی نمایش دهید، و برای آن می‌توان از دستور SWITCH در DAX استفاده کرد.
ایجاد Measure برای ساخت عدد ترتیبی در Power BI
برای رسیدن به این هدف در زبان DAX، می‌توان یک Measure ایجاد کرد که به‌طور خودکار اعداد را با پسوند ترتیبی مناسب نمایش دهد. این تکنیک مخصوصاً برای داشبوردهایی که در آن‌ها به شکل پویا رتبه‌ها یا تاریخ‌ها نمایش داده می‌شوند، بسیار کاربردی است.
در ادامه مثالی از این Measure آورده شده که می‌توانید در گزارش‌های تحلیل داده و مدل‌های هوش تجاری خود استفاده کنید:
Ordinal Numbers =
VAR LatestDate = MAX( Time[Date] )
VAR Last2Characters = RIGHT( DAY( LatestDate ), 2 )
VAR LastCharacter = RIGHT( DAY( LatestDate ), 1 )
VAR Addition =
SWITCH( TRUE( ),
Last2Characters IN { "11", "12", "13" }, "th",
LastCharacter = "1", "st",
LastCharacter = "2", "nd",
LastCharacter = "3", "rd",
"th"
)
VAR Result =
"Revenue for " & " 1st - "
& FORMAT( LatestDate, "D" )
& Addition
& " of "
& FORMAT( LatestDate, "mmmm yyyy" )
RETURN Result
تحلیل منطق کد برای ساخت اعداد ترتیبی در Power BI
در این بخش از آموزش Power BI، مرحله‌به‌مرحله بررسی می‌کنیم که کد DAX ارائه‌شده در بخش قبل چگونه عمل می‌کند و چرا برای متخصصان تحلیل داده و هوش تجاری کاربردی است.
این کد دقیقاً چه کاری انجام می‌دهد؟
شناسایی آخرین تاریخ در فیلتر فعال
ابتدا با استفاده از تابعی مانندMAX([Date])یا مقدار جاری در کانتکست، آخرین تاریخ مربوط به فیلتر یا سطر انتخاب‌شده در گزارش شناسایی می‌شود.استخراج رقم‌های پایانی عدد
سپس، از روی این تاریخ، دو مقدار استخراج می‌شود:آخرین رقم عدد (Last Digit)
دو رقم پایانی عدد (Last Two Digits)
این دو مقدار در متغیرهای جداگانه ذخیره می‌شوند تا بتوان منطق مربوط به پسوند را به‌درستی اعمال کرد.
تخصیص پسوند با استفاده از SWITCH
با استفاده از تابعSWITCH، مقادیر پسوندها به‌شکل زیر تعیین می‌شوند:اگر دو رقم آخر عدد 11، 12 یا 13 باشد، پسوند “th” اضافه می‌شود.
(برای جلوگیری از استثنا در مواردی مثل 11th یا 113th)اگر عدد به 1، 2 یا 3 ختم شود، به ترتیب پسوند “st”، “nd” و “rd” اضافه می‌شود.
در غیر این صورت، پسوند پیش‌فرض “th” استفاده می‌شود.
ترکیب نتیجه نهایی (Concatenate)
در پایان، همه اجزای متنی — شامل عدد، پسوند و سایر عناصر متن — با هم ترکیب می‌شوند تا خروجی نهایی مثل"Revenue from 1st to 25th of April"به دست آید.
✅ این منطق ساده اما قدرتمند، می‌تواند ظاهر گزارش‌های شما را حرفه‌ای‌تر و تجربه کاربری داشبوردهای تحلیل داده را ارتقاء دهد.
روش دوم: استفاده از تابع MID در DAX برای ساخت اعداد ترتیبی
یکی دیگر از روش‌های دقیق‌تر (و کمی پیچیده‌تر) برای ساخت اعداد ترتیبی در Power BI استفاده از تابع MID است. این روش بیشتر برای مواردی مفید است که بخواهید کنترل دقیق‌تری روی موقعیت کاراکترها در عدد داشته باشید.
در اینجا نسخه‌ای از این روش با استفاده از زبان DAX آورده شده که می‌توانید در گزارش‌های تحلیل داده خود به کار بگیرید:
Ordinal Numbers MID =
VAR LatestDate = MAX( 'Time'[Date] )
VAR DayNum = DAY( LatestDate )
VAR Addition = MID("thstndrdth", MIN(9, 2 * RIGHT( DayNum ) *
(MOD( DayNum -11, 100) >2) +1), 2)
VAR Result =
"Revenue for " & " 1st - "
& DayNum
& Addition
& " of "
& FORMAT( LatestDate, "mmmm yyyy" )
RETURN Result
ادامه روش تابع MID برای ساخت اعداد ترتیبی در DAX
کدی که در بخش قبل بررسی کردیم، شباهت زیادی به روش قبلی دارد، با این تفاوت که در آن از متغیر جدیدی به نام Addition استفاده شده است. این متغیر به کمک تابع MID مقدار پسوند مناسب را از یک رشته استخراج می‌کند.
چگونه این روش کار می‌کند؟
✅ تابع MID در این مثال از ۳ آرگومان استفاده می‌کند:
رشته‌ای شامل پسوندها:
"thstndrdth"
توجه کن که"th"دو بار در این رشته آمده است تا منطق انتخابی به درستی اجرا شود.مقدار شروع استخراج (Start Position):
این قسمت با منطق عددی خاصی تعیین می‌شود که بر اساس مقدار عدد ورودی (مثلاً 21، 13، 3) تصمیم می‌گیرد پسوند از کجای رشته آغاز شود. این قسمت پیچیده‌ترین بخش کد است.تعداد کاراکتر مورد نظر برای استخراج (Number of Characters):
معمولاً ۲ کاراکتر (مانند “st” یا “th”) برگردانده می‌شود.
📌 نکته:
این روش یکی از کوتاه‌ترین راه‌ها برای ساخت اعداد ترتیبی در DAX است، اما به‌خاطر منطق فشرده و استفاده از موقعیت رشته‌ای، درک آن برای افرادی که با مدل‌های تحلیل داده کار می‌کنند، ممکن است دشوار باشد.
بنابراین باید تصمیم بگیری که خوانایی کد را ترجیح می‌دهی یا کوتاه بودن آن را.
۳. ساخت اعداد ترتیبی در Power Query (زبان M)
یکی از روش‌های پیشنهادی در آموزش Power BI این است که اعداد ترتیبی را مستقیماً در جدول تاریخ ایجاد کنی. به‌این‌ترتیب، برای هر تاریخ، می‌توانی نسخه ترتیبی آن را هم داشته باشی:
مثلاً روز اول ماه می‌شود 1st و روز ۲۲ام می‌شود 22nd.
ایجاد این ستون در Power Query (M Language) مزیت‌هایی دارد:
از منابع مدل داده کاسته می‌شود
مراحل پردازش ساده‌تر می‌شود
کار با زبان DAX در ادامه راحت‌تر خواهد بود
در Power Query هم دو روش برای تولید این اعداد وجود دارد:
براساس سری عددی (Number Series)
براساس سری زمانی (Date Series)
تفاوت این دو روش در نحوه‌ی کار با انواع داده (Data Types) است که در Power Query اهمیت زیادی دارد.
۳.۱ روش اول: تولید اعداد ترتیبی براساس سری عددی
اگر تازه‌کار هستی و با ساختار شرطی if در Power Query آشنایی نداری، این بخش یک مثال خوب برای شروع است. در این روش، با استفاده از ترکیب توابع Text.End، Number.ToText و List.Contains می‌توانی یک ستون سفارشی بسازی:
let
Last2Characters = Text.End( Number.ToText( [Index] ), 2 ),
LastCharacter = Text.End( Number.ToText( [Index]), 1 )
in
if List.Contains( { "11", "12", "13" }, Last2Characters ) then "th"
else if LastCharacter = "1" then "st"
else if LastCharacter = "2" then "nd"
else if LastCharacter = "3" then "rd"
else "th"
تحلیل این کد در قالب آموزش Power BI:
✅ مرحله ۱: استخراج عدد روز از ستون Day.
✅ مرحله ۲: محاسبه‌ی دو مقدار کلیدی:
lastDigit: رقم آخر عدد (باNumber.Mod(day, 10))lastTwoDigits: دو رقم پایانی (باNumber.Mod(day, 100))
✅ مرحله ۳: تعیین پسوند با منطق زیر:
اگر عدد در بازه ۱۱ تا ۱۳ باشد، پسوند “th” اختصاص می‌یابد.
اگر رقم آخر ۱ باشد، “st”
اگر ۲ باشد، “nd”
اگر ۳ باشد، “rd”
در سایر موارد، “th”
✅ مرحله ۴: ترکیب عدد با پسوند نهایی.
استفاده از توابع Number.IntegerDivide و Number.Mod برای تولید اعداد ترتیبی
اگر بخواهی به نتیجه‌ای مشابه با روش قبلی اما با ساختار متفاوت برسی، می‌توانی از ترکیب توابع Number.IntegerDivide (تقسیم صحیح) و Number.Mod (باقی‌مانده تقسیم) در Power Query استفاده کنی.
📌 کافی‌ست کد زیر را در یک ستون سفارشی (Custom Column) قرار دهی:
let
suffix = {"th", "st", "nd", "rd"},
a =
if Text.End(Text.From( Number.IntegerDivide( [Index], 10) ), 1) = "1"
then suffix{0}
else suffix{Number.Mod([Index], 10)}?
in
a??suffix{0}
۳.۲ روش دوم: تولید اعداد ترتیبی براساس سری تاریخ (Date Series)
علاوه بر سری عددی، می‌توانی برای تاریخ‌ها نیز در Power Query اعداد ترتیبی ایجاد کنی. اگر این مقادیر را مستقیماً در جدول تاریخ ذخیره کنی، دیگر نیازی به تولید آن‌ها در DAX نخواهی داشت — که هم مدل شما ساده‌تر می‌شود و هم عملکرد آن بهتر خواهد بود.
🎯 هدف ما در این بخش آن است که با استفاده از یک ستون سفارشی به نام Ordinal Suffix، پسوند مناسب را برای روز ماه تولید کنیم. سپس این مقدار را به عدد روز اضافه می‌کنیم تا خروجی نهایی مانند 5th, 21st, 23rd و … ایجاد شود.
استفاده از تابع Date.ToText برای ساخت اعداد ترتیبی در جدول تقویم
اگر می‌خواهی اعداد ترتیبی را برای سری تاریخ‌ها در جدول تقویم ایجاد کنی، می‌توانی یک ستون سفارشی بسازی که از تابع Date.ToText استفاده می‌کند. این روش ساده و کاربردی، در پروژه‌های هوش تجاری و تحلیل داده، به خصوص هنگام آموزش Power BI، بسیار مفید خواهد بود.
مثال زیر نحوه استفاده از Date.ToText برای استخراج روز به همراه پسوند ترتیبی آن را نشان می‌دهد:
let
Daynumber = Date.ToText( [Date], "dd" , "nl-NL"),
LastCharacter = Text.End( Date.ToText( [Date], "dd" , "nl-NL"), 1 )
in
if List.Contains( { "11", "12", "13" }, Daynumber ) then "th"
else if LastCharacter = "1" then "st"
else if LastCharacter = "2" then "nd"
else if LastCharacter = "3" then "rd"
else "th"
این کد ابتدا دو متغیر Daynumber و LastCharacter را ایجاد می‌کند که در مراحل بعدی به آن‌ها ارجاع داده خواهد شد.
سپس کد موارد زیر را بررسی می‌کند:
آیا عدد روز برابر با ۱۱، ۱۲ یا ۱۳ است؟ اگر بله، پسوند
"th"را برمی‌گرداند.آیا رقم آخر عدد برابر با ۱، ۲ یا ۳ است؟ در این صورت به ترتیب پسوندهای
"st","nd","rd"اختصاص داده می‌شوند.در همه‌ی موارد دیگر، پسوند
"th"به عدد اضافه می‌شود.
let
Daynumber = Date.ToText( [Date], "dd" , "nl-NL"),
LastCharacter = Text.End( Date.ToText( [Date], "dd" , "nl-NL"), 1 )
in
if List.Contains( { "11", "12", "13" }, Daynumber ) then "th" else
if List.Contains( { "1", "2", "3" }, LastCharacter ) then
{"st", "nd", "rd"}{Number.From(LastCharacter) -1 } else "th"
بخش مهم کد: استخراج پسوند از لیست بر اساس اندیس
بخشی از کد که در آن پسوند (Suffix) استخراج می‌شود، از یک رشته (لیست متنی) استفاده می‌کند. در این روش، استخراج پسوند بر اساس موقعیت اندیس در رشته انجام می‌شود که برابر است با شماره کاراکتر منهای یک (character number – 1).
۳.۳ روش استفاده از تابع Text.Middle
در نهایت، می‌توانی اعداد ترتیبی را با استفاده از روشی که Rick Rothstein در بخش ۲.۲ معرفی کرده است، بسازی. این روش از تابع Text.Middle بهره می‌برد که کار استخراج پسوند را بر اساس اندیس انجام می‌دهد.
مزیت این روش این است که کد نسبتاً کوتاه‌تر می‌شود، اما ممکن است برای خواندن و درک آن نیاز به دقت بیشتری داشته باشی.
Text.Middle( "thstndrdth",
List.Min(
{ 8,
2 * Number.From ( Text.End( Date.ToText( [Date], "dd"), 1 ) ) *
(Number.From( Date.Day( [Date] ) - 11 - 100 *
Number.RoundDown( ( Date.Day( [Date] ) -11 ) / 100 ) > 2 ) ) } ),
2 )
نکته مهم درباره استفاده از کدهای کوتاه در VBA، Excel و DAX
مزیت اصلی این مثال این بود که می‌توان آن را در یک خط کد در VBA یا فرمول‌های Excel نوشت. این مزیت در مورد کد DAX معرفی شده در بخش ۲.۲ نیز صدق می‌کند.
تفاوت در Power Query
اما در Power Query داستان متفاوت است. کدی که برای این روش نوشته می‌شود، به دلیل نیاز به تبدیل نوع داده‌ها (Explicit Type Conversion) طولانی‌تر است. علاوه بر این، پیچیدگی بیشتری نسبت به سایر روش‌ها دارد.
در این مقاله یاد گرفتید که چگونه با استفاده از دو زبان قدرتمند DAX و Power Query، اعداد ترتیبی مانند 1st، 2nd، 3rd را بسازید. این مهارت به شما کمک می‌کند گزارش‌ها و مدل‌های هوش تجاری خود را حرفه‌ای‌تر کنید و داده‌هایتان را معنی‌دارتر و قابل فهم‌تر نمایش دهید.
حالا می‌توانید این روش‌ها را در پروژه‌های تحلیل داده خود به کار ببرید و نتایج دقیق‌تر و جذاب‌تری ارائه دهید.
دیدگاهتان را بنویسید