Forum Discussion
Left Join in Direct Query mode with one-to-many relationship
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.
Hi Anonymous
Thanking you for your detailed response. But in our requirment the joining condition is different. It is 1 to N from Table A to Table B. I have done the relationship change in the attached pbix.
The output i am looking for is below
However i am getting the below error
Please note merge query can not be used as it in direct query mode.
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.