CODEWITHSUNDEEP
powerbidaxdaxstudio
sam
sam

Reputation: 1304

How to do left outer join of two tables without any relationship in DAX using naturalLeftJoin or Generate all functions

I have 2 sample tables, Table "EX4" and "Dates". Now I want to create a third table in DAX which joins dates with EX4 table.

enter image description here

Now I want to join EX4 table with Dates[Sale_Date] where

Dates[Sale_Date] >= Ex4_Ext[min_date] and Dates[Sale_Date] <= Ex4_Ext[max_Date]

The output should be as follows:

enter image description here

I am using below DAX code and is not giving the correct output because its not doing left join. It is doing Cartesian product.

Ex4_Ext = 
var tbl = SELECTCOLUMNS(EX4,"min_date",EX4[Min_Date],"max_Date",EX4[Sale_Date])
var tbl2 = SELECTCOLUMNS(Dates,"sale_date1",date(year(Dates[Sale_Date]),MONTH(Dates[Sale_Date]),Day(Dates[Sale_Date])))
Return
generate(tbl,tbl2)

I Cannot have a relationship between these two tables..Neither Generate all nor Naturalleftjoin is working..

Any help will be appreciated..

Upvotes: 0

Views: 1899

Answers (1)

sam
sam

Reputation: 1304

After a long hit and try method I found the solution. Below DAX query can join without relationship:

Ex4_Ext = 
var tbl = SELECTCOLUMNS(EX4,"min_date",EX4[Min_Date],"max_Date",EX4[Sale_Date])
var tbl2 = generate(tbl,DATESBETWEEN(Dates[Sale_Date],[min_date],[max_Date]))
Return
SELECTCOLUMNS(tbl2,"Min_Date",[min_date],"Max_Date",[max_Date],"Sale_Date",DATE(year([Sale_Date]),month([Sale_Date]),1))

Got help from youtube video : https://www.youtube.com/watch?v=U_7mFutB5OM

Generate function when used with DATESBETWEEN function acts as Inner join

Upvotes: 1

Related Questions