DAX Tutorial: CONTAINSSTRING in Power BI.

Power BI is a powerful business intelligence tool that allows users to analyze and visualize data from various sources. One of the key features of Power BI is its Data Analysis Expressions (DAX) language, which enables users to create custom calculations and formulas.

When working with data, it is often necessary to check if a string contains a specific substring. In SQL, this can be achieved using the LIKE operator along with the % wildcards. However, in Power BI DAX, there is no direct equivalent to the LIKE operator. Instead, we can use the CONTAINSSTRING function to achieve similar functionality.

CONTAINSSTRING Function

The CONTAINSSTRING function in Power BI DAX is used to check if a text string contains a specific substring. It returns TRUE if the substring is found, and FALSE otherwise. The syntax of the CONTAINSSTRING function is as follows:

CONTAINSSTRING(text, substring [, occurrence [, compare]])

Here’s a breakdown of the function’s parameters:

  • text: The text string to search within.
  • substring: The substring to search for.
  • occurrence (optional): Specifies which occurrence of the substring to search for. If omitted, the function searches for the first occurrence.
  • compare (optional): Specifies the comparison mode. The default value is 0, which performs a case-sensitive comparison. Use 1 for a case-insensitive comparison.

Examples

Let’s look at some examples to understand how the CONTAINSSTRING function works:

Example 1:

CONTAINSSTRING("Power BI is a powerful tool", "power")

This example will return FALSE because the substring “power” is not found in the text string “Power BI is a powerful tool”. The comparison is case-sensitive by default.

Example 2:

CONTAINSSTRING("Power BI is a powerful tool", "power", 1)

This example will return TRUE because the substring “power” is found in the text string “Power BI is a powerful tool”. The comparison is case-insensitive due to the 1 parameter.

Example 3:

CONTAINSSTRING("Power BI is a powerful tool", "BI", 2)

This example will return FALSE because the substring “BI” is not found as the second occurrence in the text string “Power BI is a powerful tool”.

Using CONTAINSSTRING in Power BI

Now that we understand how the CONTAINSSTRING function works, let’s see how we can use it in Power BI. Suppose we have a table called “Products” with a column named “Product Name”. We want to create a calculated column that identifies products with the word “Power” in their names.

To achieve this, we can use the following DAX formula:

HasPower = CONTAINSSTRING(Products[Product Name], "Power")

This formula will return TRUE for products whose names contain the word “Power”, and FALSE otherwise. We can then use this calculated column for further analysis and visualization in Power BI.

Conclusion

Although Power BI DAX does not have a direct equivalent to the LIKE operator in SQL, we can achieve similar functionality using the CONTAINSSTRING function. By understanding how this function works and its syntax, we can effectively search for substrings within text strings in Power BI and perform custom calculations based on the results.

Remember to experiment and explore other DAX functions to further enhance your data analysis capabilities in Power BI.

Leave a Comment