Skip to main content

Microsoft

Calculated Table Joins in Power BI

Person Testing Different Business Processes

Calculated table joins are used to couple two or more tables of data. Common examples include CROSSJOIN, UNION, EXCEPT and INTERSECT.

Common Use Cases for Calculated Table Joins in Power BI

  • Blending or combining data across multiple tables.
  • Creating advanced calculations like new vs. returning users or repeat purchase behavior.
  • Querying tables to troubleshoot errors or understand connections in a data model.

CROSSJOIN

CROSSJOIN() Returns a table that contains the cartesian product of the specified tables.

It is a cartesian product of two sets, forming a new set containing all ordered pairs.

= CROSSJOIN(Table, Table, […] ])

Multiple table expressions can be included in a crossjoin.

Examples:

  • ‘Dim Product’
  • VALUES(‘Fact Sale’[Store ID])

1. Crossjoin

Resulting table contains 12 rows (4*3) and 2 columns (1+1)

Important Points

  • Column names must all be different in all table arguments.
  • The number of rows returned equals the product of rows in all tables.
  • The number of columns returned equals the sum of columns in all tables.

UNION

UNION() Combines or “stacks” rows from two or more tables sharing the same column structure.

= UNION(Table, Table, […])

Accepts any DAX expression for two (or more) tables with identical column structure.

Examples:

  • ‘Table 2021’, ‘Table 2022’
  • ‘Table 2021’, DATATABLE()

2. Union

UNION stacks tables together, just like append

Important Points

  • All tables must hold the same number of columns.
  • Columns are grouped according to their position in their respective tables.
  • Column names are determined by the first table expression.
  • Duplicate rows are retained.

EXCEPT

EXCEPT() Returns all rows from the left table which do not appear in the right table.

= EXCEPT(LeftTable, RightTable)

The left and right tables used for join.

(NOTE: The First table must be a table inside the data model.)

Example:

  • EXCEPT(‘Dim Product A’​, ‘Dim Product B’)

3. Except Ex

Resulting table contains rows which ONLY appear in the left table

Important Points

  • Both tables must hold the same number of columns.
  • Columns are compared based on positioning in their respective tables.
  • Column names are determined by the left table.
  • The resulting table does NOT retain relationships to other tables (can’t be used as an expanded table).

INTERSECT

INTERSECT() Returns all the rows from the left table which also appear in the right table.

= INTERSECT(LeftTable, RightTable)

The left and right tables used for joining.

(NOTE: The first table must be a table inside the data model.)

Example:

  • Previous Month Active Customers =

LeftTable: VALUES(‘Fact Sales’[Customer ID]),
RightTable: CALCULATETABLE( VALUES(​’Fact Sales’[Customer ID]), DATEADD(‘Dim Calendar’[Date],-1, MONTH))

  • INTERSECT(‘Dim Product A’, ‘Dim Product B’)

4. Intersect Ex

Resulting table contains rows which appear in BOTH tables

Important Points

  • Order matters! The result of (T1, T2) may be different from (T2, T1).
  • Columns are compared based on positioning in their respective tables.
  • Duplicate rows are retained.
  • Column names are determined by the left table.
  • The resulting table does NOT retain relationships to other tables (can’t be used as an expanded table).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Prashant Parkhedkar

Prashant is an Associate Technical Consultant at Perficient in Nagpur. He is a Microsoft Certified Power BI Data Analyst Associate with over 3 years of experience. He is enthusiastic and eager to dive into the world of business analytics.

More from this Author

Follow Us