Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there a difference between EXCEPT function in DAX and Left Join in SQL?
Also, is there a difference between INTERSECT function in DAX and Inner Join in SQL?
Solved! Go to Solution.
EXCEPT returns all the rows from Table1 which are not in Table2
Table1 = A, B, C, D
Table2 = B,C,E,F,G
Except(Table1,Table2) = A, D
Except Table2, Table1 ) = E, F, G
The left join is different because it returns always all rows from Table1 and the matching rows from table 2 in another column
Table1 Left Join Table2 =
Col1 - Col 2
A - empty
B - B
C - C
D - empty
Intersect and Inner Join are more similar because they both returns only the matching rows between table1 and table2
INTERSECT( Table1, Table2 ) = B, C
INTERSECT in DAX looks at all the columns to find matches whereas in the INNER JOIN in SQL you can specity at which column to look and the same is true for EXCEP and LEFT JOIN.
In Power Query you can also perform these joins easily and specify the columns as you do in SQL. Look at the Table.NestedJoin function in the M reference website
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Answer to Q#1: No
Explanation: EXCEPT returns the rows of the left side table, which do not appear on the right side table.
EXCEPT takes in two tables as arguements i.e. =EXCEPT(table1,table2). It's output is a table which has all the records from table1 which don't appear in table 2. If there is a matching record, it ignores it. Ex: If Table1 has A,B and C and Table2 has B, D and F, the output table will have only A and C. A left join in SQL would have included B as well. Furthermore, unlike SQL, EXCEPT matches entire records or rows, not columns. For this purpose, table1 and table2 need to have equal number of columns.
Answer to Q#2: Sort of Yes.
Explanation: INTERSECT returns the rows from table1 which appear in table2.
In the above example, it would only return B.
Again, important to remember is INTERSECT matches entire records, not columns. Hence, it's almost inner join but not completely.
EXCEPT returns all the rows from Table1 which are not in Table2
Table1 = A, B, C, D
Table2 = B,C,E,F,G
Except(Table1,Table2) = A, D
Except Table2, Table1 ) = E, F, G
The left join is different because it returns always all rows from Table1 and the matching rows from table 2 in another column
Table1 Left Join Table2 =
Col1 - Col 2
A - empty
B - B
C - C
D - empty
Intersect and Inner Join are more similar because they both returns only the matching rows between table1 and table2
INTERSECT( Table1, Table2 ) = B, C
INTERSECT in DAX looks at all the columns to find matches whereas in the INNER JOIN in SQL you can specity at which column to look and the same is true for EXCEP and LEFT JOIN.
In Power Query you can also perform these joins easily and specify the columns as you do in SQL. Look at the Table.NestedJoin function in the M reference website
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |