How to check if a Power BI column contains a text string?

by

Use the following DAX expression to check whether a table column in your Power BI data model contains a specific string:

your_column = CONTAINSSTRING(column_to_search, search_string)

For example:

Python Course = CONTAINSSTRING(Activities[Courses], "Python")

Search for multiple strings:

your_column = OR( CONTAINSSTRING(column_to_search, search_string_1 ), CONTAINSSTRING(column_to_search, search_string_n )

For example:

Data Course = OR(CONTAINSSTRING(Activities[Courses],"Pandas"), CONTAINSSTRING(Activities[Courses],"BI"))

Check that text exists in a Power BI column

Importing your Data Set

For this example, we will use a simple list of courses that we will import into Power BI from a text file.

Now that we got the data set we can find text strings within the column text.

Find whether a cell contains specific text

Here is how to search and find a specific sub string in the column text.

  • Open your Power BI report.
  • Go to the Data View.
  • Create a New Column.
  • In the formula, type the following expression:
Python Course = CONTAINSSTRING(Activities[Courses], "Python")
  • The CONTAINSSTRING function returns a boolean True if the substring in this case the word “Python” is found in the Courses column. If the string is not found, it returns a boolean False.
  • You will see the following column added to your data model:

Search for multiple values in a column

In the follow up use case, we will search for multiple strings in the column. Use the same procedure that was outlined before and the following DAX formula:

Data Course = OR( CONTAINSSTRING(Activities[Courses],"Pandas"),  CONTAINSSTRING (Activities[Courses],"BI"))
  • In this case, if either of the strings “Pandas” or “BI” are found, the Data Course column will get a True value; else a False.
  • Here’s our table:

Implement if/then logic

We can use the results of the formulas written above to implement an IF / THEN scenario. Let’s assume that for every Python course the margin is 20% and for Data Courses the margin is 40%; for the rest of the courses, the margin is the standard 50%.

Let’s add a Margin column and populate it accordingly:

  • Go to the Data View.
  • Create a New Column.
  • In the formula, type the following expression:
Margin % = IF (Activities[Python Course]= TRUE(),20, IF (Activities[Data Course] = TRUE(),40,50))

Here’s our table: