SQL->DAX: How to create a self-joined table with greater than sign in join

[ruut]

I have written the following Access SQL query:

SELECT 
  t1.Field2, 
  t1.Field1 AS Field1A, 
  t2.Field1 AS Field1B, 
  sum(t2.Field4) AS Summed
FROM
  Table1 AS t1 
INNER JOIN 
  Table1 AS t2 
      ON 
         t1.Field1 >= t2.Field1 AND 
         t1.Field2 = t2.Field2 AND 
         t1.Field3 = t2.Field3
WHERE 
  t1.Field3 = 1
GROUP BY 
  t1.Field2, 
  t1.Field1, 
  t2.Field1

which I would like to rewrite in Power BI DAX language. How to get started?

Note that Table1 is joined with itself and note the '>=' sign in the INNER JOIN condition.

Your help would be appreciated a lot. 

[v-ljerr-msft · Accepted solution]

Hi @ruut,

After a few research and test, I found a solution using M query instead DAX in your scenario. 

1. Duplicate Table1 in Query Editor.

2. Create a Blank Query, and enter the following M query in Advanced Editor.

let
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each [Field1]<=Earlier[Field1] and [Field2]=Earlier[Field2] and [Field3]=Earlier[Field3])),
    #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Field1", "Field4"}, {"RelativeJoin.Field1", "RelativeJoin.Field4"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded RelativeJoin",{"Field2", "Field1", "Field3", "Field4", "RelativeJoin.Field1", "RelativeJoin.Field4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Field3", "Field4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Field1", "Field1A"}, {"RelativeJoin.Field1", "Field1B"}, {"RelativeJoin.Field4", "Summed"}})
in
   #"Renamed Columns"

Here is the sample pbix file for your reference. 

Regards

[BalaVenuGopal]

Hi @v-ljerr-msft ,

The approach which you have suggested is greate like taek duplicate data set then join then select what ever the colulmns we requeid.

But the same thing we can achieve through DAX also . But what is the difference and which one is fater and best practice .

Please suggest.

[ruut]

Hi @v-ljerr-msft, 

Thanks for the solution. This is exactly what I needed. For lager input tables, the query is quite slow. Do you have any suggestions to increase the performance? 

Best, 

Ruut

[fhill]

Can you post sample table data and your desired output?

FOrrest

[ruut]

Input:

Output: