Data Modeling: Relationship Error while fetching data from 3 tables

[PBI_Member_01]

Hello Power BI Community,

I am trying to fetch data from three different tables which are connected in my Data Model but the relationship error gets thrown. The screenshot below explains the data points and how the tables are interconnected within my PBI File.

Now, if I bring Cust_Code from RLS Cust Details (Table A) in a table visual and Occupation_Name from Occupation table (Table B), nothing breaks and the information is shown correctly.
Similarly, if I bring Occupation_name from Occupation table (Table B) and Geo_ID from Occupation Geo Bridge table (Table C), it shows the information correctly. Attaching screenshots below for these two points:

But If I bring information from these 3 tables altogether i:e Cust_Code from Table A, Occupation_Name from Table B and Geo_ID from Table C, I get the error: Power BI can't determine the relationship between two or more fields.

Also, If I add a slicer to the report, and use Cust_Code from Table A to slice the information of Table B and Table C, that still works and slices the information correctly.

I am building the model from scratch and trying to understand the behaviour of relationships in this case to avoid any bottlenecks. Why am I being thrown with an error as shown in the screenshot, but at the same being able to slice the information for that particular table.

Also, for one of my other project files, the same functionality is applied and I am able to select the data from each table collectively in one table. Is there any particular concept which is involved here that I am not aware of.

Any help on this would be highly appreciated. Please let me know if more information is required from my end.

Thanks in advance.

[Daniel29195]

Hello @PBI_Member_01 

try adding a measure from table : occuption geo ,  

this should fix the problem .

if not, if possible, please share the pbix file so i can take a look 

best regards

[PBI_Member_01]

Hello Daniel,

Thanks for your input on this.

If I add a summarized column, it does show the information, but if I include unsummarized column, the relationship error gets thrown. My end goal would be to show descriptive information from this table.

My question would be, What if instead of summarized data, I want to display some unsummarized column's information on the table? Why would that cause a relationship error?

Also, as shown in the screenshot above, why is the unsummarized column, from Table A, able to slice the data correctly for Table C and no error is thrown at that point?

I am just trying to understand the reason behind this behaviour so that I can modify my Data Model accordingly.

Thanks again.

[Daniel29195]

@PBI_Member_01 

https://forum.enterprisedna.co/t/cant-display-because-power-bi-does-determine-the-relationship-betwe...

https://www.fpk3.com/blog/cant-determine-relationships-between-fields-bidirectional

[PBI_Member_01]

Thank you for providing relevant material to this problem, Daniel.

For the issues discussed in the attached links, I see that there is a bi-directional relationship on either side of the two tables, whereas in my case, there is only one bidirectional relationship with the occupation table while the occupation table is single directional towards the occupation geo bridge table.

Still finding it difficult to understand why the relationship would break in this case since there isn't bidirectional on both sides of the Table B, it exists only between Table A and Table B.

[Daniel29195]

@PBI_Member_01 

ok so, 

when you dont add any measure or summarized column in the visual, what power bi does internally is that it adds a hidden calculation, which is (

CALCULATE(COUNTROWS('table_from_the_many_side')) of the relationship

although you cant tell what is happening, im assuming, because you have 2 tables on the many side of the relationship,

and you are using them in the table as dont summarize,  power bi is trying to create that internal hidden measure ( since you didnt add a measure into the visual ) , but since the 2 tables, 

which are , rls cust  and occupation geo  are not linked ,  (  by that i mean that occupation geo cant filter rls cust details or relate to it in any way ) , 

that is why the problem occurs.

now you can change the second relationship to bi-directional, and still the error will still occurs, 

but here, i dont think it is worth to dig deeper, cz no one wants to create a model with random and multiple bi-directional filters which in this case, the model would be complex and hard to understand the result you are getting in the visuals , especially when you start authoring some complex dax code. 

hope this helps. 

[Daniel29195]

@PBI_Member_01 

can you please share the pbix file. it is easier for me to interpret and answer your questions . 

you can share via a link to google drive or dropbox if you want. 

best regards

[PBI_Member_01]

Sure, here is the link for the file:

PBIX File