Help with Error "Can't determine relationships between the fields"

[ERing]

I'm struggling to create visuals and keep getting the error message below. My visual is a simple table with DIM_CUSTOMER.FULL_NAME and DIM_ADDRESS.ADDRESS_STREET. I get the error message as soon as I drag in field from DIM_JOBS. The data model has been provided to me by my data engineering team, but I continue to have problem.

Can anyone help me understand where the issue is?

[Anonymous]

Hi,TomMartens ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@ERing .I am glad to help you.

Based on your description and screenshots, I have come to the following conclusions.
1. The connection mode of your data model is Direct Query.
2. There is a limited relationship in your data model.
This kind of model relationship is not favourable for your subsequent report creation (Direct Query connection mode and limited relationship are not friendly for setting up RLS)

It is very important to set up the relationship correctly, so you need to contact your team members to do this important work together.
(This is my suggestion, you can also choose not to modify the model structure if the limited relationship doesn't affect your report production )

I hope you find my suggestions below helpful.
Try to modify limited relationship
This situation usually occurs when there are many-to-many relationships in the model. Please try to modify the many-to-many relationship in the model. Try to create bridged tables, by creating bridged tables as a medium for intermediate joins between [DIM_JOBS] table and [DIM_CUSTOMER_ADDRESS_MAPPING] table.
This is more conducive to optimising the data model
like this.
You can use VALUES function to create a single list as a bridge to intermediate connections.

Attention needs to be paid to the direction of the relationship filter between the two fields.

2. Ensure that the relationship between tables passes in the right direction.
Use one-way filtering whenever possible, and two-way filtering only when necessary
Unidirectional filtering requires that data can only be filtered from one table to another, usually from a dimension table to a fact table.
If you need to use bi-directional filtering, especially if you are in a many-to-many relationship, please create bridged tables as an intermediate medium.
We hope that the following issues will help you.
URL:

Solved: RLS on DirectQuery - Microsoft Fabric Community

For the problem you are experiencing: Error ‘Can't determine relationships between the fields’.
Error ‘Can't determine relationships between the fields’
I hope the following issue is helpful.
The workaround is to set the fields to an aggregated state, which sometimes solves the problem (similar to creating a measure as suggested by TomMartens)
URL:
Solved: Relationship between Translation and Data Tables - Microsoft Fabric Community

Some common limitations in Direct Query connection mode
URL:

DirectQuery in Power BI - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[TomMartens]

Hey @ERing ,

This is expected whenever columns from different tables that are not related by a chain of 1-->--* (one-to-many) relationships.
The column State is not connected to any column from the jobs table.

Nevertheless, it's simple to create a virtual relationship. This can be done by adding a measure to the visual like the one below:

Measure = 
42

I recommend creating a more meaningful measure, though, e.g., counting the rows of the jobs table.

Regarding the model, I would not use the table "Dim_Customer_Address_Mapping." Instead I would create relationship between the Dim_Customer and Dim_Address tables.

Hopefully, this will help you overcome your issue.

Regards,

Tom

[ERing]

"The column State is not connected to any column from the jobs table." Which column are you reffering to?

Also, where do I create the measure? I don't understand the guidance with creating the measure and adding it to the visual.