Data Model Validation - Aggregated vs. Tabular

mfiery · ‎06-11-2018

Hi,

I am in the process of building out a data model involving about 10-15 tables in total for a broader reporting project. In order to ensure I had everything set up correctly, I started some basic data validation and ran into an issue I haven't been able to resolve myself.

Here is a screenshot of my simplified model to make this easier to discuss. The three tables, MDS Jobs, Work Orders and IRR are what I want to validate.

The center table always contains one record per work order. The other two tables will have a many to one relationship (Or may not have any records associated) with the Work Order table, all joining on the 'Work Order' value. In the vast majority of instances, the Work Order table and MDS Job table would be reported on together - I've set that cross-filter direction to both since in most instances I'll need those to function as a single table.

In the Report that I'm using to validate, I want to see the number of IRR records for each Work Order and MDS job combination. Simple enough, I drag the first two columns, MDS Job and Work Order over and then summarize for the count of IRR records - this works correctly. In my example I see the total of six - this is correct and I've validated for that particular combination. However, if I want to see each of those unique IRR records associated with the Work Order and MDS job combination, six rows in my table rather than aggregated to one, I set the IRR records to 'do not summarize' and I receive an error indicating that PowerBI couldn't determine a relationship - 'Can't display the data because PowerBI can't determine the relationship between two or more fields.' This seems odd given that it was able to correctly identify the count correctly.

Is there an issue with how my model is set up or is this a limitation (I can't imagine this to be the case) with PowerBI? There are other workarounds to resolve this but as I build out the larger model those work arounds would become unmanageable - so I need to ensure I'm working off of a preferred/best practice approach that I believe can be resolved at this level.

Thanks for your assistance,

Matt

Anonymous · ‎06-12-2018

@mfiery wrote:

However, if I want to see each of those unique IRR records associated with the Work Order and MDS job combination, six rows in my table rather than aggregated to one, I set the IRR records to 'do not summarize' and I receive an error indicating that PowerBI couldn't determine a relationship - 'Can't display the data because PowerBI can't determine the relationship between two or more fields.' This seems odd given that it was able to correctly identify the count correctly.

Is there an issue with how my model is set up or is this a limitation (I can't imagine this to be the case) with PowerBI? There are other workarounds to resolve this but as I build out the larger model those work arounds would become unmanageable - so I need to ensure I'm working off of a preferred/best practice approach that I believe can be resolved at this level.

@mfiery,

The above issue is a known issue. Please check the PG's response.

"This is a known bug in Power BI when using groups from a chain of N:1:N relationships. However, we cannot provide an ETA for a fix.

The customer can work around this bug by adding a model measure to the visual. The DAX generation logic for visual queries treats model measures as if they could relate any combination of groups. Therefore, adding a model measure to a visual effectively disables the check for unconstrained joins that is triggering the error message shown below. That will let the customer work around this bug."

Regards,
Lydia