left join in Report

[amitkala17]

Hello,

i have two query in my report and i am trying to implement left join, here is the scenario,

i have a main table called Emp, and one other table Organaziation, i want to see all the record from emp and only matching record from Organaziation,

at the moment it is showing only the matched record.

Please suggest

[v-yuezhe-msft · Accepted solution]

Hi @amitkala17,

Based on my test, NATURALLEFTOUTERJOIN doesn’t work in this scenario.

In Query Editor, click on the Emp table, then select “Merge Queries as New” to merge the two tables, then expand the orgname column in the merged table. It doesn’t impact on your current tables using the merge query feature to get expected result.

Thanks,
Lydia Zhang

[Iypnp39]

hii 
@amitkala17 
I Create a report This type of merge (left join),Report Locally working good but i published on Workspace(web cloud) ,Sheduled Refresh(Update) is not working.You have any idea pls give it.

[amitkala17]

Thanks Dear,

i got this one, 

what i understood is by default all queries are linked with inner join, and if we have a requirement of left/right join we need to create a seprate query.

correct me if i am wrong.

[spuder]

hi @amitkala17

unfortunately this is the wrong place for your project.

You better use the Query Editor (Power Query) and merge the tables. There you can choose which join type you'd like to have.

[amitkala17]

Hello,

i do not want to use merge query,

so it mean query's support only inner join? 

[amitkala17]

empno & fname is from Emp table & Orgname is from Organazation Table,matched record is appering in the last, and organization that does not have employee are appearing in top.

but what i want is to get all employee and only matching org ,if no match org will be null

[spuder]

OK. Understand try the DAX Function =NATURALLEFTOUTERJOIN()

[amitkala17]

where will i add this function?

[imadera]

check out this video, it better explains how to control the SQL that Power BI automatically creates...https://www.youtube.com/watch?v=UgqVSEoj4BM