"Missing Relationship Between the Fields" why?

[Anonymous]

I have 3 tables in my Power BI model:

Table 1 - has a unique equipment ID for each record. I successfully joined Table 1 to Table 2 and Table 3 using the Equipment ID.

I can create a matrix using fields from Table 1 and Table 2 OR Table 1 and Table 3. BUT I get the error "There is a missing realtionship between these fields. Use autodetect..." when I try to include fields from Table 2 AND Table 3.

I don't understand why the model can't understand that there is an inherent relationship between tables 2 and 3.

Z

[v-juanli-msft]

Hi @Anonymous

For the relationship,Table2(many)-Table1(one)-Table3(many),

When you include fields from Table 2 AND Table 3 in the table or matrix visual, It can't detemine which value to match with the two tables, for they are have many values to each id in the table1.

You need to use aggregation to get data from Table 2 and Table 3.

Best Regards

Maggie

[WolfBiber]

Hi,

I don't know exactly your datamodel, but maybe this is the solution:

double click on the connections between the tables in Relationview and change "Cross filter direction" to Both

Greetings

[Anonymous]

Thanks. I do have "both" selected.  My model is real basic - not much more to it then these 3 simple tables with the Equipment ID fields as the join in the one-to-many relationships.

[WolfBiber]

can you post a screenshot from you datamodel?

[Anonymous]

Here is the data model. Thanks: