Advanced DAX - Joins

So today we are going to learn what are Join function in Power BI Advanced Data Analysis Expression.

Introduction to Power BI DAX :- 

DAX (Data Analysis Expressions) is a formula expression language and can be used in different BI and visualization tools. DAX is also known as function language, where the full code is kept inside a function. DAX programming formula contains two data types: Numeric and Other. Numeric includes - integers, currency and decimals, while Other includes: string and binary object.

Join Function:-

Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are as follows: 

1. CROSSJOIN
2. UNION
3. EXCEPT
4. INTERSECT

Now we will learn each function in details

1. CROSSJOIN : 

• Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables.

Remarks :

• Column names from table arguments must all be different in all tables or an error is returned.
• This function is not supported for use in Direct Query mode when used in calculated columns or row-level security (RLS) rules.

Syntax : 

CROSSJOIN(<table>, <table>[, <table>]…)

Example : 

The following example shows the results of applying CROSSJOIN() to two tables

CrossJoin =

CROSSJOIN(

VALUES('Product Lookup'[product_group]),

VALUES('Product'[product_category])

),

"#ProductCount",CALCULATE(COUNTROWS('Product Lookup'))

),

'Product'[product_category] in {"Coffee","Tea"}

)

Output : 

2. UNION :

• Creates a union (join) table from a pair of tables.

Return : 

• A table that contains all the rows from each of the two table expressions.

Remarks : 

• The two tables must have the same number of columns.
• Columns are combined by position in their respective tables.
• The column names in the return table will match the column names in table_expression1.
• Duplicate rows are retained.
• The returned table has lineage where possible. For example, if the first column of each table_expression has lineage to the same base column C1 in the model, the first column in the UNION result will have lineage to C1. However, if combined columns have lineage to different base columns, or if there is an extension column, the resulting column in UNION will have no lineage.
• When data types differ, the resulting data type is determined based on the rules for data type coercion.
• The returned table will not contain columns from related tables.

Syntax :

UNION(<table_expression1>, <table_expression2> [,<table_expression>]…)

Example :

Union_join =

VAR Month = VALUES('Calendar'[Month_Name])

VAR MonthEnd = {"june","july"}

VAR Unionmonth = UNION(Month,MonthEnd)

RETURN

Unionmonth

Output :

3. EXCEPT : 

• Returns the rows of one table which do not appear in another table.

Return : 

A table that contains the rows of one table minus all the rows of another table.

Remarks : 

• If a row appears at all in both tables, it and its duplicates are not present in the result set. If a row appears in only table_expression1, it and its duplicates will appear in the result set.
• The column names will match the column names in table_expression1.
• Columns are compared based on positioning, and data comparison with no type coercion.
• The set of rows returned depends on the order of the two expressions.
• The returned table does not include columns from tables related to table_expression1.

Syntax : 

EXCEPT(<table_expression1>, <table_expression2>

Example : 

Except =

Except_storesales =

VAR Month = VALUES('Calendar'[Month_Name])

VAR MonthEnd = {"june","july","january","March"}

VAR exceptmonth = EXCEPT(Month,MonthEnd)

RETURN

ADDCOLUMNS(

exceptmonth,

"store 3 sales",[Store 3 Sales],

"store 5 sales",[Store 5 Sales]

)

Output : 

4.INTERSECT : 

• Returns the row intersection of two tables, retaining duplicates.

Return :

• A table that contains all the rows in table_expression1 that are also in table_expression2

Remarks : 

• Intersect is not commutative. In general, Intersect(T1, T2) will have a different result set than Intersect(T2, T1).
• Duplicate rows are retained. If a row appears in table_expression1 and table_expression2, it and all duplicates in table_expression_1 are included in the result set.
• The column names will match the column names in table_expression1.
• Columns are compared based on positioning, and data comparison with no type coercion.
• The returned table does not include columns from tables related to table_expression1.

Syntax : 

INTERSECT(<table_expression1>, <table_expression2>)

Example : 

Intersect_storesales =

VAR Months = VALUES('Calendar'[Month_Name])

VAR Month = {"june","july","january","March"}

VAR intersectmonth = INTERSECT(Month,Months)

VAR months_new = INTERSECT(Months,Month)

RETURN

ADDCOLUMNS(

intersectmonth,

"store 3 sales",[Store 3 Sales],

"store 5 sales",[Store 5 Sales]

)

Dashboard and Visualization :

I hope you liked this.

Thank you,

Happy Learning!