Forum Discussion

ruut's avatar
ruut
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's avatar
    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's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft 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

ruut's avatar
ruut
Frequent Visitor
8 years ago

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