CONTAINSROW
Returns TRUE if the table has a row that matches the values you give, in column order.
CONTAINSROW(<table>, <value> [, <value>] ...)Returns: TRUE or FALSE.
What it does
CONTAINSROW checks whether a specific row exists in a table. You give one value for each column, in the column order of the table, and it returns TRUE when a row matches all of them. Use it to test a combination, such as whether a store had online sales in the current period. If only one column matters, CONTAINS is simpler.
Syntax and arguments
- table: the table to check. Its columns are matched in order.
- value: one value for each column of the table, in the same order and with matching types.
Example
Whether a particular country and channel combination has sales in the current view. The country name must be spelled as it is in the data.
Country Online Exists =
CONTAINSROW (
SUMMARIZE ( Sales, Stores[Country], Channel[ChannelName] ),
"USA", "Online"
)
SUMMARIZE builds the list of country and channel pairs that have sales. CONTAINSROW then checks whether the pair "USA" and "Online" is in that list. The result is TRUE or FALSE.
Example: a second use
Whether electronics is one of the visible categories, using a single-column table.
Electronics Visible =
CONTAINSROW (
VALUES ( Product[ProductCategory] ),
"Electronics"
)
With one column, the single value is matched against that column.
Things to watch
- The values must line up with the table's columns, in order and in type. Swapping two values gives a different answer.
- A blank value matches only a blank in that position.
- The table is read in the current filter context.
- Test with a small table first so that the column order is clear.
- For a single column,
CONTAINSnames the column and is easier to read.
In business terms
Checking that a combination exists before a report shows it, such as an entity and period pair in a close checklist, or checking a list of approved account and cost center pairs against what was posted.