INTERSECT DAX Function (Table manipulation)
Returns the rows of left-side table which appear in right-side table.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| LeftTable |
The Left-side table expression to be used for Intersect. |
|
| RightTable |
The Right-side table expression to be used for Intersect. |
Return values
A table that contains all the rows in LeftTable that are also in RightTable.
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.
The returned table has lineage based on the columns in table_expression1, regardless of the lineage of the columns in the second table. For example, if the first column of LeftTable has lineage to the base column C1 in the model, the intersect will reduce the rows based on the intersect on first column of RightTable and keep the lineage on base column C1 intact.
Columns are compared based on positioning, and data comparison does not have type coercion.
The returned table does not include columns from tables related to LeftTable. Therefore, when LeftTable corresponds to a base table, once applied to the filter context it does not involve the expanded table and it only filters columns of the base table.
» 2 related functions
Examples
-- INTERSECT performs set intersection: the second parameter rows
-- are intersected with the first.
-- INTERSECT keeps the data lineage of its first argument only.
DEFINE
VAR Days = VALUES ( 'Date'[Day of Week] )
VAR WeekendDays = { "Saturday", "Sunday" }
VAR DaysWeekends = INTERSECT ( Days, WeekendDays )
VAR WeekendsDays = INTERSECT ( WeekendDays, Days )
EVALUATE DaysWeekends
EVALUATE
ADDCOLUMNS (
DaysWeekends,
"Sales Amount", [Sales Amount]
)
-- In this last result, the data lineage is from WeekendDays, which does not
-- filter the Sales table and the Sales Amount measure.
EVALUATE
ADDCOLUMNS (
WeekendsDays,
"Sales Amount", [Sales Amount]
)
| Day of Week |
|---|
| Saturday |
| Sunday |
| Day of Week | Sales Amount |
|---|---|
| Saturday | 4,332,879.26 |
| Sunday | 4,255,613.01 |
| Value | Sales Amount |
|---|---|
| Saturday | 30,591,343.98 |
| Sunday | 30,591,343.98 |
-- INTERSECT keeps duplicates, if present in the parameters.
-- In case of context transition, there are duplicated values
-- in the results of the measures, too.
EVALUATE
VAR Days = SELECTCOLUMNS ( Date, "Day of week", 'Date'[Day of Week] )
VAR WeekendDays = { "Saturday", "Sunday" }
VAR Result = INTERSECT ( Days, WeekendDays )
RETURN
ADDCOLUMNS (
TOPN ( 5, Result ),
"Sales Amount", [Sales Amount]
)
| Day of week | Sales Amount |
|---|---|
| Saturday | 4,332,879.26 |
| Sunday | 4,255,613.01 |
| Saturday | 4,332,879.26 |
| Sunday | 4,255,613.01 |
| Saturday | 4,332,879.26 |
The arguments of INTERSECT must have the same number of columns.
The following query throws an error because Date contains many more columns than WeekendDays.
EVALUATE
VAR Days = Date
VAR WeekendDays = { "Saturday", "Sunday" }
VAR Result = INTERSECT ( Days, WeekendDays )
RETURN
ADDCOLUMNS (
Result,
"Sales Amount", [Sales Amount]
)
Related articles
Learn more about INTERSECT in the following articles:
- Leverage INTERSECT to apply relationships in DAX
-
Physical and Virtual Relationships in DAX
DAX calculations can leverage relationships present in the data model, but you can obtain the same result without physical relationships, applying equivalent filters using specific DAX patterns. This article show a more efficient technique to apply virtual relationships in DAX… » Read more
-
Using tuple syntax in DAX expressions
This article describes the use of the tuple syntax in DAX expressions to simplify comparisons involving two or more columns. » Read more
-
Finding products without sales by using DAX
This article analyzes the performance of different DAX techniques to identify any products without sales in an area or a time period. » Read more
-
Using CONTAINS in DAX
This article explains how the CONTAINS function works and what can be used as better alternatives in DAX in common use cases. » Read more
-
Set functions in DAX: UNION, INTERSECT, and EXCEPT
This article describes the behavior of the DAX functions that manipulate sets; they are useful to create queries and sometimes also to author measures. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/intersect-function-dax