DAX Formula for Conditional Text Check

Formula

To create a DAX formula that checks if a given value contains the words "AOP" and "ideate" and returns "Company" if both are present, you can use the following DAX expression:

ConditionalValue = 
IF(
    CONTAINSSTRING([YourColumnName], "AOP") && CONTAINSSTRING([YourColumnName], "ideate"),
    "Company",
    BLANK()  // or any other value you want to return in the absence of the keywords
)

Explanation

1. Function Components:

   • IF(condition, true_result, false_result): This function evaluates a condition and returns one result if true, and another result if false.
   • CONTAINSSTRING(string, substring): This function checks if the specified substring is found within the provided string. It returns TRUE if the substring is present; otherwise, it returns FALSE.

2. Logic:

   • The formula checks if both "AOP" and "ideate" are present in [YourColumnName].
   • If both conditions are true, it returns "Company."
   • If either of the conditions is false, it returns a blank value (or an alternative value as specified).

Practical Example

Assume you have a table named Projects, which includes a column ProjectDescription that contains project-related text strings. You would apply the formula as follows:

ConditionalValue = 
IF(
    CONTAINSSTRING(Projects[ProjectDescription], "AOP") && 
    CONTAINSSTRING(Projects[ProjectDescription], "ideate"),
    "Company",
    BLANK()
)

Scenario

If Projects[ProjectDescription] contains:

• Row 1: "This project is about AOP and ideate strategies." → returns "Company"
• Row 2: "This project focuses on ideation." → returns BLANK
• Row 3: "AOP is crucial but does not involve ideation." → returns BLANK
• Row 4: "AOP and ideate are essential for success." → returns "Company"

Notes

• Ensure you replace [YourColumnName] with the actual name of the column you want to evaluate.
• This formula is highly adaptable and can be modified to check for additional words or different strings as needed, enhancing its utility in various scenarios.

For further learning on DAX and its effective application, consider exploring resources available on the Enterprise DNA Platform.