CONTAINS function is a Power BI Information function in DAX which returns TRUE if there exists at least one row where all columns have specified values.
SYNTAX
CONTAINS(<table_name>, <column_name>, <value>[, <column_name>, <value>]…)
table_name is a table.
column_name is an existing column in table. It cannot be an expression.
value is a scalar expression to look for in the column_name.
Lets look at an example of CONTAINS function.
Following dataset having two columns category and sub-category.
Now you want to check if any specific value exists in category column or not, Following DAX uses CONTAINS function to check whether the category column contains the value “Office Supplies” or not and returns boolean value either True if value exists or False if value does not exist.
Text_Contains = CONTAINS ( 'Global-Superstore', 'Global-Superstore'[Category], "Office Supplies" )
After commiting the DAX, Lets darg the measure into table visual next to Sub-category column.
As you can see, CONTAINS Function check the values for each category row by row and returns either True if category valuecontains “Office Supplies” or False If category does not contain the value.
Using CONTAINS function for checking values on multiple columns
Lets check for values in multiple columns, Suppose you want to check values for multiple columns then you can also provide multiple column name and values for those value columns in CONTAINS Function.
Note that, the arguments column_name and value must come in pairs, like for checking value for two columns you need to provide first column and value for column that you want to look for in first column, then second column and value for the second column as shown below DAX expression.
Lets say, you want to check whether the Category contains value “Office Supplies” and Sub-category contains value “Art” or not for any rows in dataset or not.
Lets modify the above DAX- Text_Contains as following.
Text_Contains = CONTAINS ( 'Global-Superstore', 'Global-Superstore'[Category], "Office Supplies", 'Global-Superstore'[Sub-Category], "Art" )
After commiting the DAX, you can see. It returns TRUE where it found Category value contains “Office Supplies” and Sub-category value contains “Art”.
Also Read..
