Daily DAX : Day 343 CONTAINS

Power BI DAX CONTAINS Function

Description

The CONTAINS function in Power BI DAX checks if a specific value exists in a column within a table. It returns TRUE if the value is found and FALSE if it is not.

Syntax

CONTAINS(<table>, <column>, <value>[, <column>, <value>]...)
    

• table: The table to search in.
• column: The column to check for the value.
• value: The value to look for in the column.
• Multiple column-value pairs can be specified to check multiple conditions.

Use Case

The CONTAINS function is useful for filtering data, creating conditional calculations, or validating the presence of specific values in a dataset. It is commonly used in scenarios like:

• Checking if a specific product exists in a sales table.
• Filtering rows based on multiple conditions across columns.
• Creating measures to flag specific data points.

Example

Suppose you have a table named Sales with columns Product and Region. You want to check if there is a sale of "Laptop" in the "West" region.

IsLaptopInWest = 
CONTAINS(
    Sales,
    Sales[Product], "Laptop",
    Sales[Region], "West"
)
    

Result: Returns TRUE if there is at least one row where Product = "Laptop" and Region = "West", otherwise FALSE.

Notes

• CONTAINS is case-sensitive for text values.
• It evaluates the entire table, so performance may be impacted with large datasets.
• Use in measures or calculated columns for dynamic filtering.