left outer join with out creating new query or table. is any DAX query

[deepakb18]

Hi Expert

I have blow scenerio.

I don’t want to use merge table or create new table using create table option or NATURALLEFTOUTERJOIN(as it will create new table). Is any Dax(measure or Dax column) can meet my purpose.

[deepakb18 · Accepted solution]

I have got solution from Microsoft person and i understood that bydefault whenever we create a relationship, Power BI implment INNER JOIN by default however the values are in the table, but are hidden by default since Power BI only shows accounts that have any transactions. To include empty accounts as well, change the Account attribute binding to “Show items with no data"

AND This worked for me. sometime easy solution of difficult issues.

[v-lili6-msft]

hi  @deepakb18 

Just rename one of the [Account] column eg. one is [Account] and another one is [Account 1].

Then use NATURALLEFTOUTERJOIN Function as below:

New Table = NATURALLEFTOUTERJOIN(Account,'Transaction')

Or adjust it as below:

New Table = 
--NATURALLEFTOUTERJOIN(Account,'Transaction')
SELECTCOLUMNS(NATURALLEFTOUTERJOIN(Account,'Transaction'),"Account",[Account 1],"Desc",[Desc],"Transld",[Transld],"DebitAmount",[DebitAmount],"CreditAmount",[CreditAmount])

Result:

Regards,

Lin

[deepakb18]

Hi Lin
Thanks for your reply but i dont want to create a new table using Natural Joins as data model is alredy heavy and it took longer time for pulling data and data model to get refresh.

[Pragati11]

Hi @deepakb18 ,

If you want 3rd table (as in your screenshot) just a  visual in Power BI, then you can create a relationship between both of o your tables - Account, Transaction on "Account" column whihc is a common column in both.

Then create a table visual in your Power BI report, moving all the relevant fields, as in your third table. You will get desired output.

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[deepakb18]

Well relationship is already there

[Pragati11]

Hi @deepakb18 ,

then try creating the table visual on your report (like your 3rd table in the screenshot).

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[amitchandak]

@deepakb18 

You should refer this and try in Edit Queries

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

You can use Cross Join in Dax but that is costly

filter(Crossjoin(Table1,table2),conditions)