Joins in DAX

Joins in DAX

  June 22, 2023    asghars

---

• DAX relationship has some limitations like:
• The matching criteria always use the = operator and does not allow the use of other operators such as <>, >=, <, and <= .
• Supports only one-to-many and one-to-one relationships.
• In order to overcome these limitations, DAX provides the capability to utilize join functions.
• CROSSJOIN(tab1, tab2, .......)
• CROSSJOIN join function returns a table that is a Cartesian product of the specified tables.
• Join that involves Cartesian product is also known as Full outer join.

• GENERATE(tab1, tab2)
• GENERATE join function return a table that is a Cartesian product of only two different tables.
• Join that involves Cartesian product is also known as Full outer join.
• By using FILTER function with GENERATE:
• We can use more than one matching conditions including operators other than =.



• We can create inner join i.e. only matching rows from tab1 and tab2.

• FILTER function is the DAX equivalent of WHERE clause in a T-SQL statement.
• When GENERATE function returns a calculated table then it must be make sure that both tables have unique column names.
• Using SELECTCOLUMNS(tab, col_new_name, exp_ret_col, [tab, col_new_name, exp_ret_col], …) function we can rename columns. 
  

• NATURALINNERJOIN (left_tab, right_tab)
• NATURALINNERJOIN function is used to perform Inner join i.e. only matching rows are selected from left and right tables.
• This join requires that both tables should belong to same physical source table or same lineage. 
• NATURALLEFTOUTERJOIN(left_tab, right_tab)
• NATURALLEFTOUTERJOIN function is used to perform Left outer join i.e. all rows from left table and only matching rows from right table are selected.
• EXCEPT(left_tab, right_tab)
• EXCEPT function perform Left Anti join i.e returns all rows from left table that are not matched in right table.

Download PBIX file