CONTAINS
Returns TRUE if the table has at least one row where the named column equals the value.
CONTAINS(<table>, <column>, <value> [, <column>, <value>] ...)Returns: TRUE or FALSE.
What it does
CONTAINS asks whether a value exists in a column of a table. It returns TRUE or FALSE. Use it to flag rows whose key appears in another table, such as stores that had any sales, or customers on a watch list. It is the yes or no version of a lookup.
Syntax and arguments
- table: the table to search.
- column and value: the column to test and the value to look for. More pairs can be added, and all of them must match in the same row.
Example
A flag on each store that says whether the store had any sales. This is a calculated column on the Stores table, where each row supplies its own store key.
Has Sales =
IF (
CONTAINS ( Sales, Sales[StoreKey], Stores[StoreKey] ),
"Yes",
"No"
)
For each store, CONTAINS checks the Sales table for a line with that store key. The flag can then filter a report to stores with activity.
Example: a second use
A measure that checks whether electronics is one of the categories in the current view.
Electronics Visible =
CONTAINS (
VALUES ( Product[ProductCategory] ),
Product[ProductCategory],
"Electronics"
)
The result is TRUE when electronics is in the visible list. A report can use it to show a note only when the category is on the page.
Things to watch
- Values must match exactly, including the data type.
- CONTAINS scans the table. On a large fact table it can be slow, so prefer a dimension list or a relationship where you can.
- In a calculated column, the value comes from the current row, so each row gets its own answer.
- For a test on a whole row with several columns, use
CONTAINSROW.
In business terms
Exception flags: customers on a credit hold list, vendors on the approved list, and accounts with activity in a period. These are simple yes or no answers that feed an IF statement or a filter in a close checklist or review pack.