The CONTAINS function allows you to evaluate whether there are rows with a certain value or values in one or more columns of a certain table. If positive, it returns True and, otherwise, it returns False.
CONTAINS(
table,
columnName,
value
[, columnName, value]…
)
- table: Name of a table or DAX expression that returns a data table.
- columnName: Name of an existing column. It cannot be an expression.
- value: Value to search or DAX expression that returns a single scalar value. This value will be the one indicated in the column columnName.
The CONTAINS function returns a Boolean: True if there is at least one row in the table that contains the searched value, or False otherwise.
If the value argument is specified by an expression, it will only be evaluated once.
The columnName and value arguments must be included in the function in pairs. Otherwise the function will return an error.
The columnName argument can be the name of an existing column in table, or of a column that belongs to a table related to it, in which case it must be fully qualified, returning an error if it is not.
Working with the ResellerSales table containing sales information, we have a ProductKey column that indicates the product sold. We can check if there has been any sale of product 210 (which is not true) with the following measure:
Sale Exists = CONTAINS(ResellerSales, ResellerSales[ProductKey], 210)
If, in the same example, we want to check if any sale of product 322 has been made through reseller 403 (which is true), we could do so using the following measure:
Sale Exists = CONTAINS(ResellerSales, ResellerSales[ProductKey], 322, ResellerSales[ResellerKey], 403)
Now suppose that the aforementioned ResellerSales sales table is related to a Geography table that contains information about the location of each reseller. If we want to know if the product whose key is 322 has been sold by a reseller located in "England" (which is false), we could do it in the following way:
Sale Exists = CONTAINS(ResellerSales, ResellerSales[ProductKey], 322, Geography[StateProvinceName], "England")
On the contrary, the measure:
Sale Exists = CONTAINS(ResellerSales, ResellerSales[ProductKey], 322, Geography[StateProvinceName], "Florida")
...will return True since it is true that sales have occurred in Florida:
