Cant Make a Visual Report with More than 2 Tables as Datasources saying Can't Determine Relationship

[I_NeedMorePower]

Hi Power community !

I'm unable to display a visual report (e.g, a table visual) that has 3 fields from 3 different datasources.

I have 3 datasources (tables):

1- EmployeeTable

2- EmpContractTable

3- EmpSurvey

If I tried to create a table report showing the follwoing field from each of these tables:

Field1: EmployeeTable.EmpID

Field2: EmpContractTable.ContractStartDate

Field3: EmpSurvey.SurveyDescription

Once I add a 3rd datasource to the table visual, the visual breaks and shows the following:

Quoting the error message in the picture above: "Can't determine relationships between the fields See details".

If I click on see details, it shows the follows:

The relationships done in modeling are as follows:

1- EmployeeTable.EmpID 1 -> * EmployeeContract.EmpID

2- EmployeeTable.EmpID 1 -> * EmployeeSurvey.EmpID

Relation Screenshot:

It's a 1 to * between the Employee table and the other tables, so the EmployeeTable is the medium.

Even though there are many records for the same employee in both of the contract table and in Survery table, shouldn't the table visual display duplicated records and not break?

e.g, Employee1 have 2 contracts (1st contract is old and not active, and 2nd contract is the newest and active)

and this Employee1 has also 2 surveys he participated in.

Should't the table visual display the results as follows:

Employee1	Contract1	Survey1
Employee1	Contract1	Survey2
Employee1	Contract2	Survey1
Employee1	Contract2	Survey2

Kindly let me know where i'm mistaken.

I don't want the visual to break, I want the visual to show me duplicated records. I just want the table visual to show me the intersected employees that have contracts and have survy records.

Any advice is appreciated !

Thanks in advance !

[lbendlin · Accepted solution]

create a measure and put that in the values area.

[I_NeedMorePower]

Hi @lbendlin and @Anonymous !

I really appreciate your time for explaination and showing solutions thanks alot !

Now the picture is clear.

I also found that once the relationship between Employee table and EmpContract tables are 1 to 1, it will work, beause as you mentioned that it can be hard to figure which survey belongs to which contract.

So by creating measures and using DAX, I can bypass the problem.

Thanks again !

[Anonymous]

Hi @I_NeedMorePower ,

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[Anonymous · Accepted solution]

Hi ALL,
Firstly  lbendlin thank you for your solution!
And @I_NeedMorePower , When Power BI handles relationships between multiple tables, although you have established relationships between the EmployeeTable and two other tables (EmpContractTable and EmpSurvey), these relationships exist only between the EmployeeTable and the individual tables. In other words, the EmployeeTable acts as an intermediary between the other two tables, but the EmpContractTable and EmpSurvey tables themselves are not directly related,  When you try to put fields from EmpContractTable and EmpSurvey together in the same visualization report, Power BI does not automatically understand how the data from these two tables should be combined.

Here are my two solutions for your reference, the first one is to use merge in power query to do the merge to get the data you want and avoid the complexity of the data.

The second is to use DAX to get data from other tables to populate this table, that is, to use DAX to extract the relevant fields.

SurveyDescription = 
CALCULATE(
    FIRSTNONBLANK(EmpSurveyTable[SurveyDescription], EmpSurveyTable[SurveyDescription]),
    FILTER(EmpSurveyTable, EmpSurveyTable[EmpID] = EmpContractTable[EmpID])
)

I hope my answer is helpful to you, if you still have questions you can check my pbix file, if you have further questions you can contact me at any time, I will reply to you as soon as I receive your message, looking forward to your reply!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[lbendlin · Accepted solution]

create a measure and put that in the values area.