Can't display the data because Power BI can't determine the relationship between two or more fields.

[Anonymous]

Hi @Anonymous 

Ticket_Reference is a middle table. You relate it with other two tables by PersonID columns.

Now if you build a table visual with TicketID(Ticket_Reference), FirstName(Person_Reference_Customer) and FirstName(Person_Reference_Technician), you will get an error.

We can find that if we only build a table visual by TicketID(Ticket_Reference) with one of the FirstName, it works well.

That's because when you add two FirstName column from two tables, Power BI is confused about how to expand your table by relationship.

I reproduced your error.

That's your relationship. When we only build a table visual by TicketID(Ticket_Reference) with one of the FirstName, Power BI know how to expand the table.  But if we add three columns from three tables, power bi is confused about which one it should choose, expand with Person_Reference_Customer first, or Person_Reference_Technician. It couldn't expand twice by itself, due to data model.

It is better for you to build a new merge table, because of power bi logic doesn't support you to expand your table by relationship as above.

According to your statement, you don't want to merge three visuals, you want to keep these Person tables be as seperate tables in model.  You can use merge as a new to build a new merge table instead of merge three tables directly.

Best Regards,

Rico Zhou

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

[Anonymous]

Thank you so much for the re-constructing the model and responding.

When power bi is expanding from ticket to other person tables in a single direction, ticket table will filter data in the other 2 person tables and show records relates to selected ticketid present in person tables correct ? or this will not happen because at any point of time power bi can apply filter to one direction of model ?

About merging tables.

1) As I wanted to keep these 3 tables as seperate individual tables per business need, I did not tried merging.

2) After that I do not have no other option, I went ahead and tried to merge tables. But when I am trying to merge, I am getting an error saying "this operation is not allowed in direct query mode" because ticket table is pulled into power bi using advanced sql in direct query mode. Found out that when you use override sql to pull fields into power bi it is not allowing to do anyother power query tranformations.

Let me know your thoughts. Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous 

If you can change your data model as below, you can achieve your goal by relationship.

Result:

Your original relationship, Power BI will be confused about no related between person tables. So it couldn't expand correctly.

Your connection mode is Direct Query. Merge as New Query and change your connection mode to Mixed mode is also a good choice. You original tables are still direct query mode but your new table is import.

Best Regards,

Rico Zhou

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

[Anonymous]

Hi Hasham,

Yes whatever columns you mentioned in the post are correct, But since I want to use Tickets table as master table and use customer/techinician as left join tables. I am giving filter direction as single to propagate filters from Tickets to Customers/Technician.

When I select the FirstName column from both Customer/Technician into Visual then i am getting error, If I just pull Firstname from any one table it is working.

Do you know how to achieve this ?

1) I do want to merge tables for left outer join in power query because I want these Person tables be as seperate tables in model not merged into a single table. how to acheive Left join functionality from the data model ?

[KNP]

Hi @Anonymous, 

In the diagram provided, it looks like the 1-many join is going in the wrong direction (if I understand your model correctly). The 1 side of the relationship should be on the 'PERSON...' tables and the many on the 'TICKETS...' table as @HashamNiaz suggested.

Then when you need to reference the customer or technician in the visuals, you do it from the 'PERSON...' tables. 

Also, you've said a couple of times that 

---

@Anonymous wrote:
I do want to merge tables for left outer join in power query because I want these Person tables be as seperate tables in model not merged into a single table...

---

I assumed you meant "I do not want to merge...", is that correct?

[HashamNiaz]

Hi @Anonymous !

You didn't mention the column names you used in relationship. Please post your Data Model diagram.

Ideally your model sould look like this;

Tables:

Person_Reference_Technician (Only include unique Technicians, with PersonID as key column)
Person_Reference_Customer (Only include unique Customers, with PersonID as key column)

Tickets_Referece (Includes unique tickets, with TicketID as key column, PersonID to be used with Cusotmer table for joining, TechnicianID to be used with Technician table for joining)

Relationships:

1-Many Relationship between [Person_Reference_Technician] & [Tickets_Referece] tables based on columns [PersonID] from Technician table & [TechnicianID] from Tickets table
1-Many Relationship between [Person_Reference_Customer] & [Tickets_Referece] tables based on columns [PersonID] from Customer table & [PersonID] from Tickets table

Regards,

Hasham