Downloads
DAXTable constructionIntermediate

INTERSECT

Returns the rows that appear in every table you give.

Syntax
INTERSECT(<table1>, <table2> [, <table3>] ...)

Returns: A table.

What it does

INTERSECT keeps only the rows that appear in all the tables. It answers "which members are on both lists?" For example: which customers bought in a store and also online. The tables must have the same number of columns with compatible types. Wrap the result in DISTINCT when you need one row per value.

Syntax and arguments

Example

Customers who bought in both channels.

Customers in Both Channels =
INTERSECT (
    CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Channel[ChannelName] = "Store" ),
    CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Channel[ChannelName] = "Online" )
)

Each part lists the customers in one channel, and INTERSECT keeps the customers that appear in both. Count them in a measure with COUNTROWS around the table.

Example: a second use

Product categories that also appear in the budget. A category with no budget line can then be found by comparison.

Budgeted Categories =
INTERSECT (
    VALUES ( Product[ProductCategory] ),
    VALUES ( Budget[Category] )
)

This assumes a Budget table with a Category column, which is not part of the Contoso sample.

Things to watch

In business terms

Customers who buy through two channels, vendors that are both on the approved list and in the payables ledger, and accounts that are in both the chart of accounts and the budget file.

Related functions

All DAX functions · View as Markdown · DAX fundamentals