Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ruut
Frequent Visitor

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. 

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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.

d1.PNG

 

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"

blankquery.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

5 REPLIES 5
v-ljerr-msft
Employee
Employee

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.

d1.PNG

 

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"

blankquery.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

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.

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
Resident Rockstar
Resident Rockstar

Can you post sample table data and your desired output?

 

FOrrest




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




ruut
Frequent Visitor

Input:

2017-10-06 17_15_34-Access - PECO_PhaseIII_BudgetTracker - many hidden objects _ Database- C__key ar.png

Output:

2017-10-09 10_39_27-Access - PECO_PhaseIII_BudgetTracker - many hidden objects _ Database- C__key ar.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.