0

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

sam
  • 1,242
  • 3
  • 12
  • 31
  • Here's a Power Query solution: https://stackoverflow.com/questions/74521206/powerbi-counting-days-in-a-range-on-multiple-line-for-staff-absence-purposes/74521418#74521418 – David Browne - Microsoft Nov 27 '22 at 16:14
  • Can we not solve this in DAX? Will Generate all , Generate and Naturalleftjoin functions can only work with relationship? I have found a solution in the video : https://www.youtube.com/watch?v=U_7mFutB5OM. But the solution is not working correctly for me as advised in this video – sam Nov 27 '22 at 16:30
  • Yes, although schema transformations are more commonly done in Power Query, so similar solutions are easier to find. But DAX is a very capable language, and calculated tables in DAX don't have any performance penalty. The only downside to solving this in DAX is that the base tables have to be loaded as well. – David Browne - Microsoft Nov 27 '22 at 17:11

1 Answers1

1

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

sam
  • 1,242
  • 3
  • 12
  • 31