سيناريوهات DAX في Power Pivot

ينطبق على
Excel لـ Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

يوفر هذا القسم ارتباطات إلى أمثلة توضح استخدام صيغ DAX في السيناريوهات التالية.

  • إجراء العمليات الحسابية المعقدة
  • استخدام النصوص والتواريخ
  • القيم الشرطية واختبار الأخطاء
  • استخدام تحليل معلومات الوقت
  • تصنيف القيم ومقارنتها

في هذه المقالة

بدء الاستخدام

تفضل بزيارة موقع Wiki الخاص بمركز موارد DAX حيث يمكنك العثور على جميع أنواع المعلومات حول DAX بما في ذلك المدونات والعينات والمستندات التقنية ومقاطع الفيديو التي يوفرها محترفون رائدون في المجال وMicrosoft.

السيناريوهات: إجراء حسابات معقدة

بإمكان صيغ DAX إجراء عمليات حسابية معقدة تتضمن تجميعات مخصصة وتصفية واستخدام القيم الشرطية. يوفر هذا المقطع أمثلة حول كيفية بدء استخدام العمليات الحسابية المخصصة.

إنشاء عمليات حسابية مخصصة ل PivotTable

تعد الدالتان CALCULATE وCALCULATETABLE دالتين فعالتين ومرنة ويفيدان في تعريف الحقول المحسوبة. تتيح لك هذه الدالات تغيير السياق الذي سيتم إجراء العملية الحسابية فيه. يمكنك أيضا تخصيص نوع التجميع أو العملية الرياضية المطلوب تنفيذها. اطلع على المواضيع التالية للحصول على أمثلة.

تطبيق عامل تصفية على صيغة

في معظم الأماكن حيث تأخذ دالة DAX جدولا كوسيطة، يمكنك عادة تمرير جدول تمت تصفيته بدلا من ذلك، إما باستخدام الدالة FILTER بدلا من اسم الجدول، أو باستخدام تعبير عامل تصفية كأحد وسيطات الدالة. توفر المواضيع التالية أمثلة حول كيفية إنشاء عوامل التصفية وكيفية تأثير عوامل التصفية على نتائج الصيغ. للحصول على مزيد من المعلومات، راجع تصفية البيانات في صيغ DAX.

تتيح لك الدالة FILTER تحديد معايير التصفية باستخدام تعبير، بينما تم تصميم الدالات الأخرى بشكل خاص لتصفية القيم الفارغة.

إزالة عوامل التصفية بشكل انتقائي لإنشاء نسبة ديناميكية

من خلال إنشاء عوامل التصفية الديناميكية في الصيغ، يمكنك الإجابة بسهولة على أسئلة مثل التالية:

  • ماذا كانت مساهمة مبيعات المنتج الحالي في إجمالي المبيعات لهذا العام؟
  • كم ساهم هذا القسم في إجمالي الأرباح لجميع سنوات التشغيل، مقارنة بالأقسام الأخرى؟

يمكن أن تتأثر الصيغ التي تستخدمها في PivotTable بسياق PivotTable، ولكن يمكنك تغيير السياق بشكل انتقائي عن طريق إضافة عوامل التصفية أو إزالتها. يوضح لك المثال في الموضوع "الكل" كيفية القيام بذلك. لحساب نسبة المبيعات لموزع معين على مبيعات جميع الموزعين، يمكنك إنشاء مقياس يحسب قيمة السياق الحالي مقسومة على قيمة السياق ALL.

يوفر الموضوع ALLEXCEPT مثال على كيفية مسح عوامل التصفية في صيغة ما بشكل انتقائي يرشدك كلا المثالين إلى كيفية تغيير النتائج استنادا إلى تصميم PivotTable.

للحصول على أمثلة أخرى حول كيفية حساب النسب والنسب المئوية، راجع المواضيع التالية:

استخدام قيمة من حلقة خارجية

بالإضافة إلى استخدام القيم من السياق الحالي في العمليات الحسابية، يمكن ل DAX استخدام قيمة من حلقة سابقة في إنشاء مجموعة من العمليات الحسابية ذات الصلة. يقدم الموضوع التالي معاينة حول كيفية إنشاء صيغة تشير إلى قيمة من حلقة خارجية. تدعم الدالة EARLIER حتى مستويين من الحلقات المتداخلة.

لمعرفة المزيد حول سياق الصفوف والجداول ذات الصلة، وكيفية استخدام هذا المفهوم في الصيغ، راجع السياق في صيغ DAX.

السيناريوهات: استخدام النصوص والتواريخ

يوفر هذا القسم ارتباطات إلى موضوعات مرجعية DAX تحتوي على أمثلة على السيناريوهات الشائعة التي تتضمن العمل باستخدام النص أو استخراج قيم التاريخ والوقت وتكوينها أو إنشاء قيم تستند إلى شرط.

إنشاء عمود رئيسي بالتسلسل

لا يسمح Power Pivot بالمفاتيح المركبة؛ وبالتالي، إذا كان لديك مفاتيح مركبة في مصدر البيانات، فقد تحتاج إلى دمجها في عمود مفتاح واحد. يقدم الموضوع التالي مثالا واحدا حول كيفية إنشاء عمود محسوب استنادا إلى مفتاح مركب.

Compose تاريخ استنادا إلى أجزاء التاريخ المستخرجة من تاريخ نصي

يستخدم Power Pivot نوع بيانات تاريخ/وقت SQL Server لاستخدام التواريخ؛ وبالتالي إذا كانت البيانات الخارجية تحتوي على تواريخ منسقة بشكل مختلف -- على سبيل المثال، إذا كانت التواريخ مكتوبة بتنسيق تاريخ إقليمي لا يتعرف عليه محرك بيانات Power Pivot، أو إذا كانت بياناتك تستخدم مفاتيح بديلة للأعداد الصحيحة -- فقد تحتاج إلى استخدام صيغة DAX لاستخراج أجزاء التاريخ ثم إنشاء الأجزاء في تاريخ صالح. تمثيل الوقت.

على سبيل المثال، إذا كان لديك عمود تواريخ تم تمثيلها كعدد صحيح ثم استيراده كسلسلة نصية، يمكنك تحويل السلسلة إلى قيمة تاريخ/وقت باستخدام الصيغة التالية:

=DATE(RIGHT([value1],4),LEFT([Value1],2),MID([Value1],2))

Value1 النتيجة
01032009 1/3/2009
12132008 12/13/2008
06252007 6/25/2007

توفر المواضيع التالية المزيد من المعلومات حول الدالات المستخدمة لاستخراج التواريخ وإنشاءها.

تعريف تنسيق أرقام أو تاريخ مخصص

إذا كانت البيانات تحتوي على تواريخ أو أرقام غير ممثلة بأحد تنسيقات النص القياسية في Windows، فيمكنك تعريف تنسيق مخصص لضمان التعامل مع القيم بشكل صحيح. تستخدم هذه التنسيقات عند تحويل القيم إلى سلاسل أو من سلاسل. كما توفر المواضيع التالية قائمة مفصلة بالتنسيقات المعرفة مسبقا والمتوفرة للعمل باستخدام التواريخ والأرقام.

تغيير أنواع البيانات باستخدام صيغة

في Power Pivot، يتم تحديد نوع بيانات الإخراج بواسطة الأعمدة المصدر، ولا يمكنك تحديد نوع بيانات النتيجة بشكل واضح، لأن نوع البيانات الأمثل يحدده Power Pivot. ومع ذلك، يمكنك استخدام تحويلات نوع البيانات الضمنية التي يقوم بها Power Pivot لمعالجة نوع بيانات الإخراج. 

  • لتحويل تاريخ أو سلسلة أرقام إلى رقم، اضرب في 1.0. على سبيل المثال، تحسب الصيغة التالية التاريخ الحالي ناقص 3 أيام، ثم تنتج قيمة العدد الصحيح المقابل.
    =(TODAY()-3)*1.0
  • لتحويل قيمة تاريخ أو رقم أو عملة إلى سلسلة، أنشئ سلسلة القيمة باستخدام سلسلة فارغة. على سبيل المثال، ترجع الصيغة التالية تاريخ اليوم كسلسلة.
    =""& TODAY()

يمكن أيضا استخدام الدالات التالية لضمان إرجاع نوع بيانات معين:

تحويل الأرقام الحقيقية إلى أعداد صحيحة

السيناريو: القيم الشرطية واختبار الأخطاء

مثل Excel، يحتوي DAX على دالات تتيح لك اختبار القيم في البيانات وإرجاع قيمة مختلفة استنادا إلى شرط. على سبيل المثال، يمكنك إنشاء عمود محسوب يقوم بتسمية البائعين إما كمفضلين أو كقيمة استنادا إلى مبلغ المبيعات السنوية. تعد الدالات التي تختبر القيم مفيدة أيضا في التحقق من نطاق القيم أو نوعها لمنع أخطاء البيانات غير المتوقعة من كسر العمليات الحسابية.

إنشاء قيمة تستند إلى شرط

يمكنك استخدام شروط IF المتداخلة لاختبار القيم وإنشاء قيم جديدة بشكل مشروط. تحتوي المواضيع التالية على بعض الأمثلة البسيطة حول المعالجة الشرطية والقيم الشرطية:

اختبار الأخطاء داخل صيغة

بخلاف Excel، لا يمكن وجود قيم صالحة في أحد صفوف عمود محسوب وقيم غير صالحة في صف آخر. وهذا يعني أنه في حال وجود خطأ في أي جزء من عمود Power Pivot، فسيتم وضع علامة على العمود بأكمله بخطأ، لذا يجب عليك دائما تصحيح أخطاء الصيغ التي ينتج عنها قيم غير صحيحة.

على سبيل المثال، إذا قمت بإنشاء صيغة تقسم على صفر، فقد تحصل على الناتج اللانهائي أو خطأ. ستفشل بعض الصيغ أيضا إذا واجهت الدالة قيمة فارغة عندما تتوقع قيمة رقمية. أثناء تطوير نموذج البيانات، من الأفضل السماح بالأخطاء بالظهور بحيث يمكنك النقر فوق الرسالة واستكشاف المشكلة وإصلاحها. ومع ذلك، عند نشر مصنفات، يجب تضمين معالجة الأخطاء لمنع القيم غير المتوقعة من التسبب في فشل العمليات الحسابية.

لتجنب إرجاع الأخطاء في عمود محسوب، يمكنك استخدام مجموعة من الدالات المنطقية ودالات المعلومات لاختبار الأخطاء وإرجاع القيم الصالحة دائما. توفر الموضوعات التالية بعض الأمثلة بسيطة حول كيفية القيام بذلك في DAX:

السيناريوهات: استخدام تحليل معلومات الوقت

تتضمن دالات تحليل معلومات الوقت في DAX دالات تساعدك على استرداد التواريخ أو نطاقات التواريخ من بياناتك. يمكنك عندئذ استخدام هذه التواريخ أو نطاقات التواريخ لحساب القيم عبر فترات مماثلة. تتضمن دالات تحليل معلومات الوقت أيضا دالات تعمل مع فواصل زمنية تواريخ قياسية، للسماح لك بمقارنة القيم عبر الأشهر أو السنوات أو أرباع السنة. يمكنك أيضا إنشاء صيغة تقارن بين قيم التاريخ الأول والأخير في فترة محددة.

للحصول على قائمة بجميع دالات تحليل معلومات الوقت، انظر دالات معلومات الوقت (DAX). للحصول على تلميحات حول كيفية استخدام التواريخ والأوقات بفعالية في تحليل Power Pivot، راجع التواريخ في Power Pivot.

حساب المبيعات المتراكمة

تحتوي المواضيع التالية على أمثلة حول كيفية حساب الأرصدة المغلقة والافتتاحية. تتيح لك الأمثلة إنشاء أرصدة تشغيل عبر فواصل زمنية مختلفة مثل الأيام أو الأشهر أو أرباع السنة أو السنوات.

مقارنة القيم مع مرور الوقت

تحتوي المواضيع التالية على أمثلة حول كيفية مقارنة المجاميع عبر فترات زمنية مختلفة. الفترات الزمنية الافتراضية التي يدعمها DAX هي الأشهر وأرباع السنة والسنوات.

حساب قيمة عبر نطاق تاريخ مخصص

راجع المواضيع التالية للحصول على أمثلة حول كيفية استرداد نطاقات التاريخ المخصصة، مثل أول 15 يوما بعد بدء العرض الترويجي للمبيعات.

إذا كنت تستخدم دالات تحليل معلومات الوقت لاسترداد مجموعة مخصصة من التواريخ، فيمكنك استخدام مجموعة التواريخ هذه كإدخال لدالة تجري حسابات لإنشاء تجميعات مخصصة عبر الفترات الزمنية. راجع الموضوع التالي للحصول على مثال حول كيفية القيام بذلك:

  • الدالة PARALLELPERIOD

    ملاحظة

    إذا لم تكن بحاجة إلى تحديد نطاق تاريخ مخصص، ولكنك تعمل باستخدام وحدات محاسبية قياسية مثل الأشهر أو أرباع السنة أو السنوات، فإننا ننصحك بإجراء العمليات الحسابية باستخدام دالات تحليل معلومات الوقت المصممة لهذا الغرض، مثل TOTALQTD وTOTALMTD وTOTALQTD وما إلى ذلك.

السيناريوهات: تصنيف القيم ومقارنتها

لإظهار العدد العلوي n فقط من العناصر في عمود أو PivotTable، تتوفر لديك عدة خيارات:

  • يمكنك استخدام الميزات في Excel لإنشاء عامل تصفية أعلى. يمكنك أيضا تحديد عدد من القيم العليا أو الدنيا في PivotTable. يصف الجزء الأول من هذا القسم كيفية تصفية أهم 10 عناصر في PivotTable. لمزيد من المعلومات، راجع وثائق Excel.
  • يمكنك إنشاء صيغة تقوم بتصنيف القيم بشكل ديناميكي، ثم التصفية حسب قيم التصنيف، أو استخدام قيمة المرتبة كمقسم طريقة عرض. يصف الجزء الثاني من هذا القسم كيفية إنشاء هذه الصيغة ومن ثم استخدام هذا الترتيب في مقسم طريقة عرض.

هناك مزايا وعيوب لكل طريقة.

  • عامل التصفية الأعلى في Excel سهل الاستخدام، ولكن عامل التصفية لأغراض العرض فقط. إذا تغيرت البيانات الأساسية في PivotTable، فيجب تحديث PivotTable يدويا لرؤية التغييرات. إذا كنت بحاجة إلى العمل بشكل ديناميكي مع التصنيفات، يمكنك استخدام DAX لإنشاء صيغة تقارن القيم بالقيم الأخرى داخل عمود.
  • صيغة DAX أكثر قوة. علاوة على ذلك، عند إضافة قيمة المرتبة إلى مقسم طريقة عرض، ما عليك سوى النقر فوق مقسم طريقة العرض لتغيير عدد القيم العليا التي يتم عرضها. ومع ذلك، تعد العمليات الحسابية مكلفة من الناحية الحسابية وقد لا تكون هذه الطريقة مناسبة للجداول ذات صفوف متعددة.

إظهار العناصر العشرة الأولى فقط في PivotTable

لإظهار القيم العليا أو السفلى في PivotTable
  1. في PivotTable، انقر فوق السهم لأسفل في عنوان " تسميات الصفوف ".
  2. حدد أهم10عوامل تصفية> القيم.
  3. في مربع الحوار "اسم> عمود ال 10 الأعليا"<، اختر العمود المطلوب ترتيبه وعدد القيم، كما يلي:
    1. حدد "أعلى" لرؤية الخلايا ذات القيم الأعلى أو حدد "أسفل" لرؤية الخلايا ذات القيم الدنيا.
    2. اكتب عدد القيم العليا أو الدنيا التي تريد رؤيتها. القيمة الافتراضية هي 10.
    3. حدد الطريقة التي تريد بها عرض القيم:
الاسمDescriptionItemsحدد هذا الخيار لتصفية PivotTable بحيث تظهر قائمة العناصر العليا أو الدنيا فقط حسب قيمها. النسبة المئويةحدد هذا الخيار لتصفية PivotTable بحيث لا تظهر سوى العناصر التي تصل إلى النسبة المئوية المحددة. المجموعحدد هذا الخيار لعرض مجموع قيم العناصر العليا أو الدنيا.
  1. حدد العمود الذي يحتوي على القيم التي تريد ترتيبها.
  2. انقر فوق موافق.

ترتيب العناصر ديناميكيا باستخدام صيغة

يحتوي الموضوع التالي على مثال حول كيفية استخدام DAX لإنشاء تصنيف مخزن في عمود محسوب. نظرا لأنه يتم حساب صيغ DAX ديناميكيا، يمكنك دائما التأكد من صحة الترتيب حتى إذا تغيرت البيانات الأساسية. وأيضا، ونظرا لاستخدام الصيغة في عمود محسوب، يمكنك استخدام الترتيب في مقسم طريقة عرض ثم تحديد أعلى 5 أو أعلى 10 أو حتى أعلى 100 قيم.