Unable to show data from related tables at the same time

[Anonymous]

Hi all,

I am having problems with the data of a report. The problem is that, in my model, I have more than one 1-* relationship with other tables, and when I try to show the information of all tables, I get the "Can't display this visual" message, about Power BI not being able to relate two or more fields.

The model is something like the following:

So the green relationship from Projects to Clients (*-1) is OK; and having only one of the red relationships is also OK (either Projects-Orders OR Projects-Other table), but when I put fields of all four tables (including the always-ok Clients table), I get the error message (for having more than one 1-* relationship, I assume).

I checked this post, but the Accepted solutio about making the relationship bidireccional is NOT WORKING for me in my model. I have also tried to 'merge' the data in one single table, but there is too much information and it is definitely not an option.

Any suggested solutions? Should I do something specific for the bidirectional relationship to work? This issue is really blocking me and I find no suitable solution in Power BI.

Regards,

Antonio

[pwc_ananta]

Hi @BILASolution 

Thanks for providing the update on this issue. 

I am new on Power BI. I am facing the similar type of issue. What is the way around for showing the data of all three sources in a single table visual. I have also created the bi-directional relationships but it didn't working.

Now I am joining all the tables in SQL developer to get the data in one table. But I think my approach is not very vaible to this problem. There is some other solution which I don't get it yet.

Please guide!

Regards

[BILASolution]

@pwc_ananta 

Below the Project table, the two tables are different characteristics of that, so doesnt exist any relationship. Those are two tasks or areas completely different.

Thanks : )

[BILASolution · Accepted solution]

Hi @Anonymous

The reason for that error message is... There isn't a relationship between "Orders" and "Other Table", when you try to put some fields from all tables at the same time, power bi can't do a real merge between tables. Check the pictures below...

 

It's impossible to merge both tables (second picture), that's the reason. An alternative I propose is work separately each table.

I hope it helps

Regards

BILASolution

[Anonymous]

Hello,

Thank you for your response. The 'Orders' and 'Other table' are, in fact, related: by the Projects.

Project has two IDs, one is the Project ID which I use to join with both Clients and Orders, and a File ID with I use for that Other table. So, one Project may have +1 Order and +1 File, but for every project I have the Project ID and the File ID. So they should be relatable.

Furthermore, if I get the information only from one of the red relationships separately, I can get all the information regarding a Project and its Orders (or its Files from Other table).

If the information is there and it is, in fact, related, why is Power BI unable to merge it?

Regards,

[BILASolution]

@Anonymous

If "FileID" is inside the "Projects" table, That means both tables (Projects and Other Table) are in One to One relationship. Other possibility, Projects is a result of a many to many relationship. I'm trying to understand the situation, I could be wrong. 

[Anonymous]

I think I get the problem. I can have either Orders or the Other tables information, but having both at the same time, althought they are 'connected' via Project, PBI can not know which Order has which Other table data.

Still, I am not convinced about the bidirectional relationship not working... that should, for the cases it can, show the relationship and for those who can't, should show blanks, right?

Regards