PowerBI DAX – CONTAINS

Contains Function is similar to the Filter except for the fact that is scalar in nature. What this means is we use Contains when looking for a specific value rather than trying to filter an entire table to result in a set. This is especially useful when trying to find and fix typos within the data or creating a subset using existing data without creating a calculated column etc.

In the below screenshot you can see we have created a field call ExContains which looks for a specific value “20 mm Double sided bubble wrap 50m” and return true if the exact string has been matched.

The measure is created as shown below

ExContains = CONTAINS('Dimension Stock Item','Dimension Stock Item'[Stock Item],"20 mm Double sided bubble wrap 50m")

This way we can identify and exclude the one typo from the report without having to make changes to the data typically we used this is eliminate anything classified as Unknown for example.

The SQL Equivalent of the above function would be similar to

SELECT CASE 
        WHEN name = 'Jayanth'
            THEN 'True'
        ELSE 'False'
        END
FROM orders