درک خطای "can’t determine relationship between the fields" در Power BI

در Power BI، هنگام ساختن یک matrix، شما ستون‌هایی را درون ماتریس می‌کشید و رها می‌کنید (drag and drop) و سپس چند measure اضافه می‌کنید. Power BI به‌صورت خودکار تشخیص می‌دهد که کدام ترکیب از مقادیر را باید نمایش دهد. این فرایند آن‌قدر شهودی است که اغلب جزئیات آن را نادیده می‌گیریم.
با این حال، گاهی اوقات Power BI نمی‌تواند تشخیص دهد که چطور باید ماتریس را مقداردهی کند و در نتیجه خطای زیر را نمایش می‌دهد:

“can’t determine relationship between the fields”

اضافه کردن یک measure این مشکل را برطرف می‌کند، اما چرا؟
در برخی سناریوهای دیگر، Power BI ردیف‌های خالی زیادی را نشان می‌دهد که تنها زمانی بسیاری از آن‌ها حذف می‌شوند که یک measure اضافه شود. حتی در برخی موارد دیگر، Power BI فقط بخشی از مقادیر را نشان می‌دهد، حتی وقتی که هیچ measureای وجود ندارد.

این رفتار نتیجه‌ی ترکیبی از ویژگی‌هاست: برخی از این ویژگی‌ها مربوط به DAX هستند، در حالی که برخی دیگر به ویژگی‌های Power BI برمی‌گردند.
زمانی که هیچ measureای در کار نباشد، Power BI کوئری را به شیوه‌ای خاص ایجاد می‌کند؛ در این حالت از یک bridge table برای اتصال جداول مورد استفاده در ماتریس استفاده می‌شود.
از طرف دیگر، DAX ویژگی‌هایی مانند auto-exists و non-empty را در توابعی مثل SUMMARIZECOLUMNS پیاده‌سازی می‌کند.

بیایید کمی عمیق‌تر شویم و ابتدا ویژگی‌های DAX را بهتر بشناسیم.

معرفی نحوه اجرای تابع SUMMARIZECOLUMNS

Power BI از تابع SUMMARIZECOLUMNS به‌عنوان تابع اصلی برای انجام کوئری‌ها استفاده می‌کند. تقریباً تمام visual‌ها از SUMMARIZECOLUMNS برای بازیابی مقادیری که باید نمایش داده شوند، بهره می‌برند.
به عنوان مثال، نمونه‌ی زیر یک matrix ساده است که شامل Product[Category]، Product[Subcategory] و Sales Amount measure می‌باشد.

می‌توانید مشاهده کنید که تمام ترکیب‌های ممکن از category و subcategory نمایش داده نمی‌شوند. برای مثال، ترکیب Audio/Televisions در لیست وجود ندارد. کوئری زیر وظیفه‌ی مقداردهی به این matrix را بر عهده دارد:

تابع SUMMARIZECOLUMNS برای group by کردن چهار ستون به کار می‌رود (دسته‌بندی‌ها بر اساس category code مرتب می‌شوند و زیر‌دسته‌ها بر اساس subcategory code؛ بنابراین هر چهار ستون باید در لیست group-by حضور داشته باشند) و همچنین برای محاسبه مقدار Sales Amount.

تابع SUMMARIZECOLUMNS فهرستی از تمام ترکیب‌های موجود از این چهار ستون را تولید می‌کند.
از آنجایی که تمام این ستون‌ها از یک جدول (یعنی جدول Product) هستند، عملکرد SUMMARIZECOLUMNS مشابه با تابع SUMMARIZE است که جدول Product را بر اساس این چهار ستون گروه‌بندی کرده، مقدار measure را محاسبه کرده، و سپس ردیف‌هایی را که نتیجه خالی دارند حذف می‌کند.

به عبارت دیگر، این کد معادل با کد زیر است:

بخش SUMMARIZE به عنوان ویژگی auto-exists شناخته می‌شود. SUMMARIZE تنها ترکیب‌های موجود از مقادیر را تولید می‌کند. این کار باعث کاهش تعداد ردیف‌هایی می‌شود که باید ارزیابی شوند و تمرکز را فقط روی ترکیب‌هایی می‌گذارد که معنا دارند.

بخش FILTER به عنوان ویژگی non-empty شناخته می‌شود. SUMMARIZE ترکیب‌هایی را که نتیجه‌ای خالی تولید می‌کنند، از خروجی حذف می‌کند.

نکته قابل توجه این است که SUMMARIZE از جدول Product و نه جدول Sales، به عنوان جدول مبنای grouping استفاده می‌کند. فعلاً این نکته جزئی را در ذهن داشته باشید؛ بعداً مفید خواهد بود.

همان‌طور که گفتیم، تمام ترکیب‌های ممکن از category و subcategory در matrix وجود ندارند. ممکن است ترکیبی از مقادیر به دلیل یکی از دو عملیات auto-exists یا non-empty نمایش داده نشود. نتیجه نهایی در هر دو حالت یکی است: ترکیب مورد نظر در خروجی وجود ندارد.
با این حال، ترکیب‌هایی که توسط non-empty حذف شده‌اند قابل نمایش مجدد هستند، در حالی که ترکیب‌هایی که توسط auto-exists حذف شده‌اند هیچ‌گاه قابل نمایش نیستند.

در Power BI می‌توانید از گزینه‌ی “Show items with no data” استفاده کنید تا ترکیب‌هایی را که توسط non-empty پنهان شده‌اند، نمایش دهید.

برای مثال، بیایید یک slicer اضافه کنیم تا فقط یک شهر را فیلتر کند. این باعث کاهش تعداد ترکیب‌های قابل مشاهده می‌شود.

وارد کردن گزینه Show items with no data در Power BI باعث می‌شود که تمام categories قابل مشاهده باشند و برای هر دسته، زیر‌دسته‌های مربوطه نیز نمایش داده شوند. با این حال، ترکیب‌های نامعتبر از category و subcategory نمایش داده نمی‌شوند.

به دلیل ویژگی auto-exists، ستون‌هایی که از یک جدول هستند با استفاده از SUMMARIZE گروه‌بندی می‌شوند.
اما وقتی ستون‌هایی از جداول مختلف اضافه می‌کنید، چه اتفاقی می‌افتد؟
در این حالت، دیگر نمی‌توان از SUMMARIZE استفاده کرد و جداول با استفاده از CROSSJOIN ترکیب می‌شوند.

در ماتریس زیر، ستون‌های Store[Country] و Store[State] به ستون‌ها اضافه شده‌اند و فیلتر روی یک شهر خاص همچنان حفظ شده است.
همان‌طور که مشاهده می‌کنید، فقط دو فروشگاه وجود دارند که به مشتریانی که در شهر Aalen زندگی می‌کنند، کالا می‌فروشند.

از آنجایی که دو جدول در کوئری دخیل هستند، هر دو جدول به‌صورت جداگانه خلاصه (summarize) می‌شوند و سپس نتایج آن‌ها با استفاده از cross-join ترکیب می‌شود. این عملیات معادل کوئری زیر است:

جزئیات مهم در مورد کوئری اخیر این است که جدول خلاصه‌شده (summarized table) همانطور که ممکن است انتظار رود جدول Sales نیست.
دو جدولی که در کوئری دخیل هستند (Store و Product) به‌صورت جداگانه خلاصه می‌شوند و سپس با cross-join ترکیب می‌شوند.
فضای حاصل بسیار بزرگ است و شامل ترکیب‌های زیادی از ستون‌های Store و Product است که هیچ مقداری برای Sales Amount measure تولید نمی‌کنند. بعداً، عملیات non-empty آن ردیف‌های خالی را که هنوز ارزیابی شده‌اند حذف می‌کند.

این موضوع به‌وضوح زمانی دیده می‌شود که تعریف Sales Amount را تغییر دهیم و صفر را به آن اضافه کنیم تا از مقادیر خالی (blanks) جلوگیری شود:

این تغییر کوچک، ماتریس را به یک هیولا تبدیل می‌کند که شامل ردیف‌ها و ستون‌های بسیار زیادی است و همه مقادیر آن صفر را نمایش می‌دهند.

رفتار مشابهی را می‌توان با استفاده از ویژگی “Show items with no data” مشاهده کرد.
بنابراین، ما مهم‌ترین نکات درباره SUMMARIZECOLUMNS را یاد گرفته‌ایم: ستون‌هایی که از یک جدول هستند خلاصه می‌شوند، نتایج آن‌ها با cross-join ترکیب می‌شود و non-empty ردیف‌های خالی را حذف می‌کند.
از آنجایی که non-empty بیشتر ترکیب‌ها را حذف می‌کند، measure مورد استفاده در ماتریس نقش کلیدی در تعیین آنچه در ماتریس نمایش داده می‌شود، دارد. اگر تمام measureها در یک ردیف خاص مقدار خالی (blank) داشته باشند، آن ردیف از خروجی حذف می‌شود.

درک خطا

بنابراین، measureهای استفاده شده در ماتریس تعیین می‌کنند کدام ردیف‌ها نمایش داده شوند. اگر هیچ measureای استفاده نشود، آنگاه SUMMARIZECOLUMNS تمام ترکیب‌های حاصل از cross-join را تولید می‌کند. با این حال، به شکلی نسبتاً شگفت‌انگیز، اگر Sales Amount measure را از ماتریس حذف کنیم، نتیجه‌ی زیر حاصل می‌شود.

با اینکه هیچ مقداری نمایش داده نمی‌شود، تنها برخی از ترکیب‌ها قابل مشاهده هستند. یعنی همان مجموعه‌ای از مقادیر که قبلاً وقتی Sales Amount وجود داشت نمایش داده می‌شدند.

این رفتار به DAX بستگی ندارد. Power BI تشخیص داده است که هیچ measureای برای ترکیب مشخصی از ستون‌های مدل محاسبه نمی‌شود، و سعی می‌کند با تغییر کوئری، از نمایش تمام ترکیب‌های حاصل از cross-join جلوگیری کند.
Power BI (و نه DAX) تعیین کرده است که دو جدولی که در ماتریس استفاده می‌شوند، هر دو به جدول Sales مرتبط هستند. بنابراین، کوئری‌ای تولید می‌کند که شامل یک measure می‌شود که تعداد ردیف‌های جدول Sales را می‌شمارد تا بر ویژگی non-empty در SUMMARIZECOLUMNS تکیه کند و تعداد ردیف‌های بازگردانده شده را کاهش دهد.

این یک نسخه ساده‌شده از کوئری اجرا شده است:

همان‌طور که می‌بینید، این Power BI است که به SUMMARIZECOLUMNS دستور می‌دهد از جدول Sales به‌عنوان یک bridge table برای کاهش تعداد ردیف‌ها استفاده کند.
اگر چنین جدولی وجود نداشته باشد — یعنی جدولی که بتواند توسط جداول مورد استفاده در ماتریس فیلتر شود و نقش یک bridge طبیعی را ایفا کند — Power BI خطا تولید می‌کند.

می‌توانیم به سادگی این موضوع را با ایجاد یک نسخه کپی از جدول Store در یک جدول محاسباتی جدید به نام Unrelated Store بررسی کنیم:

استفاده از ستون‌هایی از جدول Unrelated Store به جای جدول Store باعث بروز خطا در ماتریس می‌شود.

اضافه کردن هر measureی به ماتریس، خطا را رفع می‌کند، زیرا—اگر measureای وجود داشته باشد—Power BI به جای استفاده از الگوریتم خود (که جستجو برای یافتن یک bridge table مناسب است)، بر DAX تکیه می‌کند تا مقادیر قابل نمایش را تعیین کند.

نتیجه‌گیری

وقتی در یک ماتریس از measure استفاده می‌کنید، خود measure مسئول تعیین ردیف‌های قابل نمایش است.
دو مکانیزم در این فرآیند دخیل هستند: non-empty و auto-exists.
Auto-exists فقط ترکیب‌های معتبر مقادیر ستون‌های یک جدول را نشان می‌دهد.
Non-empty ردیف‌هایی را حذف می‌کند که تمام measureها در آن‌ها مقدار blank دارند، از خروجی SUMMARIZECOLUMNS.

اگر هیچ measureای وجود نداشت، SUMMARIZECOLUMNS نتیجه بزرگی تولید می‌کرد چون تمام جداول خلاصه‌شده را به صورت cross-join ترکیب می‌کرد.
Power BI با استفاده از یک bridge table جلوی بازگرداندن این مجموعه داده بزرگ را می‌گیرد و تعداد ردیف‌های نمایش داده شده را کاهش می‌دهد.

اگر bridge table مناسبی پیدا نشود، Power BI خطا تولید می‌کند. رفع این مشکل ساده است: فقط کافیست یک measure اضافه کنید تا مشخص کنید کدام ترکیب‌ها باید نمایش داده شوند.