Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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
Can you post sample table data and your desired output?
FOrrest
Proud to give back to the community!
Thank You!
Input:
Output:
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |