CONTAINS DAX Function (Information)

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


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

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.


  • 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:

    FILTER (
        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:

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

can be better written using TREATAS:

    TREATAS ( 
        VALUES ( sourceTable[sourceColumn] ),

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

        ALL (  targetTable[targetColumn] ),
        VALUES ( sourceTable[sourceColumn] )
» 5 related articles


--  CONTAINS is useful to search in a table for the presence
--  of at least one row with a given set of values
DEFINE MEASURE Sales[Customers without stores] = 
        FILTER (
            NOT CONTAINS (
                Store[CountryRegion], Customer[CountryRegion],
                Store[City],          Customer[City]
    "Customers", COUNTROWS ( Customer ),
    "Customers without stores", [Customers without stores]
CountryRegion Customers Customers without stores
Australia 3,631 3,524
United States 8,086 7,362
Canada 1,579 1,381
Germany 1,791 1,668
United Kingdom 1,931 1,271
France 1,814 1,426
the Netherlands 1 (Blank)
Greece 1 (Blank)
Switzerland 1 (Blank)
Ireland 1 (Blank)
Portugal 1 (Blank)
Spain 1 (Blank)
Italy 1 (Blank)
Russia 2 (Blank)
Poland 1 (Blank)
Turkmenistan 1 (Blank)
Thailand 1 (Blank)
China 5 (Blank)
Kyrgyzstan 1 (Blank)
South Korea 2 (Blank)
Syria 1 (Blank)
Pakistan 1 (Blank)
India 3 (Blank)
Japan 7 1
Singapore 1 (Blank)
Taiwan 1 (Blank)
Iran 1 (Blank)
Bhutan 1 (Blank)
Armenia 1 (Blank)

Related articles

Learn more about CONTAINS in the following articles:

Last update: Jul 12, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber

Microsoft documentation:

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

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.


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


A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.


This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.


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.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.