DAX Formula to Classify Values

Requirement

You need a DAX formula that checks if a value:

1. Starts with the word "AOP".
2. Contains the word "ideate".
3. Contains the word "IRO".

If any of the above conditions are met, the formula should return the value "Company".

DAX Formula

CompanyClassification = 
IF(
    LEFT(ValueColumn, 3) = "AOP" || 
    SEARCH("ideate", ValueColumn, 1, 0) > 0 || 
    SEARCH("IRO", ValueColumn, 1, 0) > 0, 
    "Company", 
    BLANK()
)

Explanation of the Formula

1. LEFT Function:

   • LEFT(ValueColumn, 3) extracts the first three characters of ValueColumn and checks if they equal "AOP".

2. SEARCH Function:

   • SEARCH("ideate", ValueColumn, 1, 0) looks for the substring "ideate" in ValueColumn. The third parameter specifies the starting position (1 means start from the beginning), and the fourth parameter (0) indicates that if "ideate" is not found, return 0.
   • Similar logic is applied for "IRO".

3. IF Statement:

   • The IF statement evaluates whether any of the conditions are true. If true, it returns "Company". If none of the conditions are satisfied, it returns a blank value.

Practical Example

Suppose you have a table called Projects, and a column named ProjectDescription containing various project descriptions.

• If ProjectDescription contains "AOP123", the formula would return "Company".
• If ProjectDescription is "We need to ideate for the next phase", it would return "Company" as well.
• If ProjectDescription is "This project is related to IRO", the response would also be "Company".
• If ProjectDescription has none of these phrases, such as "Normal Project", it would return blank.

Conclusion

This DAX formula allows you to classify project descriptions based on specific criteria, enhancing your data analysis capabilities. For further learning on DAX and its applications, consider exploring resources available on the Enterprise DNA Platform.