Understanding the CONTAINSSTRINGEXACT Function
The CONTAINSSTRINGEXACT function is used to check if one string is contained within another string, and returns true or false depending on the result. Unlike the CONTAINSSTRING function, which performs a case-insensitive search, the CONTAINSSTRINGEXACT function performs a case-sensitive search.
The syntax for the CONTAINSSTRINGEXACT function is as follows:
Where `text` is the string to search in, and `substring` is the string to search for.
Using the CONTAINSSTRINGEXACT Function in Power BI
To use the CONTAINSSTRINGEXACT function in Power BI, you need to create a new measure or column. Let's create a new column to demonstrate how to use the function.
1. Open Power BI Desktop and load your data into the report.
2. In the Fields pane, right-click on the table where you want to add the new column and select "New Column".
3. In the formula bar, enter the following DAX formula:
Column Name = IF(CONTAINSSTRINGEXACT([Text], “Substring”), “True”, “False”)
Replace `Column Name` with the name you want to give your new column, `[Text]` with the name of the column that contains the text you want to search in, and `"Substring"` with the string you want to search for.
4. Press Enter to create the new column, and the result will be displayed in the table.
5. Finally, you can drag and drop the new column into your report canvas to create visualizations.
Tips for Using the CONTAINSSTRINGEXACT Function
Here are some tips to keep in mind when using the CONTAINSSTRINGEXACT function in Power BI:
- The CONTAINSSTRINGEXACT function is case-sensitive, so make sure you enter the substring exactly as it appears in the text.
- The function returns a Boolean value (True or False), so make sure you format the result as text if you want to display it in a visual.
- If you want to search for a substring that contains a special character, such as an apostrophe or quotation mark, you need to escape the character with a backslash () in the formula.
- You can use the & operator to concatenate multiple strings together. For example, if you want to search for a substring that contains both "apple" and "orange", you can use the formula:
IF(CONTAINSSTRINGEXACT([Text], “apple”) & CONTAINSSTRINGEXACT([Text], “orange”), “True”, “False”)
The CONTAINSSTRINGEXACT function in DAX is a powerful tool for searching for substrings in Power BI. By using this function, you can perform case-sensitive searches and customize your data analysis. Take advantage of these tips to ensure you get the most out of this function and create meaningful insights for your business.
Power BI DAX Training Courses by G Com Solutions (0800 998 9248)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00