Left Join in Direct Query mode with one-to-many relationship

[Hitu]

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

[v-tejrama]

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!

[v-tejrama]

Hi @Hitu ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[rohit1991]

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])

[Hitu]

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.

[v-tejrama]

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
• 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.

[Hitu]

Hi @v-tejrama 
Thanking you for your response and sharing the Pbix. In my the joing condition is different the Joining condition is 1**N from Table A to Table B. If we do this kind of join the related function will not work in Table A.
In our case the joining will be like below 

The requirement is to have all the rows from table A then Left Join with table B and then left Join with table c  in direct query mode 

[v-tejrama]

Hi @Hitu ,

Thanks for your question. I’m confident this will clear things up. Here’s what you need to know:

1. When you use 'Table'[ID] = CurrentRow directly inside CALCULATE, it acts as a column filter, not a row-level context.
2. By wrapping your condition in FILTER('Table', 'Table'[ID] = CurrentRow), DAX evaluates each row individually, which is essential for calculated columns.
3. My Power BI model (see the screenshot) demonstrates this: joined columns from related tables and applied filters work as expected because the filter context is set up correctly.
   
   4.The main point: for calculated columns, especially with variables, always use FILTER(...)
   when comparing row values. This guarantees CALCULATE processes it as a row-by-row operation.

I’ve attached the screenshots for your reference.

Thank you.

[Hitu]

Hi @v-tejrama 

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.

one-to-many relationship.pbix 

[v-tejrama · Accepted solution]

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.