Daily DAX : Day 103 FIND

 The FIND function in Power BI's Data Analysis Expressions (DAX) is used for searching for a text string within another text string. Here's an explanation of how it works and its main use case:

Syntax

FIND(text_to_find, within_text, [start_num], [not_found])

    text_to_find: The text you want to find.

    within_text: The text within which you want to search for text_to_find.

    start_num (optional): The character position at which to start the search. If omitted, it starts from position 1.

    not_found (optional): The value returned if text_to_find is not found. If omitted, it returns an error.

How It Works

    FIND is case-sensitive, meaning it will distinguish between uppercase and lowercase letters.

    It returns the starting position of text_to_find within within_text. If text_to_find isn't found, it either returns the not_found value or an error if not_found isn't specified.

Main Use Case

The primary use of the FIND function in Power BI includes:

    Data Cleaning and Transformation: 

        You can use FIND to detect and manipulate specific patterns or markers within text data. For example, you might want to extract parts of strings based on known delimiters or to clean data by removing or replacing specific text patterns.

    Conditional Formatting and Filtering: 

        FIND can be used in conditional statements to format or filter data based on the presence of certain text. For instance, you could highlight rows in a table where a particular product name appears in a description column.

    Data Validation: 

        Checking if certain text is present in fields to ensure data integrity or to flag data for review. For example, ensuring that all email addresses contain an "@" symbol.

Example

Let's say you have a column called ProductDescription and you want to check if the word "Wireless" appears in any description:

dax

WirelessCheck = 

IF(

    FIND("Wireless", [ProductDescription], 1, -1) > 0,

    "Wireless Product",

    "Non-Wireless Product"

)

This measure will return "Wireless Product" if "Wireless" is found in the description, otherwise "Non-Wireless Product". Here, -1 is used as not_found to return -1 when "Wireless" isn't found, which is then evaluated by IF to be less than or equal to 0.

Remember, because FIND is case-sensitive, you might want to use LOWER or UPPER functions in combination with FIND if you want case-insensitive searches.

https://dax.guide/find/