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

 

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

5 Replies

  • 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

    • BalaVenuGopal's avatar
      BalaVenuGopal
      Icon for Resolver I rankResolver I
      8 years ago

      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.

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Can you post sample table data and your desired output?

     

    FOrrest