INTERSECT DAX Function (Table manipulation)

Returns the rows of left-side table which appear in right-side table.

Syntax

INTERSECT ( <LeftTable>, <RightTable> )
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

Table An entire table or a table with one or more columns.

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.

» 6 related articles
» 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:

Related functions

Other related functions are:

Last update: Mar 27, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber

Microsoft documentation: https://docs.microsoft.com/en-us/dax/intersect-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of INTERSECT? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.