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 is0
, which performs a case-sensitive comparison. Use1
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.