Table visual trouble

[MarkPalmberg]

I have three queries in my data model. One contains unique IDs for all the customers in my database. The second contains address information for my customers (multiple addresses per customer), and the third contains order categories for my customers (multiple categories per customer). The second and third tables are connected to the first via two-way, one-to-many relationships. I’d like to create a table visualization that contains the customer ID from the first table, address information from the second table, and order categories from the third table.

When I add the ID from table one and the address info from table two to my table visualization, everything’s fine. But when I then try to add product category, I get an error that Power BI can’t determine a relationship between two or more fields. I can also add the ID and product category to a table visualization, but I then get the same error if I try to add any address information. At least I know that the ID is actually working between table one and tables two and three...they just won’t all display in the same table visualization.

Is my only recourse to merge the queries? Thanks a lot for any ideas you may have!

[v-xicai]

Hi  @MarkPalmberg  ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[MarkPalmberg]

Yes, @v-xicai , it makes sense. I was hoping not to have to merge these queries, but it looks like that may be the best option. 

So, to clarify, is the error I'm getting expected behavior in Power Bi? I.e., does trying to bring in fields from two tables on the one-to-many side of a relationship create an insurmountable query issue?

Thanks again!

[v-xicai]

Hi @MarkPalmberg ,

There are several solutions you can considerate:

1.You may try using the CROSSJOIN function to create a calculated table like DAX below.

Table = CROSSJOIN(Table1,Table2,Table3)

2.You may use Merge Queries (Table.NestedJoin) in Query Editor.

3.You may use the merge feature in Query Editor.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.