Forum Discussion
Left Join in Direct Query mode with one-to-many relationship
Hi All,
I have a requirement to do a left join between three dimenssion tables based on a ID column. All three tables are in direct query mode. The joining condition will be A to B (1**N) and A to C(1**N). The requirement is to have all rows from table A and matching records from B and C .Merge query will not work as it will change the resultant table into Import mode. Selecting "Show item with no data" will also not give correct result as once it is joined third table it will not work. One joining in model view with One to Many and dragging column in table visual it is throwing "Cannot dtermine relationship between the fields"
Requesting your valuable advice and suggestion
Hi Hitu ,
Thank you for sharing the updated details and the PBIX file. I’ve reviewed the relationship setup and the DirectQuery error you encountered.
Power BI requires a single, clear relationship path between fields for DirectQuery to work correctly. In your model, the join from Table A to Table C goes through Table B, which functions in Import mode but can cause issues in DirectQuery since Power BI must generate a single SQL query at runtime. If the join is ambiguous, you’ll see the “Can’t determine relationships between the fields” error.
The most effective solution is to prepare the join at the source. You can either create a database view that joins Table A, Table B, and Table C, or build a bridge table at the source with the necessary keys and columns for direct relationships in your model.
If source modification isn’t feasible, you can use a DAX workaround like TREATAS or LOOKUPVALUE, though performance will depend on your dataset and source.
Thank you,Tejaswi.
9 Replies
Hi Hitu
To simulate a left join between A >>B and A >> C (both 1**N):
1. Create relationships:
A to B and A to C, both 1**N, single direction.2. Use RELATED() in A:
Create calculated columns in A to pull fields from B and C:B_Status_Joined = RELATED(TableB[B_Status]) C_Category_Joined = RELATED(TableC[C_Category])Hi rohit1991
Thanking you for giving the the solution.
I have tried the approach. How ever we can not add column in the 1 side of the table using realted function in a 1**N relationship. Beacuse for a single record in the A table have multiple record in B table, so it will not be able to decide which value to show from B for a particular value in A.Hi Hitu ,
Here are the steps :
- Confirm column names
Ensure that the column referenced in the RELATED() function exists in the related table. Double-check for any typos or recent field name changes. - Check table relationships
In Model view, verify that there is an active one-to-many relationship between the base table (where you are creating the RELATED() column) and the lookup table (containing the referenced column).- For this scenario:
- TableA[ID] represents the "one" side
- Connected to TableB[ID] and TableC[ID] on the "many" side
- For this scenario:
- Activate relationships if needed
If you see a dashed line, the relationship is inactive. Right-click and select “Make this relationship active.” - Apply RELATED in the correct direction
The RELATED() function retrieves values only from the "one" side of the relationship to the "many" side.- For example, values can be pulled from TableB to TableA only if TableB is the lookup table.
- Validate in visuals and slicers
Add slicers for B_Status_Joined and C_Category_Joined from TableA. They should now display the joined values correctly, without blanks or errors.
I’ve attached the .pbix file and screenshots for your reference.
Thank you.Tejaswi.
- Confirm column names
Hi Hitu ,
Thank you rohit1991 for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you for your understanding!
