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] )
    )
)

Related articles

Learn more about CONTAINS in the following articles:

Last update: Oct 13, 2018   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

MSDN documentation: https://msdn.microsoft.com/en-us/query-bi/dax/contains-function-dax

DAX Conventions
Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

Contribute

Want to improve the content of CONTAINS? Did you find any issue? Please, report it us!
All submissions will be evaluated for possible updates of the content.