CONTAINS DAX Function (Information)
Returns TRUE if there exists at least one row where all columns have specified values.
Syntax
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
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:
-
Physical and Virtual Relationships in DAX
-
Propagating filters using TREATAS in DAX
-
The IN operator in DAX
This article describes the IN operator in DAX, which simplifies logical conditions checking whether a certain value is included in a list of values or expressions. » Read more
Last update: Jan 11, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
MSDN documentation: https://docs.microsoft.com/en-us/dax/contains-function-dax