INTERSECT
The rows of one table that a second table also has; duplicates of the first remain.
Group: Table manipulation. Compatible with DAX.
Syntax
INTERSECT ( <table_expression1>, <table_expression2> )| Parameter | Type | Description |
|---|---|---|
table_expression1 | Table | The rows to start from. |
table_expression2 | Table | The rows to look for. It must have as many columns as the first. |
Returns
Table. The rows of the first table that the second has too, with the first table's columns.
Remarks
- Rows are compared column by column, by position, not by name.
- Duplicates come from the first table: a row it holds twice comes back twice when the second has it once, while a row twice in the second only counts once.
- So INTERSECT ( A, B ) and INTERSECT ( B, A ) can differ in their duplicates, and in the names and model ties of the columns, which come from the first.
- A blank matches a blank.
Examples
Planned and brewed
EVALUATE
VAR Plan =
DATATABLE (
"Product", STRING,
{ { "Pils" }, { "IPA" }, { "Stout" }, { "Stout" }, { "Cider" } }
)
VAR Brewed = DATATABLE ( "Product", STRING, { { "Pils" }, { "Cider" }, { "Cider" } } )
RETURN
INTERSECT ( Plan, Brewed )
ORDER BY [Product]| Product |
|---|
| Cider |
| Pils |
Duplicates from the first table
Pils is twice in the plan and once in the brews: it comes back twice.
EVALUATE
VAR Plan = DATATABLE ( "Product", STRING, { { "Pils" }, { "Pils" }, { "IPA" } } )
VAR Brewed = DATATABLE ( "Product", STRING, { { "Pils" } } )
RETURN
INTERSECT ( Plan, Brewed )
ORDER BY [Product]| Product |
|---|
| Pils |
| Pils |