CONTAINSROW - Daxplained

CONTAINSROW is a DAX function that returns TRUE if there exists at least one row where all specified columns have the specified values.

Syntax

CONTAINSROW(
   Table,
   [ Value, ... ]
)

Argument	Properties	Description
Table		The table to test.
Value	Repeatable	A scalar expression to look for in the corresponding column.

Return Values

TRUE or FALSE.

Remarks

Except syntax, the IN operator and CONTAINSROW function are functionally equivalent.
```
<scalarExpr> IN <tableExpr> 
( <scalarExpr1>, <scalarExpr2>, … ) IN <tableExpr>
```
- The number of scalarExprN must match the number of columns in tableExpr.
- NOT IN is not an operator in DAX. To perform the logical negation of the IN operator, put NOT in front of the entire expression. For example, NOT [Color] IN { “Red”, “Yellow”, “Blue” }.
Unlike the = operator, the IN operator and the CONTAINSROW function perform strict comparison. For example, the BLANK value does not match 0.

Examples

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

Example 1

The following DAX queries:

EVALUATE
FILTER (
    ALL ( Product[Color] ),
    ( [Color] )
        IN {
        "Red",
        "Yellow",
        "Blue"
    }
)
ORDER BY [Color]

and

EVALUATE
FILTER (
    ALL ( Product[Color] ),
    CONTAINSROW (
        {
            "Red",
            "Yellow",
            "Blue"
        },
        [Color]
    )
)
ORDER BY [Color]

Return the following table with a single column:

[Color]
Blue
Red
Yellow

Example 2

The following equivalent DAX queries:

EVALUATE
FILTER (
    ALL ( Product[Color] ),
    NOT [Color]
        IN {
        "Red",
        "Yellow",
        "Blue"
    }
)
ORDER BY [Color]

and

EVALUATE
FILTER (
    ALL ( Product[Color] ),
    NOT CONTAINSROW (
        {
            "Red",
            "Yellow",
            "Blue"
        },
        [Color]
    )
)
ORDER BY [Color]