PowerBi linking tables Can't Determine relationship

[Milas_jonas]

Hi all,

Newbie here so please accept my apologies if this is something too simple. 

I am working with structured data stored in oracle data warehouse. I am finding it impossible to bring data from one table to another!

I am using direct query.

Table1: F_Properties (Data size 74K rows)

Table2: F_WRK_Orders (Data size over 1Million rows - although for this excercise filtered to less)

The unique key identifier in F_Property is Prop_Ref. There are no duplicates here. The other table, WRK_Orders will have many works orders raised against the same property, therfore the column Prop_Ref will have duplicates. 

I am trying to link the tables so to bring more information in the WRK_Order table from the F_Property table. I have linked Prop_Ref to Prop_ref Many to One(*:1) in a Single filter direction.

On the report page, when I pull data from the F_Properties table, I get this error:

"Can't Determine relationship between the fields. Can't display the data because PowerBi can't determine the relationship between two or more fields. "

10 hours and can't figure it out! What am I doing wrong?

Any help would be very grateful.

Many thanks in advance

[Anonymous]

Hi @Milas_jonas ,

Thanks for the reply from @amitchandak and @Wilson_ .

Start by clearing the relationship you created between the two tables by dragging the Prop_Ref column of the F_Properties table to the Prop_Ref column of the F_WRK_Orders table in the Model view of Power BI Desktop.

Double-check the "one" and "many" ends of the relationship. Verify that the relationship is active and set to a single filter direction from F_Properties to F_WRK_Orders.

A data type mismatch between columns in two tables may cause problems. Ensure that the data types are consistent.

For example, the Prop_Ref columns in both tables are text or both are numeric.

A null value or a blank value in a Prop_Ref column in either table can sometimes cause unexpected behavior. Verify that there are no null values or blank spaces in the Prop_Ref column.

You should now be able to establish a relationship between the two tables via the Prop_Ref column.

If it is not resolved, it is fine if you can provide me with a screenshot of the error and a screenshot of the model, please remember to erase the sensitive information.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Milas_jonas]

Hello,

Thank you very much for response and advise, much appreciated.

I have done some data cleanse as per your message and I have come accross some null values. The problem that I have is that still need this Null values as they have another Unique Key on another Column, that I can link it to the F_Properties table to obtain the same data from that unique key.

Bareing in mind this is Direct Query and the data is over 2 million rows, which is the best way to also link this ? 

So on I need to link also the Property_Admin_Unit_Code to Admin_Unit_Code.

So if Null value on the Prop_Ref in the Works_Order data table, then use the Admin_Unit_Ref to bring bring the admin unit data set from the property table.

Many Thanks

Many Thanks in advance

How can I do that?

[amitchandak]

@Milas_jonas , Check the join might be inactive.

[Wilson_]

Hi Milas_jonas,

Can you please show the model view in your pbix?

[Milas_jonas]

Hello @Wilson_ 

Please see below.

I have coppied F_WRK_ORD table and kept the NULL Values and created the new table F_WRK_ORD_AU. The Admin Unit data is in the D_Admin table. Thank you for helping out