Forum Discussion

Frequent Visitor

8 years ago

Solved

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

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

Need Help

v-ljerr-msft

8 years ago

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

Regards

v-ljerr-msft

Microsoft Employee

8 years ago

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

Regards