Home » CONTAINS Function DAX

CONTAINS Function DAX

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..

DAX- FIND Function




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.