Culprit of Implementing Left Join a Subset of Table

[KylieF]

I have these two tables:

I want to implement something equivalent to that in SQL:

select table1.EnrolmentLead_Key, table1.child_last_name, table2.guardian_last_name
from table1
left join table2 on table1.EnrolmentLead_Key = table2.EnrolmentLead_Key and table2.is_primary = 'True'

 And the expected result is like: 

The question is how to implement in Power BI to select the data from a semantic model. If I do:

Either:

Or 

Neither of them work.

[Anonymous · Accepted solution]

Hi @KylieF ,

The Table data is shown below:

‘is_primary’: The data type is 'text'

Please follow these steps:
1. Use the following DAX expression to create a table

Table = SUMMARIZE ( 'Table1',
'Table1'[EnrolmentLead_Key],'Table1'[child_last_name],
"guardian_last_name",CALCULATE (MAX('Table2'[guardian_last_name]),'Table2'[EnrolementLead_Key] = EARLIER ( 'Table1'[EnrolmentLead_Key] ),'Table2'[is_primary] = "true" ))

2. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly