INTERSECT

Applies to: Calculated column Calculated table Measure Visual calculation

Returns the row intersection of two tables, retaining duplicates.

Syntax

INTERSECT(<tableExpression1>, <tableExpression2>)

Parameters

Term	Definition
tableExpression	Any DAX expression that returns a table.

Return value

A table that contains all the rows in tableExpression1 that are also in tableExpression2

Exceptions

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 tableExpression1 and tableExpression2, it and all duplicates in tableExpression1 are included in the result set.

• The column names will match the column names in tableExpression1.

• The returned table has lineage based on the columns in tableExpression1 , regardless of the lineage of the columns in the second table. For example, if the first column of first tableExpression has lineage to the base column C1 in the model, the intersect will reduce the rows based on the intersect on first column of second tableExpression and keep the lineage on base column C1 intact.

• Columns are compared based on positioning, and data comparison with no type coercion.

• The returned table does not include columns from tables related to tableExpression1.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

States1

State
A
A
B
B
B
C
D
D

States2

State
B
C
D
D
D
E

Intersect(States1, States2)

State
B
B
B
C
D
D

Intersect(States2, States1)

State
B
C
D
D
D