CONTAINS DAX Function (Information)

Returns TRUE if there exists at least one row where all columns have specified values.

Syntax

CONTAINS ( <Table>, <ColumnName>, <Value> [, <ColumnName>, <Value> [, … ] ] )

Parameter	Attributes	Description
Table		The table to test.
ColumnName	Repeatable	A column in the input table or in a related table.
Value	Repeatable	A scalar expression to look for in the column.

Return values

Scalar A single boolean value.

A value of TRUE if each specified value can be found in the corresponding columnName, or are contained, in those columns; otherwise, the function returns FALSE.

Remarks

The arguments columnName and value must come in pairs; otherwise an error is returned.
columnName must belong to the specified table, or to a table that is related to table.
If columnName refers to a column in a related table then it must be fully qualified; otherwise, an error is returned.

Using CONTAINS is more efficient than using a similar syntax using COUNTROWS and FILTER:

COUNTROWS ( 
    FILTER (
        table,
        columnName = value
    )
) > 0

A common pattern with CONTAINS is that used for a virtual relationship, which is better implemented using TREATAS or INTERSECT. The following code:

CALCULATE ( 
    [measure],
    FILTER ( 
        ALL ( targetTable[targetColumn] ),
        CONTAINS ( 
            VALUES ( sourceTable[sourceColumn] ),
            sourceTable[sourceColumn],
            targetTable[targetColumn]
        )
    )
)

can be better written using TREATAS:

CALCULATE ( 
    [measure],
    TREATAS ( 
        VALUES ( sourceTable[sourceColumn] ),
        targetTable[targetColumn]
    )
)

If TREATAS is not available, INTERSECT provide a second choice alternative:

CALCULATE ( 
    [measure],
    INTERSECT ( 
        ALL (  targetTable[targetColumn] ),
        VALUES ( sourceTable[sourceColumn] )
    )
)

Learn more about CONTAINS in the following articles:

Last update: Apr 14, 2019 » Contribute » Show contributors

Contributors: Alberto Ferrari, Marco Russo

MSDN documentation: https://docs.microsoft.com/en-us/dax/contains-function-dax

Syntax

Return values

Remarks

Related articles

Contribute